Strange way of optimizing a query

[Ryan]

I came across a situation that I've been unable to explain and was
hoping somebody had an answer:

I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1 second
and looked exactly the same as before. I later realized that if I
take the original query, change it to SQL view and run it, then change
it back to design view and save it, it acomplishes the same thing.
The query looks exactly the same as before, but appears to have been
optimized somehow by running as a SQL statement first (either from
code or simply from the SQL view of the query).

Just to clear this from the discussion, I understand that a query can
run faster the second time around (I believe do to optimization), so I
made sure that when I compared them, I always ran them twice to
eliminate this from being the answer. Even after running the query
several times in its original state, it took 8 seconds. After simply
changing the view to SQL, running it, and changing it back, it always
takes 1 second thereafter.

If anybody has an answer to this, I would appreciate it.

Ryan

[Allen Browne]

When you save a query and run it, JET performs and saves an execution plan.
The execution plan is not recalculated unless the query is altered.

If the data has changed radically since the query was created, the saved
execution plan may be completely inappropriate. For example, if you create
the query with a few dozen records and later execute it with many thousands
of records, the saved plan may be completely wrong. If you then change the
SQL statement (or generate one dynamically in code), Access recalculates the
execution plan and so the query runs in a way that is much more appropriate
to the new data.

The issues regarding execution plan are quite different from the caching of
the data which speeds up the retrieval of the information from the disk on
subsequent reads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ryan" <[e-mail]> wrote in message
news:cf*************************@posting.google.co m...

I came across a situation that I've been unable to explain and was
hoping somebody had an answer:

I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1 second
and looked exactly the same as before. I later realized that if I
take the original query, change it to SQL view and run it, then change
it back to design view and save it, it acomplishes the same thing.
The query looks exactly the same as before, but appears to have been
optimized somehow by running as a SQL statement first (either from
code or simply from the SQL view of the query).

Just to clear this from the discussion, I understand that a query can
run faster the second time around (I believe do to optimization), so I
made sure that when I compared them, I always ran them twice to
eliminate this from being the answer. Even after running the query
several times in its original state, it took 8 seconds. After simply
changing the view to SQL, running it, and changing it back, it always
takes 1 second thereafter.

If anybody has an answer to this, I would appreciate it.

Ryan

[Ryan]

"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<7P********************@news-server.bigpond.net.au>...

When you save a query and run it, JET performs and saves an execution plan.
The execution plan is not recalculated unless the query is altered.

If the data has changed radically since the query was created, the saved
execution plan may be completely inappropriate. For example, if you create
the query with a few dozen records and later execute it with many thousands
of records, the saved plan may be completely wrong. If you then change the
SQL statement (or generate one dynamically in code), Access recalculates the
execution plan and so the query runs in a way that is much more appropriate
to the new data.

The issues regarding execution plan are quite different from the caching of
the data which speeds up the retrieval of the information from the disk on
subsequent reads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ryan" <[e-mail]> wrote in message
news:cf*************************@posting.google.co m...

I came across a situation that I've been unable to explain and was
hoping somebody had an answer:

I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1 second
and looked exactly the same as before. I later realized that if I
take the original query, change it to SQL view and run it, then change
it back to design view and save it, it acomplishes the same thing.
The query looks exactly the same as before, but appears to have been
optimized somehow by running as a SQL statement first (either from
code or simply from the SQL view of the query).

Just to clear this from the discussion, I understand that a query can
run faster the second time around (I believe do to optimization), so I
made sure that when I compared them, I always ran them twice to
eliminate this from being the answer. Even after running the query
several times in its original state, it took 8 seconds. After simply
changing the view to SQL, running it, and changing it back, it always
takes 1 second thereafter.

If anybody has an answer to this, I would appreciate it.

Ryan

Thank you for the response, Allen. That was very helpful. Is there
another way of recalculating the execution plans on a query or all
queries other than the way I described?

Ryan

[Allen Browne]

I think you will find that compacting the database causes it to recalculate
the query plan. That would be the simplest approach for a database in
operation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ryan" <[e-mail]> wrote in message
news:cf**************************@posting.google.c om...

"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<7P********************@news-server.bigpond.net.au>...

When you save a query and run it, JET performs and saves an execution plan. The execution plan is not recalculated unless the query is altered.

If the data has changed radically since the query was created, the saved
execution plan may be completely inappropriate. For example, if you create the query with a few dozen records and later execute it with many thousands of records, the saved plan may be completely wrong. If you then change the SQL statement (or generate one dynamically in code), Access recalculates the execution plan and so the query runs in a way that is much more appropriate to the new data.

The issues regarding execution plan are quite different from the caching of the data which speeds up the retrieval of the information from the disk on subsequent reads.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ryan" <[e-mail]> wrote in message
news:cf*************************@posting.google.co m...

I came across a situation that I've been unable to explain and was
hoping somebody had an answer:

I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything, I copied the SQL statement back into
a query and tried running it again. It now also only took 1 second
and looked exactly the same as before. I later realized that if I
take the original query, change it to SQL view and run it, then change
it back to design view and save it, it acomplishes the same thing.
The query looks exactly the same as before, but appears to have been
optimized somehow by running as a SQL statement first (either from
code or simply from the SQL view of the query).

Just to clear this from the discussion, I understand that a query can
run faster the second time around (I believe do to optimization), so I
made sure that when I compared them, I always ran them twice to
eliminate this from being the answer. Even after running the query
several times in its original state, it took 8 seconds. After simply
changing the view to SQL, running it, and changing it back, it always
takes 1 second thereafter.

If anybody has an answer to this, I would appreciate it.

Ryan

Thank you for the response, Allen. That was very helpful. Is there
another way of recalculating the execution plans on a query or all
queries other than the way I described?

Ryan

[Ryan]

>

Hmm... interesting. So, how do you force Access to reoptimize your
queries Loop through them in VB and do some minor thing then undo it
to force the compiler to see the query as changed? (Okay, I'll go
back to smoking crack now...)

My question exactly. By the way, I work for a drug testing company
(i.e. smoking crack) (<:

Allen, I tried compacting the database and even repairing it. Neither
one seemed to effect anything. I even tried making a small change to
the query, saving it, and running it again and still no change. The
only time it makes a major difference (from over 30 seconds to less
than 5) is when I change the query view to SQL, run it, then change it
back to design view and run it again. When the query is in SQL view,
it is still slow and sometimes I even need to break it because it
takes so long. However, as soon as I switch back to design view, it
is lightning fast compared to what it was. Also, if I copy the SQL
statement out of the query and run it in code, it is very fast.

I understand what you are saying about the "execution plan" and it
makes sense, because the tables did indeed grow to thousands of
records since it was written. I would just like to know how I could
re-calculate all of the execution plans. Although, I've noticed that
this set of events only seems to help on my update queries, not
others.

Ryan

[Allen Browne]

Interesting, Ryan. I've never had reason to test MS's claim that compacting
causes the query plan to be recalc'd.

You could try reassigning the SQL property of the QueryDef - even assigning
it to itself:

For Each qdf in db.QueryDefs
qdf.SQL = qdf.SQL
Next

Over time this approach may cause the database to bloat.
Interestingly, I think there is a training CrLf at the end of the SQL
statement built through the query design grid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ryan" <[e-mail]> wrote in message
news:cf**************************@posting.google.c om...

Hmm... interesting. So, how do you force Access to reoptimize your
queries Loop through them in VB and do some minor thing then undo it
to force the compiler to see the query as changed? (Okay, I'll go
back to smoking crack now...)

My question exactly. By the way, I work for a drug testing company
(i.e. smoking crack) (<:

Allen, I tried compacting the database and even repairing it. Neither
one seemed to effect anything. I even tried making a small change to
the query, saving it, and running it again and still no change. The
only time it makes a major difference (from over 30 seconds to less
than 5) is when I change the query view to SQL, run it, then change it
back to design view and run it again. When the query is in SQL view,
it is still slow and sometimes I even need to break it because it
takes so long. However, as soon as I switch back to design view, it
is lightning fast compared to what it was. Also, if I copy the SQL
statement out of the query and run it in code, it is very fast.

I understand what you are saying about the "execution plan" and it
makes sense, because the tables did indeed grow to thousands of
records since it was written. I would just like to know how I could
re-calculate all of the execution plans. Although, I've noticed that
this set of events only seems to help on my update queries, not
others.

Ryan

[Ryan]

"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<sO********************@news-server.bigpond.net.au>...

Interesting, Ryan. I've never had reason to test MS's claim that compacting
causes the query plan to be recalc'd.

You could try reassigning the SQL property of the QueryDef - even assigning
it to itself:

For Each qdf in db.QueryDefs
qdf.SQL = qdf.SQL
Next

Over time this approach may cause the database to bloat.
Interestingly, I think there is a training CrLf at the end of the SQL
statement built through the query design grid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hey Allen,

I tried the above and it did work. Thank you very much. After I
optimized the query, I tried making change to it to see if it remains
fast and it did. I suppose I'll just have to re-set the SQL statement
with the above method every so often to keep things optimized in the
event of major data increases.

Thanks again for the assistance.
Ryan