Any execution sequence in Access?

[Luting]

Hello everyone,

I wonder is there any pre defined sequence in Access when it executes
queries and views.
For example, is there any difference in efficiency of the following
two queries?
1. SELECT * FROM table1, table2
WHERE table1.date=table2.date
AND table1.name like "%A%"


2. SELECT * FROM table1, table2
WHERE table1.name like "%A%"
AND table1.date=table2.date


table1 is a huge table with millions of records. So if Access can
filter it with "table1.name like "%A%"" first, the join part will be
greatly speeded up.

If both the queries are the same to Access, is there any other ways I
can tell Access to execute using one plan instead of another?

 

[Bob Barrows [MVP]]

Hello everyone,

I wonder is there any pre defined sequence in Access when it executes
queries and views.
For example, is there any difference in efficiency of the following
two queries?
1. SELECT * FROM table1, table2
WHERE table1.date=table2.date
AND table1.name like "%A%"


2. SELECT * FROM table1, table2
WHERE table1.name like "%A%"
AND table1.date=table2.date

Do you really have fields named "name" and "date"?? Bad mistake, if so!
Do not used reserved keywords for table and field names - using "date" as a
field name can especially have serious consequences.

See here for a list of reserved keywords
http://www.aspfaq.com/show.asp?id=2080

table1 is a huge table with millions of records. So if Access can
filter it with "table1.name like "%A%"" first, the join part will be
greatly speeded up.

If both the queries are the same to Access, is there any other ways I
can tell Access to execute using one plan instead of another?

No, they are the same query. I suspect you will need to filter the records
yourself in a subquery or saved query before the join. Jet (in fact most
database engines) assembles the data source first, from the sources listed
in the FROM clause, before applying any filter criteria.
So, you can either create a saved query, call it FilteredTable1 with the sql

select * from table2 where [name] like "%A%"

Then join table1 to the saved query (I prefer not to use the obsolete join
syntax you are using - and hopefully you plan to specify the fields to be
returned by the query instead of using selstar):

SELECT * FROM FilteredTable1 As q join table2
ON q.date=table2.date


Or you can use a subquery:
:
SELECT * FROM
(select * from table2 where [name] like "%A%") As q
join table2 ON q.date=table2.date

 

[Luting]

Do you really have fields named "name" and "date"?? Bad mistake, if so!

Do not used reserved keywords for table and field names - using "date" as a
field name can especially have serious consequences.

See here for a list of reserved keywordshttp://www.aspfaq.com/show.asp?id=2080

Hi Bob,

No, "name" and "date" are not the real names. I just tried to use
simple names in the example.
But thank you for your alert. I didn't know they were bad names.

And thank you very much for the answer.

 

[Klatuu]

As to efficiencies, either way you write it will be fine. After you have
created a query and when you save it, Jet uses a query optimizing system
known as Rushmore. It analyses the query requirements and determines the
most efficient way to retrieve the requested records.

 

[Bob Barrows [MVP]]

Does that include doing filtration before assembling the data source? That's
contrary to the way I was taught most database engines worked. I would be
very interested in seeing this confirmed. Unfortunately, I don't have time
to test it for myself.

Luting, can you test both methods and let us know if there is a difference?
I'd be happy to be proven wrong on this.
Oh! And there is a registry setting to allow you to see the execution plan
that Jet will use for your query. Looking at the plans should tell us more
than timing results will.

http://articles.techrepublic.com.com/5100-10878_11-5064388.html

 

[Bob Barrows [MVP]]

Ah, a sample plan was posted in the article and it appears that I was wrong.
You will not need the subquery at all.
Here are the first two steps of the plan that was shown:

01) Restrict rows of table Orders
by scanning
testing expression "Orders.ShipCity="Madrid""

02) Inner Join result of '01)' to table 'Order Details'
using index 'Order Details!OrdersOrder Details'
join expression "Orders.OrderID=[Order Details].OrderID"

Thanks for jumping in Dave, I've learned something new today.

 

[Marshall Barton]

I wonder is there any pre defined sequence in Access when it executes
queries and views.
For example, is there any difference in efficiency of the following
two queries?
1. SELECT * FROM table1, table2
WHERE table1.date=table2.date
AND table1.name like "%A%"


2. SELECT * FROM table1, table2
WHERE table1.name like "%A%"
AND table1.date=table2.date


table1 is a huge table with millions of records. So if Access can
filter it with "table1.name like "%A%"" first, the join part will be
greatly speeded up.

If both the queries are the same to Access, is there any other ways I
can tell Access to execute using one plan instead of another?

A query's execution plan is derived from many considerations
and may change in many situations, so what you are trying to
do is not something you can control.

The most important consideration to speed up a query is to
use indexed fields in straightforward comparisons,
Like "%A%" can not take advantage of indexing so it will
definately require a total table scan. Actually, any field
based calculated value (or a Like pattern that starts with
%) in the Where clause will end up in the same boat, so the
equivalent using the InStr function will have no significant
effect.

The only thing I can suggest about your query is to use a
normal Join ON clause and make sure that the two date fields
are indexed. Beyond that, I think you are stuck.

 

[Michel Walsh]

The problem with LIKE "%A%" is that no index can be used, while it may be
possible to use indexes on fields 'date', and next, the optimal plan may be
to end with the LIKE condition. So, in short, I doubt there is a unique
optimal plan that will fit all cases, but you can make tests to see if there
is a difference. As example:


SELECT * FROM (SELECT * FROM table1 WHERE name LIKE pattern) AS x INNER JOIN
table2 ON x.date=table2.date



would (probably, untested) force the LIKE operator to be executed first, and

SELECT * FROM (SELECT * FROM table1 INNER JOIN table2 ON
table1.date=table2.date) AS x WHERE x.name LIKE pattern


could force the inner join before applying the criteria implying LIKE.

I tried one MS SQL Server (easier to get precise stats), and an inner join
on indexed fields first, the last expression, got 36% of the whole batch
made of the two statements... so, it was twice faster than doing the LIKE
first. But that is on one of my database (since we need a lot of records,
isn't it... ). For your database, it can be the reverse.


Vanderghast, Access MVP

 

[Luting]

Hi everyone,

I test all the solutions you guys suggested. It turns out that all of
them work the same way.
Access will first select records that match "x.name LIKE pattern" then
it will do join.

Thank you all very much.