Search code examples
sqlsql-serverquery-performance

SQL Server - GROUP BY with HAVING on more than 10 million rows hitting performance

SQL Server - how to improve query performance with GROUP BY and with HAVING clause of 30 + OR conditions on More than 10 million rows

Solution

  • We can be more helpful if you show us your query (you can obfuscate it if you need), but generically you can create computed, persisted, bit columns that pre-calculate the OR statements for you:

    https://blog.sqlauthority.com/2016/04/27/sql-server-computed-column-conditions-case-statement/

    Instead of:

    HAVING [A] > 100000 OR [B] < 1000

    Use:

    ALTER TABLE [FOO]
ADD IsFiltered AS CASE WHEN [A] > 100000 OR [B] < 1000 THEN 1 ELSE 0 END PERSISTED

    And then add [IsFiltered] to an index for extra speed. You can also create functions to perform calculations for you:

    formula for computed column based on different table's column

    Alternatively, it may be time to bite the bullet and create these calculations in an SSAS cube. That can be a big leap, but cubes can provide a lot of insight into your data.