When adding additional SELECT clauses to an aggregating query over a large fact table and using ColumnStore, the performance degrades in a step-wise linear fashion with large steps. It may be quicker to execute several less-complex queries rather than a single complex query.
I’ve submitted a Microsoft Connect bug report here: https://connect.microsoft.com/SQLServer/feedback/details/761895/.
Continue reading for full details, or download this attachment: https://www.box.com/s/5hp9f0ditg0fspghu506 [PDF file also available via Microsoft Connect]. Actual test results, steps to reproduce, etc., as well as more pretty graphs :), are included.
Essentially, the performance of a non-grouping SQL SELECT query degrades when applied to a ColumnStore index. This has been tested with SQL Server 2012 RTM CU1. Performing partial aggregation can result in a 15x performance improvement in some of the cases I observed.
See the full details in my Microsoft Connect submission here: https://connect.microsoft.com/SQLServer/feedback/details/761469/. Or download the details here: https://www.box.com/s/frf7imhyclb2efz2tfvb.
This is a technical post and is probably worth skipping if you don’t have a background in IT.
The HTTP protocol is moderately efficient at single transmissions that it was originally designed for, such as the HTTP GET requests that are used to retrieve a web pages HTML source. However, this moderate efficiency is signficiantly reduced by the actual behaviour and semantics of website content and browsers. Continue reading