query runs very very slow

[cliff]

My data in tballnumcount query is something like this.. this query runs fast
and there are about 11462 records

sr numvalue numcount latest
1 14 4 10/26/2008
1 18 3 10/25/2008
1 23 1 10/23/2008
1 35 1 10/23/2008
1 37 1 10/23/2008

from the above 11462 records, I have written following query to select top
20 numvalue from each sr's but it is running very slow.

SELECT tballnumcount.SR, tballnumcount.NumValue, tballnumcount.Numcount,
tballnumcount.latest
FROM tballnumcount
WHERE (((tballnumcount.NumValue) In (SELECT TOP 20 numvalue
FROM tballNumCount as B
WHERE B.SR=tballnumcount.SR
ORDER BY b.Numcount desc,b.latest,b.Numvalue)))
ORDER BY tballnumcount.SR, tballnumcount.Numcount DESC ,
tballnumcount.latest DESC , tballnumcount.numvalue;


can you help me to overcome this problem


thanks

cliff

 

[John Spencer]

Well, you are using a correlated subquery. The subquery must run once for
every record in the database, so it is going to be slow.

Something like the following MIGHT be faster, but it won't be updateable.

SELECT A.SR, A.NumCount, A.Latest, A.NumValue,
Count(B.SR) as Rank
FROM tballNumCount as A LEFT JOIN tbalNumCount as B
ON A.SR = B.SR
AND (A.NumCount*10000000 + A.Latest*100 + A.numValue) <
(B.NumCount*10000000 + B.Latest*100 + B.numValue)
GROUP BY A.SR, A.NumCount, A.Latest, A.NumValue
HAVING Count(B.Sr) <20

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

John,

Why have you chosen to create the long numeric calculation involving
NumCount, Latest, and NumValue)?

How about using a nonequi-join.

SELECT T1.SR, T1.NumValue, T1.NumCount,
T1.Latest, Count(T2.SR) AS Rank
FROM tballnumcount AS T1
INNER JOIN tballnumcount AS T2
ON T1.SR = T2.SR AND T2.NumCount >= T1.NumCount
GROUP BY T1.SR, T1.NumValue, T1.NumCount, T1.Latest
HAVING Count(T2.SR) <=20

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John Spencer]

My understanding was that the poster was using all three items to determine
the top 20. SO, it appeared to me that I would need to incorporate all three
into the non-equi join.

Obvioiusly, if that wasn't needed, then your version is more efficient.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[cliff]

Hi john, thanks for help. You are great, Your option working wonderfully.
Now I small problem to compare result of top 20 with different last sr's and
display odd
numbers.

I use following query to find out all numbers featured in last 3/4 sr's

SELECT a.sr, B.NumValue, (count(a.Numvalue)/5) AS Numcount, max(b.cdate) AS
latest, max(b.sr) AS lastsr
FROM [fast starta and end] AS a INNER JOIN [fast start and end] AS B ON
(B.SR+[p1]>=A.SR) AND (b.sr-a.sr<=[p1]) AND (b.sr<a.sr)
GROUP BY a.sr, B.NumValue
ORDER BY a.sr, (count(a.Numvalue)/5) DESC , max(b.sr) DESC , max(b.cdate)
DESC , b.numvalue;

I replace parameter [p1] value with 3/4 to get my result. then I use query
to select top 20 numbers

Now I want compare top 20 result from say last 3 sr's with top 20 result
from say last 4 sr's and I want display numbers not figured in top 20 of
last 3 sr's which are featured in last 4 sr's. how do I do it.


please help me out to solve this problem


thanks million


cliff