Why is this qry slow?

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

Guest

Hi all!

I have a rather big database (~2000 records in each of two tables). From
these tables, I have several querys to sort and filter data etc. The problem
is with the one pasted below. It is extremely slow (several minutes), even
when trying it with a 200-recs table. When applying it to a 10-record set it
works perfectly.

It should simply pick out the 5 most recent date-times of calibration
occations for every parametername in the database (or more specifically, a
query [qryEvaluateGreatestDevFromEachCal]).

SELECT qryEvaluateParamname.Paramname,
qryEvaluateGreatestDevFromEachCal.DateTimeCorrect
FROM qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname
WHERE (((qryEvaluateGreatestDevFromEachCal.DateTimeCorrect) In (Select Top 5
[DateTimeCorrect] From qryEvaluateGreatestDevFromEachCal Where
[Paramname]=[qryEvaluateParamname]![Paramname])))
ORDER BY qryEvaluateGreatestDevFromEachCal.DateTimeCorrect DESC;

Any ideas how to improve this query to make it work reasonably fast?

Thankful for any suggestions!
/Peter the Swede
 
I should mention that the two queries used as sources of data below, runs
very fast, regardless of the number of records.

Peter the Swede

"Peter the Swede" skrev:
 
Because it has to run a "top 5 query" for every line in
qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname

So it has to sort the records 4000,000 times.

It would probably be faster to join all of the records, then
sort and group all of them once.

(david)
 
Uhu, I see. Didn't think that 4 000 000 was to much for an up-to-date
computer, but you're obviously right.

I'll try some joining!

Thanx!

"david@epsomdotcomdotau" skrev:
Because it has to run a "top 5 query" for every line in
qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname

So it has to sort the records 4000,000 times.

It would probably be faster to join all of the records, then
sort and group all of them once.

(david)


Peter the Swede said:
Hi all!

I have a rather big database (~2000 records in each of two tables). From
these tables, I have several querys to sort and filter data etc. The problem
is with the one pasted below. It is extremely slow (several minutes), even
when trying it with a 200-recs table. When applying it to a 10-record set it
works perfectly.

It should simply pick out the 5 most recent date-times of calibration
occations for every parametername in the database (or more specifically, a
query [qryEvaluateGreatestDevFromEachCal]).

SELECT qryEvaluateParamname.Paramname,
qryEvaluateGreatestDevFromEachCal.DateTimeCorrect
FROM qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname
WHERE (((qryEvaluateGreatestDevFromEachCal.DateTimeCorrect) In (Select Top 5
[DateTimeCorrect] From qryEvaluateGreatestDevFromEachCal Where
[Paramname]=[qryEvaluateParamname]![Paramname])))
ORDER BY qryEvaluateGreatestDevFromEachCal.DateTimeCorrect DESC;

Any ideas how to improve this query to make it work reasonably fast?

Thankful for any suggestions!
/Peter the Swede
Click to expand...
Click to expand...
 
Uhu, I see. Didn't think that 4 000 000 was to much for an up-to-date

It's the sorting that's the killer. 4 000 000 sort queries
takes a while, even on a modern PC.

(david)

Peter the Swede said:
Uhu, I see. Didn't think that 4 000 000 was to much for an up-to-date
computer, but you're obviously right.

I'll try some joining!

Thanx!

"david@epsomdotcomdotau" skrev:
Because it has to run a "top 5 query" for every line in
qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname

So it has to sort the records 4000,000 times.

It would probably be faster to join all of the records, then
sort and group all of them once.

(david)


message
Hi all!

I have a rather big database (~2000 records in each of two tables).
From
these tables, I have several querys to sort and filter data etc. The problem
is with the one pasted below. It is extremely slow (several minutes),
even
when trying it with a 200-recs table. When applying it to a 10-record
set it
works perfectly.

It should simply pick out the 5 most recent date-times of calibration
occations for every parametername in the database (or more
specifically, a
query [qryEvaluateGreatestDevFromEachCal]).

SELECT qryEvaluateParamname.Paramname,
qryEvaluateGreatestDevFromEachCal.DateTimeCorrect
FROM qryEvaluateGreatestDevFromEachCal, qryEvaluateParamname
WHERE (((qryEvaluateGreatestDevFromEachCal.DateTimeCorrect) In (Select
Top 5
[DateTimeCorrect] From qryEvaluateGreatestDevFromEachCal Where
[Paramname]=[qryEvaluateParamname]![Paramname])))
ORDER BY qryEvaluateGreatestDevFromEachCal.DateTimeCorrect DESC;

Any ideas how to improve this query to make it work reasonably fast?

Thankful for any suggestions!
/Peter the Swede
Click to expand...
Click to expand...
Click to expand...
 
Back
Top