Too long calculation - subquery issue?

[laurentc via AccessMonster.com]

Hi.

I have an issue with my Access project.

I have rather big tables of data (about 11 000 rows).
These tables are historical product quotations, so they are very simple :
- MyDate (PrimaryKey)
- Quote

I do some calculations on these data:
- I calculate the 10-days Average, using a subquery.
- I calculate the 10-days StDev, using a subquery.
- I use the query1 to gather all the infos ( MyDate / Quote / 10-days
average/ 10-days StDev)
- I use a form to open a chart which plots the query1.

However, when I open the chart, it takes more than 10 minutes to show the
results !
As I have quite a good Notebook (Sony 1.7Mhz, 1.25Go RAM...), I think there
is an issue in my query optimisation.

What do you think?

My queries are based on this code:
qry1_Average10:

SELECT a.Mydate, a.quote, Avg(b.quote) AS Average10
FROM Mytable AS a, Mytable AS b
WHERE(b.[MyDate]>[a].[MyDate]-10 And b.[MyDate]<=[a].[MyDate])
GROUP BY a.[MyDate],
ORDER BY a.[MyDate];
qry1_StdDev10:

SELECT a.Mydate, a.quote, StDev(b.quote) AS StdDev10
FROM Mytable AS a, Mytable AS b
WHERE(b.[MyDate]>=[a].[MyDate]-14 And b.[MyDate]<[a].[MyDate])
GROUP BY a.[MyDate],
ORDER BY a.[MyDate];

And I just ask for the fields MyDate and Quote from "Mytable" and StDev10 and
Average10 from the subqueries.

How could I make it work much faster?

Regards,

Laurent

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[Rich P]

Yes. If the query takes more than a few seconds to run that suggests a
problem somewhere - a bottleneck. Note: when I say a query should take
only a few seconds, if you have say a giant union query that is composed
of a dozen queries and that takes 3 minutes to run, I am refereing to
each individual query of the dozen.

In your case, I would isolate each individual query (if there is more
than one) and run it straight from the query builder. See how long it
takes to run in the Query tool. If it (or each query) only takes a few
seconds to run individually, then the problem is not with the query but
how you access them. If each query takes a long time by itself, then
you need to isolate that query and just post a question on that query
alone for starters.

I would work on one query at a time. A lot of times you could gain some
performance by using temporary tables to store transient data. The idea
here is that you are whittling down the size of the data rather than
querying against all of your data. The Access Jet engine doesn't do as
well with a bunch of subqueries as say the Sql Server Engine (which is
1000 times bigger than Jet).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[laurentc via AccessMonster.com]

Thanks for your answer, Rich.
My issue is that the first subquery (which calculates the average) is rather
slow, the second query (which calculate the stdev) is also rather slow (more
than 1 minute each).

And the query which takes the Average from the first subquery, the stdev from
the second subquery, and the date and close from the table is very, very slow.
.. (up to 10 minutes)
So you must be right when you say that there is an issue in my queries.
However, I cannot find how to calculate a "mobile average" or a "mobile
standard dev" without this kind of "loop" (I use the my table two times, and
I name it "a" and "b" to be able to do my calculations, as shown in my codes).
What do you think?
Regards,

Laurent


Rich P wrote:

>Yes. If the query takes more than a few seconds to run that suggests a
problem somewhere - a bottleneck. Note: when I say a query should take
only a few seconds, if you have say a giant union query that is composed
of a dozen queries and that takes 3 minutes to run, I am refereing to
each individual query of the dozen.

In your case, I would isolate each individual query (if there is more
than one) and run it straight from the query builder. See how long it
takes to run in the Query tool. If it (or each query) only takes a few
seconds to run individually, then the problem is not with the query but
how you access them. If each query takes a long time by itself, then
you need to isolate that query and just post a question on that query
alone for starters.

I would work on one query at a time. A lot of times you could gain some
performance by using temporary tables to store transient data. The idea
here is that you are whittling down the size of the data rather than
querying against all of your data. The Access Jet engine doesn't do as
well with a bunch of subqueries as say the Sql Server Engine (which is
1000 times bigger than Jet).

Rich

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200609/1

[Rich P]

Hi Laurent,

If you are calling your query in a loop several times then is it the
loop which is taking a long time? How long does it take to run the
query from the Query tool? If it takes a long time from the query tool
then what I would do is to first pull the subset of data that you are
actually querying. For example, if you are querying rows that are
between certain date ranges, I would first pull the rows that are within
those date ranges and store those rows in a temporary table (an actual
table that you can create on the fly with "Select * Into tblTempForAvg
from ..." and then query that temp table for the average and do the
same for the Sdev and so on.

If it is not possible to pull a subset of your data then you will have
to face a fact of life (which a lot of people in this NG seem to have a
problem with) that Access is designed ideally for single user desktop
usage not Enterprise multiuser usage (ideally - that is). If you have
to query against a ton of data with several subqueries, you will have to
use something with a bigger engine than Access Jet - like sql server.

Rich

*** Sent via Developersdex http://www.developersdex.com ***