The performance of a query over a large fact table with a ColumnStore index degrades non-linearly when additional result clauses are added - by SteveH_UK

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 761895 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/10/2012 3:29:24 AM
Access Restriction Public


We have a Kimball-style star schema data mart.  We have tens of millions of rows with a variety of integer and decimal data types.  We are performing aggregating SELECT queries either over the entire data in the table or over a significant slice of data. We aggregate the data according to the dimensionality and calculate aggregated values.  All data is pre-loaded into the buffer cache.  We are not using SQL Server Analysis Services, only the relational engine.

We expect the performance of the system to degrade as additional calculations are added, and as more result rows are required, however we also expect that it is cheaper to include additional SELECT clauses in a single statement versus separating the requests into multiple statements.

As the number of SELECT clauses is increased, we see a mostly linear progression in calculation time.  However, at a certain point the performance degrades substantially such that it may be cheaper to execute multiple queries rather than a single execution of the more complex query.

Full details in attachment.
Sign in to post a comment.
Posted by Microsoft on 4/15/2014 at 9:38 AM

we have made changes in SQL Server 2014 to address this issue using global batch aggregates delivered in SQL14. Would like request if you can try this scenario in the new release

Posted by Microsoft on 9/12/2012 at 3:52 PM

Thank you for reporting this performance issue with the columnstore index. We will investigate and get back to you with our findings.

Susan Price
Senior Program Manager
SQL Server Database Engine