Solved

ms access sql order of query criteria processing

Posted on 2016-09-26
10
39 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 57
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 57
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57
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 250 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now