[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ms access sql order of query criteria processing

Posted on 2016-09-26
10
Medium Priority
?
86 Views
Last Modified: 2016-09-27
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
0
Comment
Question by:COACHMAN99
  • 4
  • 4
  • 2
10 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 41817025
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
 
LVL 58
ID: 41817636
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
 
LVL 58
ID: 41817654
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:Nick67
ID: 41818355
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
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41818403
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
 
LVL 58
ID: 41818410
<<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
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 41818468
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
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 41818518
<<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
 
LVL 26

Expert Comment

by:Nick67
ID: 41818537
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
 
LVL 7

Author Comment

by:COACHMAN99
ID: 41818867
THANKS GUYS.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question