Hi,

System: D7pro, DBISAM v3.27

I'm trying to optimize som queries which are called repeatedly. I
normally do this by using parameterized queries which i do a Prepare on
before executing them...

But i've ran into a problem with this approch...

Example: Say i want to select a number of records from a table where
some field equals a value in a list. Could be illustrated by the
following pseudocode:
********
var
 MyArray: Array of integer;
 a: integer;
begin
 MyArray:=[random number of integers];
 Query.SQL.Add('SELECT SomeInt FROM MyTable');
 Query.SQL.Add('WHERE (0=1)');
 for a:=0 to Length(MyArray)-1 do
 begin
   Query.SQL.Add('OR (SomeInt = '+inttostr(MyArray[a])+')');
 end;
 Query.Open;
 ....etc...
end;
********

The above code can be parameterized by changing:
   Query.SQL.Add('OR (SomeField = '+inttostr(MyArray[a])+')');
to:
   Query.SQL.Add('OR (SomeField = ram'+a+')');
   Query.Params[a].value:=MyArray[a];

But the length of MyArray varies from execution to execution! So the SQL
will not be the same in these executions, therefore the
prepare-technique have no effekt(?). There is no boundries on MyArray(no
length-limitation) so i cannot create a static amount of parameters.

My conclusion is that the above illustrates a queries which cannot be
parameterized....please prove me wrong

Any ideas, thoughts are welcome

Thanks in advance..

Kind regards
 Thomas

"Thomas Eg Jørgensen" <thomas@hest.notaplan.com> wrote in message
news:BC3C2644-09D8-4751-BCAB-7C6EADF37D69@news.elevatesoft.com...
>
> Any ideas, thoughts are welcome
>

I believe you are right in that you can not use parameters. Furthemore, any
change in the SQL will require the query to be prepared again.

You could try breaking it up into two separate steps. The loop thru the
array adds records to a memory table. Then on the "big" query, you JOIN the
memory table.

The steps would be:

1. Create memory table.
2. Prepare "big" query.

Then, every execution

1. Empty memory table. Close "big" query.
2. Loop thru array adding to memory table.
3. Open "big" query.

Robert

Thomas

> var
>  MyArray: Array of integer;
>  a: integer;
> begin
>  MyArray:=[random number of integers];
>  Query.SQL.Add('SELECT SomeInt FROM MyTable');
>  Query.SQL.Add('WHERE (0=1)');
>  for a:=0 to Length(MyArray)-1 do
>  begin
>    Query.SQL.Add('OR (SomeInt = '+inttostr(MyArray[a])+')');
>  end;
>  Query.Open;
>  ....etc...
> end;
> ********

Just to note. Use *IN* instead of many *OR*

select * from table where f=1 or f=2 or f=3 or f=4
replace with
select * from table where f in (1,2,3,4)

Eduardo

Thomas,

<< My conclusion is that the above illustrates a queries which cannot be
parameterized....please prove me wrong>>

Sorry - you're correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jose


Remember that DBISAM can't replace the data for an IN test. Maybe we'll get this sometime in EevateDB

Roy Lambert

"Jose Eduardo Helminsky" <contato@hpro.com.br> skrev i en meddelelse
news:1A52E005-D362-4C53-B9A8-406EE0BF784C@news.elevatesoft.com...
>> var
>>  MyArray: Array of integer;
>>  a: integer;
>> begin
>>  MyArray:=[random number of integers];
>>  Query.SQL.Add('SELECT SomeInt FROM MyTable');
>>  Query.SQL.Add('WHERE (0=1)');
>>  for a:=0 to Length(MyArray)-1 do
>>  begin
>>    Query.SQL.Add('OR (SomeInt = '+inttostr(MyArray[a])+')');
>>  end;
>>  Query.Open;
>>  ....etc...
>> end;
>> ********
>
> Just to note. Use *IN* instead of many *OR*
>
> select * from table where f=1 or f=2 or f=3 or f=4
> replace with
> select * from table where f in (1,2,3,4)
>

Why? Performance?

Kind regards
 Thomas

Thomas

> Why? Performance?
Yes

This is an off-topic answer but an advise.

Eduardo