[sqr-users] Re: Dynamic SQL vs. Fixed SQL execution

[David Donnelly <Dave@isisbio.com>]

Olga,

After you use the -s SQR flag to check how many times statements are 
compiled, I would suggest that you hard-code the queries that you think are 
being constructed in one of your tests that seems to run a long time.  In 
other words, if you think you are constructing

         let $where = 'where a.b = c.d'

and use [$where] as dynamic where-clause, then actually hard-code the same 
clause and see how that runs.  I guess you'll need to do this for all six 
dynamic queries.

You might want to limit the size of the outer query result set (using 
loops=nnn) to less than "several thousand" while doing this.

To be honest, I don't know if the queries are recompiled every time they're 
executed.  The possibilities are (1)yes; (2) no; and (3) only if the 
contents of the dynamic clause is changed.  You could test the latter, and 
if that turns out to be the case, then don't change the $where variable 
unless you need to.  I have some vague memory of doing this, but I can't 
remember for sure.

And finally, it is possible that the compiler doesn't have enough info to 
decide about indexes.  One thing that is sometimes possible is to include 
"extra" predicates. For example, if you have a multi-field key, and your 
dynamic query selects a lower-level part of it, make sure the higher-level 
fields are all specified. And what about compiler hints -- might they be 
any help? I have never had to use them.

Best of luck
Dave


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users