Is Access smart enough to optimize this ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mathieu.page@gmail.com - 20 Dec 2005 16:54 GMT

Hi,

I often have recursives queries in my applications, like in this
simplified example :

req1 :
SELECT EmployeNo, EmployeName, EmployePhone
FROM Employe;

req2 :
SELECT *
FROM req1
WHERE EmployeNo = 200;

The non optimal way to implement this is to run req1 first then run
req2 on the result of req1. The optimized way to do it is to move the
condition (EmployeNo = 200) at the begining of the execution plan in
order to avoid to load every rows of Employe.

This is a simplified example. I have more complex exemples involving
UNION queries. I want to know if Access will do this kind of
optimizations all by himself or do I have to hardcode them by moving
the conditions in the deeper queries.

I examined showplan.out and it seem that the conditions are not moved.
However I'm not an expert at understanding execution plans and the
header of the file says : "Currently does not handle subqueries, vt
parameters, and subqueries".

Do you think that this kind of optimisation are automated? If yes, in
wich cases?

Mathieu Pagé
mathieu.page@gmail.com

Reply to this Message

Lyle Fairfield - 20 Dec 2005 18:11 GMT

Maybe.

There are 24 347 296 factors which may impact on JET optimization.
These factors may change over time, machine and data.
A query plan which is optimized for my machine may not be an optimal
plan for yours. A query plan which is optimized for my machine today
may not be an optimal plan for my machine tomorrow. A query
optimization plan that is efficient for ten thousand records may not be
efficient for a million records. A query optimization plan that is
efficient for the parameter "USA" may not be efficient for the
parameter "United States of America". (These are two reasons why saved
optimizations may result in poor performance). What is the best
optimization for your query when Employe has one record?

My answer to your question is:
1. Maybe;
2. If I can help or direct optimization by writing or revising a few
extra lines of SQL, I do; why leave things to chance?

(Of course there may be those who will 100% KNOW what happens every
time; at least one of those savants post here; perhaps he will advise.)

Reply to this Message

mathieu.page@gmail.com - 20 Dec 2005 19:01 GMT

Hi Lyle,

Thanks for your answer.

I don't like to do direct optimisations in my SQL code if I can help,
because this make it less clear. Having a parameter hiden in a
sub-sub-queries is not pretty, I think.

Reply to this Message

Pachydermitis - 20 Dec 2005 19:06 GMT

Lyle is right.
My experience is that access is only fair at its optimization. At
times I have been surprised that it did so well, but more often I end
up having to write my queries to do it myself. Your example
specifically probably won't make a difference. Since you use a lot of
Unions and since union queries are the very worst and slowest of all,
limit their subqueries in any way you can prior to the union. Anyway
SELECT EmployeNo, EmployeName, EmployePhone FROM Employe WHERE
EmployeNo = 200; is better.
HTH

Reply to this Message

Bri - 20 Dec 2005 20:08 GMT

> Hi,
>
[quoted text clipped - 30 lines]
> Mathieu Pagé
> mathieu.page@gmail.com

I use similar queries myself. Usually, the query does not have criteria,
but limits the fields (I can then use it as a RWOP query for users in
groups that are not allowed to see all of the data). I then base other
queries on these base queries (or more likely create an SQL string in
VBA based on them). I haven't noticed any performance issues doing this
vs putting all of it in one query. Yes, it might run slower, but not
enough to be noticed. In fact, I have had some queries run faster by
breaking them down into two nested queries. This allowed me to 'direct'
the optimizer (no, I can't prove this, just seems that way from imperial
results) into doing things in the right order. Union queries, however,
are a bit finicky and seem to 'unoptimize' on a whim.

Bottom line is that optimization is a black box. Lyle has hit the nail
on the head with his answer of "Maybe". The only way to be sure is to
try it both ways and see if there is a noticable difference. It usually
isn't to hard to tell if a query hasn't been optimized.

Good luck.

--
Bri

Reply to this Message

Larry Linson - 20 Dec 2005 20:30 GMT

Lyle properly points out that there are many, many factors involved in
efficient data retrieval. It is difficult for me to imagine, however, that

SELECT EmployeNo, EmployeName, EmployePhone
FROM Employe
WHERE EmployeNo = 200;

would ever be _less_ efficient than the combination.

On the other hand, because the Jet database engine is good at optimization,
there are possibly circumstances in which it would not be measurably more
efficient, either. And, in fact, the only difference _might_ be in Jet's
preparation time, because they may both end up executing exactly the same.

Our only consolation is that the Jet database engine IS good at optimization
(yet, after reflecting for a moment, I add "except when it isn't").

Larry Linson
Microsoft Access MVP

Reply to this Message