SQL Server Performance

group by performance tuning

Discussion in 'ALL SQL SERVER QUESTIONS' started by afsar, Jul 2, 2012.

  1. afsar New Member

    Hello,

    I have a big table(100.000.000 records) and a query as following works very slow.
    Is there any way to change this sql for increasing performance?
    And what do you suggest to check?

    select top 10 ProductID, count(1) as Total FROM Sales with (nolock)
    GROUP BY ProductID
    ORDER BY Total DESC

    Thanks in advance
  2. FrankKalis Moderator

    Welcome to the forum!
    Since you want the 10 products with the highest COUNT(), SQL Server has to scan the whole index every time to derive this information. Even with an index and the NOLOCK hint in place, this is gonna take quite some time and there is hardly anything you can do to improve this. However, if you are in full control of the database, what you could do is to have another table with the precalculated number of sales per product. Depending on the requirements from the users that table has to be updated every time something happens to the sales table to reflect the current state of sales or, if they are happy with approximate numbers, such update could happen over night in another process. Once you have such a table in place, a query like the above is trivial.

Share This Page