Hi,

I have some Select-Queries, that are frequeltly called with different parameters in a loop and even a sub loop.

What is the best practice to optimize the speed?

I tried first (example)

for i:=0 to x do begin
 Query.SQL.Text:='Select A,B,C From myTable Where A='+QuotedStr(myA);
 Query.active:=true;
end;

an then

Query.SQL.Text:='Select A,B,C From myTable Where A=:myA
Query.Prepare;
for i:=0 to x do begin
 Query.Active:=false;  // otherwise Param myA will not be changed in the next row
 Query.ParamByName('myA').AsString:=myA;
 Query.Active:=true;
end;

I seems that there is no remarkable difference in speed between theese two variants.

What would you recommend?

Burkhard


The second is the recommended form.

The time saving will depend on the complexity of the query and the number of loops. The idea behind preparing it and then just altering the parameters is that preparing the query involves parsing the sql and opening the tables tables involved, checking on wether indices exist and if they should be used, making sure the columns requested exist and how they should be formatted.

The query you're testing against isn't going to show much preparation time so not much difference in time. Try it with a complex table with multiple joins, subselects and calculated result columns and you should see a significant difference.

Roy Lambert

Burkhard,

<< I have some Select-Queries, that are frequeltly called with different parameters in a loop and even a sub loop.

What is the best practice to optimize the speed?

I tried first (example)

for i:=0 to x do begin
 Query.SQL.Text:='Select A,B,C From myTable Where A='+QuotedStr(myA);
 Query.active:=true;
end;

an then

Query.SQL.Text:='Select A,B,C From myTable Where A=:myA
Query.Prepare;
for i:=0 to x do begin
 Query.Active:=false;  // otherwise Param myA will not be changed in the next row
 Query.ParamByName('myA').AsString:=myA;
 Query.Active:=true;
end;

I seems that there is no remarkable difference in speed between theese two variants. >>

*If* the issue is with the time it takes for EDB to open/close the tables and compile the query, then the second version should be much faster.  However, it may be that the performance bottleneck is elsewhere, such as in the optimization of the query itself.

If you want to email me (support@elevatesoft.com) the query along with your database catalog/table files, I can profile it here and tell you what's going on.  Just be sure to indicate whether the database is Unicode or ANSI, along with any customizations to the signature/encryption password.

Tim Young
Elevate Software
www.elevatesoft.com