Top N query never finishes processing.

[PaulCL]

I have a table(Orders) with 5 million records that also contains 250 unique groups(Markets). I am trying to apply a Top N query to retrieve the top 3 records(FirmNames), based on TotalVolume, from each unique group. My current query never finishes running. Is there a more efficient way to retrieve these top 3 records? Thanks

SELECT Orders.Market, Orders.TotalVolume, Orders.FirmName INTO [Top 3 Luxury Market Final]
FROM Orders
WHERE (((Orders.TotalVolume) In (SELECT TOP 3 TotalVolume
FROM Orders AS Dupe
WHERE Dupe.Market = Orders.Market
ORDER BY Dupe.TotalVolume DESC, Dupe.FirmName DESC)))
ORDER BY Orders.Market, Orders.TotalVolume DESC , Orders.FirmName;

 

[apr pillai]

I think it will be faster if you try to simplify the process in more than one step, considering the large volume of data you are dealing with. The IN Clause in your Query itself is a delaying factor.

Create a temporary table by summarizing on Company & Order-wise total value. Creating this table itself will eliminate detailed items under each Order and reduce the table size.

Try running your Query on this temporary table or create SELECT Query with TOP N setting to get the result.

If you need this to be done frequently then sequence the process in a Macro and run from a command button click.

 

[PaulCL]

I think it will be faster if you try to simplify the process in more than one step, considering the large volume of data you are dealing with. The IN Clause in your Query itself is a delaying factor.

Create a temporary table by summarizing on Company & Order-wise total value. Creating this table itself will eliminate detailed items under each Order and reduce the table size.

Try running your Query on this temporary table or create SELECT Query with TOP N setting to get the result.

If you need this to be done frequently then sequence the process in a Macro and run from a command button click.

Thanks for the help.