Is columnstore indexes not a good fit for this for performance for another query

GokhanVarol 2017-05-28 16:41:33

I had posted a similar question a short while ago, this is another kind of query I tested with and without columnstore indexes and columnstore indexes performed poorly. Is there anything I can do to speed up the columnstore query or this is what to expect from ColumnStore indexes for this kind of workload?
Thank you in advance.

Query Columnstore 585 Seconds
Query No Columnstore 285 Seconds

SQLkiwi 2017-05-28 19:18:32
To compare like with like, you need to apply some tuning work to the columnstore case, as has been done with the non-columnstore case.

For a fair comparison, you need to fix the cardinality estimates for the grouping operations (hidden in a function?) so that the optimizer does not choose row mode partial and global aggregation (a reasonable enough strategy if the estimates were accurate).

You would also need to ensure the server has enough memory (and the query is able to access it) to handle the hashing operations. Currently the query is being granted the maximum allowed per query, meaning you need to either add memory or reconfigure workspace memory maximum grants.

As it is, the columnstore plan has many several expensive transitions between row and batch mode, and a couple of very expensive row mode spills.

The query itself may or may not be a great candidate for columnstore processing. If the aggregation operations do not reduce row counts very much, and the hash joins are large and similar on both inputs, the alternate strategy may make more sense. Only apples-to-apples testing would prove this one way or the other.

GokhanVarol 2017-05-28 19:32:56
Can I also partition the tables and use a driver per partition for columnstore queries to reduce the memory grants, I can't seem to get this to work?
SQLkiwi 2017-05-28 19:37:52
I'm not sure without trying it. If not, you can always write the apply as an explicit loop, adding rows to the temporary table on each iteration.
GokhanVarol 2017-05-29 04:25:21
This is a sample function, would it work with columnstore indexes, each column in function depends on few other columns?