Running SUM across large amount of rows

Running SUM across large amount of rows

回答1

You can take a look to the column store in SQL Server. In short, you are able to create a column store index on your tables - different from the traditional row store index.

These indexes are specially design for optimizing aggregate queries when huge amount of data is involved (for example, like in Data Warehouse star and snowflake schemes).

From the docs:

Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes.

because:

• Data compression - you can many benefits from here; for example, columnstore indexes read compressed data from disk, which means fewer bytes of data need to be read into memory;
• Column elimination - columnstore indexes skip reading in columns that are not required for the query result and further reduces I/O for query execution and therefore improves query performance (not like rowstore indexes)
• Rowgroup elimination - optimize table scans using metadata to eliminate specific rowgroups based on your filtering criteria;
• Batch Mode Execution - prior to SQL Server 2019, only queries involving such indexes, can benefit from batch mode processing which reduce your execution time further (check this video to see how great is the this mode)