Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 7 of 7 total |
Complex parameterization...? |
Wed, May 30 2007 10:15 AM | Permanent Link |
=?iso-8859-1?Q?Thomas_Eg_J=F8rgensen?= | 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 |
Wed, May 30 2007 10:37 AM | Permanent Link |
"Robert" | "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 |
Wed, May 30 2007 12:33 PM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Wed, May 30 2007 12:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, May 30 2007 1:04 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jose
Remember that DBISAM can't replace the data for an IN test. Maybe we'll get this sometime in EevateDB Roy Lambert |
Thu, May 31 2007 7:49 AM | Permanent Link |
=?iso-8859-1?Q?Thomas_Eg_J=F8rgensen?= | "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 |
Thu, May 31 2007 3:10 PM | Permanent Link |
"Jose Eduardo Helminsky" | Thomas
> Why? Performance? Yes This is an off-topic answer but an advise. Eduardo |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |