Access rewriting queries

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

Hi all,

I've got a query which finds records for an 'advanced search' form - ie
users enter parameters on the form, and the query returns the records.
Parameters that are blank should be ignored. There are 7 parameters in all.

I made a query which worked beautifully, but after I saved and reopened it,
I found that Access had rewritten it into an awful mess. I suspect this is
because each of the parameters' criteria has ".... Or Like [formControlName]
Is Null".

The query is now too slow to be of any use. As I said, it worked fine
before. All the fields are indexed. Viewing the SQL for the query brings up
a horrible mess, which I'll happily post if anybody would like to see.

Is there any way to stop Access from rewriting my query? Does anybody have a
better solution than the way I'm doing it?

Thanks in advance,

Stuart
 
Stuart-

This is exactly why many developers create a custom Query-By-Form that
builds a WHERE condition only for the parameter values entered. The ((x =
parameter) or (parameter Is Null)) technique falls on its face beyond 3
parameters, and usually results in a "query too complex".

Access is "rewriting" your query in an attempt to make it more efficient.
Consider the predicate:

WHERE ((X = Parm1) Or (Parm1 Is Null)) AND ((Y = Parm2) Or (Parm2 Is Null))

Coded this way, the JET engine must test *all* the predicate pairs for each
row to make a decision. Access tries to optimize it like this:

WHERE ((X = Parm1) And (Y = Parm2)) Or ((X = Parm1) And (Parm2 Is Null)) Or
((Parm1 Is Null) And Y = Parm2)) Or ((Parm1 Is Null) And (Parm2 Is Null))

Restated this way, the predicate testing can stop at the first pair that
returns True. When you add a third or fourth parameter, you can image how
this really explodes!

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Hi John,

Thanks for your reply.
Access is "rewriting" your query in an attempt to make it more efficient.
Click to expand...
This makes sense - it's short-circuiting it, like Java (you can tell where
my roots are!).
This is exactly why many developers create a custom Query-By-Form that
builds a WHERE condition only for the parameter values entered.
Click to expand...
Could you direct me to a website that gives an example of this? I understand
what you're saying, but I don't know how to pull it off in Access. I'm not
too experienced in Access.

Thanks!

Stuart
 
Back
Top