Running long queries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query involving 23,000+ records (where 5 numbers are averaged per
record). Is there any way to speed up the process?

Thanks.
 
My subquery looks like this:
SELECT T1.Earnings.PIN, T1.End_date, T1.Earnings
FROM Queryavgearn AS T1
WHERE 5 >= ( SELECT COUNT(*) FROM Queryavgearn WHERE Earnings.PIN =
T1.Earnings.PIN AND [End_date] >= T1.[End_date])
ORDER BY Earnings.PIN DESC;

The next query looks like this:
SELECT Querylastfiveyears.PIN, Avg(Querylastfiveyears.Earnings) AS
AvgOfEarnings
FROM Querylastfiveyears
GROUP BY Querylastfiveyears.PIN
WITH OWNERACCESS OPTION;

I've tried running the last query before...after a day and a half it still
wasn't finished (my computer is less than 6 months old and I'm using Access
2003). Is it just a matter of having to process a lot of data or is there
something I can do to speed it up? I've tried indexing but that doesn't seem
to have helped very much.
 
If you run it with a constant in the place of the avg, does that speed it
up? Just a debug thing to see where the problem is...

How fast does the inner query run if you run it alone?

Have you tried the suggestions here:
http://builder.com.com/5100-6388-5064388.html

HTH;

-Amy

A said:
My subquery looks like this:
SELECT T1.Earnings.PIN, T1.End_date, T1.Earnings
FROM Queryavgearn AS T1
WHERE 5 >= ( SELECT COUNT(*) FROM Queryavgearn WHERE Earnings.PIN =
T1.Earnings.PIN AND [End_date] >= T1.[End_date])
ORDER BY Earnings.PIN DESC;

The next query looks like this:
SELECT Querylastfiveyears.PIN, Avg(Querylastfiveyears.Earnings) AS
AvgOfEarnings
FROM Querylastfiveyears
GROUP BY Querylastfiveyears.PIN
WITH OWNERACCESS OPTION;

I've tried running the last query before...after a day and a half it still
wasn't finished (my computer is less than 6 months old and I'm using
Access
2003). Is it just a matter of having to process a lot of data or is there
something I can do to speed it up? I've tried indexing but that doesn't
seem
to have helped very much.




A said:
I have a query involving 23,000+ records (where 5 numbers are averaged
per
record). Is there any way to speed up the process?

Thanks.
Click to expand...
Click to expand...
 
Hi A,

What is the SQL for "Queryavgearn"
and what is the structure of table "Earnings?"

I have never seen nor needed a "double-dot"
field in a (nonSQL Server) query, i.e.,
what is going on with

T1.Earnings.PIN

Can PIN come from more than one table
in "Queryavgearn?" Is that how you ended
up with this?


Can't you give it your
own single fieldname in "Queryavgearn,"
say like "QPIN" or such?

SELECT ...,
Earnings.PIN As QPIN,
.....

Other than above...

I might first turn your "subquery" into a
make table query just to "divide and conquer."

SELECT
T1.QPIN,
T1.End_date,
T1.Earnings,
( SELECT COUNT(*)
FROM Queryavgearn As T2
WHERE
T2.QPIN = T1.QPIN
AND
T2.[End_date] >= T1.[End_date]) As cnt
FROM Queryavgearn AS T1
INTO tblTest
ORDER BY T1.QPIN DESC;

just to verify it runs "faster" (or not).

does this complete in less than a day? 8-)

A said:
My subquery looks like this:
SELECT T1.Earnings.PIN, T1.End_date, T1.Earnings
FROM Queryavgearn AS T1
WHERE 5 >= ( SELECT COUNT(*) FROM Queryavgearn WHERE Earnings.PIN =
T1.Earnings.PIN AND [End_date] >= T1.[End_date])
ORDER BY Earnings.PIN DESC;

The next query looks like this:
SELECT Querylastfiveyears.PIN, Avg(Querylastfiveyears.Earnings) AS
AvgOfEarnings
FROM Querylastfiveyears
GROUP BY Querylastfiveyears.PIN
WITH OWNERACCESS OPTION;

I've tried running the last query before...after a day and a half it still
wasn't finished (my computer is less than 6 months old and I'm using
Access
2003). Is it just a matter of having to process a lot of data or is there
something I can do to speed it up? I've tried indexing but that doesn't
seem
to have helped very much.




A said:
I have a query involving 23,000+ records (where 5 numbers are averaged
per
record). Is there any way to speed up the process?

Thanks.
Click to expand...
Click to expand...
 
I'm working with tables having nearly 2 million rows each.

And I've noticed that, by far, the aspect of the whole process that takes
the most time is sorting.

Try leaving the ORDER statement out to see if that helps. You can always
sort your results later, with some other program -- that's what I'm doing,
with a good text editor.

Nicholas
 
Back
Top