> Is that because by name is very close to a variant
> solution, one of the slowest items in Pascal ?

No, just that there is additional work that cannot be avoided.
Scanning the name to see if there is a relation name provided,
processing the string for quotes etc and then looping through the
entire column list trying to match the name (using the slower Ansi
functions rather than direct byte compares).

Its no big deal, and there is not much to be done about it. I am
avoiding real work this afternoon, so as a result of Tobias' query I
decided to run a test/demo against the employee database. Here are
the results to give you and indication of what the difference is...


procedure TForm1.ButtonEnh3Click(Sender: TObject);
var
i: integer;
tmpDT: TDateTime;
tmpStr: string;
begin
IB_DSQL1.SQL.Clear;
IB_DSQL1.SQL.Add( 'INSERT INTO COUNTRY( COUNTRY, CURRENCY )' );
IB_DSQL1.SQL.Add( 'VALUES( :P1, :P2 )' );
IB_DSQL1.Prepare;
tmpDT := Now;
for i := 0 to 10000 do
begin
IB_DSQL1.ParamByName('P1').AsString := 'A ' + IntToStr(i);
IB_DSQL1.ParamByName('P2').AsString := 'B ' + IntToStr(i);
IB_DSQL1.Execute;
end;
tmpDT := Now - tmpDT;
DateTimeToString( tmpStr, 'hh:nn:ss.zzz', tmpDT );
MemoEnh1.Lines.Add( 'ByName: ' + tmpStr );
end;

procedure TForm1.ButtonEnh4Click(Sender: TObject);
var
i: integer;
Col1, Col2: TIB_Column;
tmpDT: TDateTime;
tmpStr: string;
begin
IB_DSQL1.SQL.Clear;
IB_DSQL1.SQL.Add( 'INSERT INTO COUNTRY( COUNTRY, CURRENCY )' );
IB_DSQL1.SQL.Add( 'VALUES( :P1, :P2 )' );
IB_DSQL1.Prepare;
tmpDT := Now;
Col1 := IB_DSQL1.ParamByName('P1');
Col2 := IB_DSQL1.ParamByName('P2');
for i := 0 to 10000 do
begin
Col1.AsString := 'C ' + IntToStr(i);
Col2.AsString := 'D ' + IntToStr(i);
IB_DSQL1.Execute;
end;
tmpDT := Now - tmpDT;
DateTimeToString( tmpStr, 'hh:nn:ss.zzz', tmpDT );
MemoEnh1.Lines.Add( 'ColRef: ' + tmpStr );
end;


Connected and ran the top function 5 times:

ByName: 00:00:08.500
ByName: 00:00:08.391
ByName: 00:00:09.828
ByName: 00:00:09.906
ByName: 00:00:09.828

Average: 9.29


Connected and run the second function 5 times:

ColRef: 00:00:07.485
ColRef: 00:00:07.219
ColRef: 00:00:08.734
ColRef: 00:00:08.765
ColRef: 00:00:08.750

Average: 8.19


Which makes the second function (using column references) a bit over
11% faster.

(I would have run some longer tests but I am still waiting on a new
CPU fan and did not want to cook my system.)

The tests were done using a local firebird installation. Presumably
the difference would be less significant on a remote server. If you
have more than two fields/parameters you can expect the difference to
be larger. If you have blob columns involved then the difference will
probably be smaller (more work involved in saving a blob).

So I think this demonstrates that column references are significantly
faster. The difference is not so great as to be important in many
situations, such responding to user interactions etc. But the
difference is big enough to be prefer this approach in loops.


Geoff Worboys
Telesis Computing