Query Performance

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I had one question about the best way to perform a query. I have a form
that has it's Record Source created dynamically. Currently I just create a
SQL statement in code as the record source. I then realized that I use all
the same fields and the only difference is the where clause. My question
is, in terms of performance, is it better to use the SQL statement or would
it be better to create a query using those fields and then just selecting *
from the query and creating the WHERE clause via code? Or does it just not
matter?

TIA,
Bill
 
Hi,


In theory a saved query with parameters ( to accommodate different value
you would compare your fields ) is preferable, because you win a couple of
millisecond, time required to get an execution plan, but if that means that
such query become too "fancy", with too many OR, the optimizer would not be
able to find a performing execution plan, at least, not as performing as if
you would have make a specific query without the or. As example:


WHERE (x1=x2) AND ( (param IS NULL) OR (myfield=param))


is a candidate to be slower than


WHERE (x1=x2) AND (myfield = param)


in the case where param is not null. So, ad hoc query can be faster in some
(many) cases, because they capture just the implied specificities.



So, in conclusion, your own tests, experimentations, would speak better
than I about which alternative is best. About if its matter or not, you
cannot save two seconds on a portion of the program that executes itself in
less than 0.1 sec. :-)



Hoping it may help,
Vanderghast, Access MVP.
 
Back
Top