TOP 20 PER Marketplace

[Guest]

Hello I have following SQL:
SELECT qryTempeAccounts.*
FROM qryTempeAccounts
WHERE qryTempeAccounts.[Total Over 60] In ( SELECT TOP 20 T.[Total Over
60] from qryTempeAccounts As T WHERE T.marketplace =
qryTempeAccounts.marketplace ORDER BY [Total Over 60] DESC )
ORDER BY [qryTempeAccounts].[marketplace];

What I am trying to do is pull top 20 per marketplace based on the highest
over 60 days amount. (top 20 highest amount for each marketplace)
Table itself only have 73,000 records ( I don't considered this alot for
Access). I tried running it and it's been 30 minutes and still no result!
Anyone has better idea how I can get this query to run??

thank you!

 

[John Spencer]

Make sure you have indexes on proper fields. But I suspect that the problem
has to do with qryTempeAccounts having to run multiple times. Perhaps
that can be optimized in the subquery. Also, if qryTempeAccounts has an
ORDER BY clause drop it.

Suggest you post the code for qryTempeAccounts in addition to what you
already have posted. Wild guess that the subquery might end up looking
something LIKE the following


In (SELECT TOP 20 AccountID
FROM Sometable as T
WHERE T.Marketplace = qryTmpeAccounts.Marketplace
GROUP BY AccountID
ORDER BY Sum(Amount) DESC)

SUM(Amount)

 

[Michel Walsh]

Hi,


You have an index on marketPlace and on [Total Over 60] ? Probably not,
since you call the table "qryTempeAccount", which is, I suppose, a query.
Make a temporary table out of the query, and index the mentioned fields.


If this it still too slow, try to join (to compute a rank) rather than to
"sub-query":


SELECT a.marketPlace, a.totalOver60
FROM myTable As a LEFT JOIN myTable as b
ON a.marketPlace = b.marketPlace AND a.totalOver60 < b.totalOver60
GROUP BY a.marketPlace, a.totalOver60
HAVING COUNT(b.totalOver60) < 10



In that query, COUNT(b.totalOver60) supplies the rank, within the same
marketPlace, occupied by the value a.totalOver60. I use a LEFT join and
strict < comparisons to include all the ex-equo that could eventually fill
position 10.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you both!!
It runs so much quicker! !

Hi,


You have an index on marketPlace and on [Total Over 60] ? Probably not,
since you call the table "qryTempeAccount", which is, I suppose, a query.
Make a temporary table out of the query, and index the mentioned fields.


If this it still too slow, try to join (to compute a rank) rather than to
"sub-query":


SELECT a.marketPlace, a.totalOver60
FROM myTable As a LEFT JOIN myTable as b
ON a.marketPlace = b.marketPlace AND a.totalOver60 < b.totalOver60
GROUP BY a.marketPlace, a.totalOver60
HAVING COUNT(b.totalOver60) < 10



In that query, COUNT(b.totalOver60) supplies the rank, within the same
marketPlace, occupied by the value a.totalOver60. I use a LEFT join and
strict < comparisons to include all the ex-equo that could eventually fill
position 10.



Hoping it may help,
Vanderghast, Access MVP

Hello I have following SQL:
SELECT qryTempeAccounts.*
FROM qryTempeAccounts
WHERE qryTempeAccounts.[Total Over 60] In ( SELECT TOP 20 T.[Total
Over
60] from qryTempeAccounts As T WHERE T.marketplace =
qryTempeAccounts.marketplace ORDER BY [Total Over 60] DESC )
ORDER BY [qryTempeAccounts].[marketplace];

What I am trying to do is pull top 20 per marketplace based on the highest
over 60 days amount. (top 20 highest amount for each marketplace)
Table itself only have 73,000 records ( I don't considered this alot for
Access). I tried running it and it's been 30 minutes and still no result!
Anyone has better idea how I can get this query to run??

thank you!