Performance Issue

[Matt]

Hi Everyone!

I have a 2 queries, both containing sub queries, that run in about 1-2
minutes each when run from the query designer. When I put these
queries into a module they take about 1 1/2 - 2 times as long to run
(same parameters and all).

My question is, Is this normal?

also, is there something that exists to speed a query up (Like "Hints"
in Oracle)?

Any help of references are appreciated!

Thanks Guys!

 

[Allen Browne]

When you save a query, Access saves the query plan as well. It is therefore
theoretically possible that it could take longer to run a dynamic query than
a pre-compiled one, since it does take time to determine the best plan to
use.

But I don't think I have every observed this factor in any measureable way,
so perhaps something else is happening, such as a difference in the way the
parameters are being interpreted. If that's a factor, declaring the
parameters, or explicitly typecasting calculated fields could make a
difference.

In general, Access does not show you its query plan in the way Oracle or SQL
Server does. However, you can get info on what it's up to. Here's some links
that might get you started in that direction:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp


(Hopefully those links are still current.)

 

[Guest]

You need to give us more information on the query itself. How complex is it?
How many tables are involved? Are the involved tables large? The same with
the subqueries. Are your tables normalized?

In general a form should take about the same time as the query itself, but
if the form has additional filters, it may run slower.

I would say that 1 - 2 minutes is slow, but again I don't know your
structure. Often, people design their tables so badly that they end up
creating complicated queries just to get the data they need, which takes
longer. If the table design was done properly, the query runs lightning fast.

Ray

 

[Dirk Goldgar]

Hi Everyone!

I have a 2 queries, both containing sub queries, that run in about 1-2
minutes each when run from the query designer. When I put these
queries into a module they take about 1 1/2 - 2 times as long to run
(same parameters and all).

My question is, Is this normal?

What exactly do you mean by "put these queries into a module"? Are you
talking about executing the stored queries, or are you executing the SQL
statement as a string, or what?