ms access sql order of query criteria processing

Hi All,
Assuming the ACE engine is sub-setting as it goes, are the query criteria (in the query designer) processed left-to-right, right-to-left or n/a?
The reason I ask is if there are two criteria in the where-clause, a date and a indexed integer, will the query run quicker with the date on the left or right? (or n/a).
And, can one assume the designer order will be the same as the SQL view order?
thanks
LVL 7
COACHMAN99Asked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
Sometimes, after entering a query in the designer, and then looking at the SQL, things have changed. This made me wonder whether the SQL version took priority (and was optimised)


Neither.
Both the Editor and the SQL are constructs of what Access actually stores.
Build a query with the editor.
Save it.
Open it in SQL View, and get rid of all the brackets.
Close and save it.
Open it again in SQL view -- it will likely be text-perfect
Change to Design View
Change something
Switch to SQL View.
All the brackets will be back.

Access saves information to generate the view from what it knows the query to be.
But it uses rules to do that in both cases.
The real McCoy is neither the editor OR text view.

View the system tables.
Have a look it MSysQueries
Both text and design view are built up from (or validated against) the data from here.

Really, what you do with straight SQL in the text or editor isn't going to affect performance.
(now, use DLookup to get a value instead of adding a table and JOIN, that's not straight SQL and WILL have an effect)
0
 
Nick67Commented:
n/a

The engines are smart.
An absolute plethora of variables are involved.
The tables, their keys, the datatype of their keys, indexing of fields JOINed, dataypes of the fields JOINed on and lots more

You mentioned ACE so I assume an Access backend -- because a different backend adds another layer of possibilities.

It is likely that in this case, your indexed integer will get attention first, simply because dates are stored as Doubles, and integer math is more rapid than other types of computation.

When you are working in situations where a query profiler can sniff the engine's operation, you can see that what gets executed is sometimes a far cry from what is written.  The days when you could write atrocious SQL that performed poorly at retrieving the same set of records that well-crafted SQL would retrieve are pretty much behind us.

The query you write these days is more a criterion for the resultset returned by the engine and less an instruction set to the engine  of how to retrieve the results.

And, can one assume the designer order will be the same as the SQL view order?
Not sure what you mean, here.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Short answer: no one really knows since JET/ACE processing has never been documented.

But you can turn on JET's SHOWPLAN function and as long as it doesn't include a sub-query, you will be able to see how JET actually processed a query.

SHOWPLAN is a registry entry that needs to be made.  Once done, a text file logs the execution of any query run.

I'm not at my desk at the moment, but I'll get the required key for you in a bit.

Jim.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here's the keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug

and if on a 64 bit OS and running  in 32 bit mode:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug

 This would be for Access 2007.  You need to change the version (12.0) for later versions.

Jim.
0
 
Nick67Commented:
Once done, a text file logs the execution of any query run.
The location of that log file would be a good thig to know :)
0
 
COACHMAN99Author Commented:
Thanks Guys. I appreciate the info. and suggestions.

I was sure I read somewhere long ago (probably JET) that the sequence, and priority, that parameters/criteria  are processed in an specific order.
I'll wait a while to see if anyone came across that article.

re 'designer order will be the same as the SQL view order': Sometimes, after entering a query in the designer, and then looking at the SQL, things have changed. This made me wonder whether the SQL version took priority (and was optimised)

Maybe Microsoft knows the best sequence to enter criteria (left-to-right or right-to-left, or none of the above :-)

Does anyone know if the FE relationships take priority over the BE set?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<or none of the above :-)>>

 I know this for sure.   Nothing follows what's in the query designer, nor in the SQL statement.  And it's the SQL statement that counts and is what executed.  JEt has a multitude of join strategies and may apply criteria at any point.

<<Does anyone know if the FE relationships take priority over the BE set?>>

Separate question, but relationships are only effective within a DB container.  FE ones don't count.  They'll help you do joins in the query designer, but that's it.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<Both the Editor and the SQL are constructs of what Access actually stores.>>

 Actually no, it stores the SQL statement.  MSysQueries only has some additional attributes to build a query def.

The procedure you outlined above is nothing more than a reflection of taking the SQL and translating it into the query designer, the translating back when you switch to SQL view.

 The SQL statement is stored in MSysObjects.

Jim.
0
 
Nick67Commented:
The SQL statement is stored in MSysObjects.
I'll take your word for it.
but, it is not stored in clear text there.
So a process of one sort or another grabs data and displays it for you.

In any event, I think we both agree, re-arranging field order in the editor or SQL text does not have performance implications.
0
 
COACHMAN99Author Commented:
THANKS GUYS.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.