Test run with a Columnstore index, the duration got a lot larger

GokhanVarol 2013-03-15 18:48:14

Both 2 queries return identical results. I was not happy with the performance of the first query and ended up creating a flatted out table (of Property, Trans, TransMortgage) and created a columnstore index thinking it may perform better.
Unfortunately the duration jumped from 934 seconds to 7749 seconds.
Can I assuse columnstore index may not be a good option for a query of this kind?
Thank you

SQLkiwi 2013-03-15 22:17:52
The query is not a good candidate for a columnstore index as it stands. The current CS implementation works best when aggregations can be performed in batch mode, segments can be eliminated in the CS scan, and most filtering is performed inside the CS scan.

Your query reads every row from the large table, performs a number of moderately complex computations, filters a relatively small percentage using string comparisons, and then performs most of its work in the hash join probe residual.

It might be possible to rework the design to be more CS-friendly, but it would require a fair bit of analysis and redesign. If you decide to go this route, be sure to read the TechNet article, "SQL Server Columnstore Performance Tuning".

GokhanVarol 2013-03-16 00:40:34
Thank you Paul one more time 🙂
Let me ask, this is a small subset of data, in production database the tables are larger ( about 50 times larger, server is stronger too), this first query takes a long time (I sm struggleing to perform it better). Do you see room for imorovement in the first query?
There is these gigantic queries they need to run which is touching close to tb in size each 3-10 tables snd filtered sometimes in all the tables, I have been rewriting them for days now thinking I can make a considerabke change but having no luck.