Geek City: Build a Big Table with a Columnstore Index

re: Geek City: Build a Big Table with a Columnstore Index

Mike Tarlton — Fri, 27 Apr 2012 21:33:46 GMT

Thank you for making it available. I Think I'll use it and modify a little in accordance with my necessaty. I will comment agian with my results.

re: Geek City: Build a Big Table with a Columnstore Index

Andrei — Wed, 09 May 2012 21:05:58 GMT

Hi Kalen:

thank you very much for your post! I did everything you suggested and than ran this query (over LAN, with Columnstore Index in place, SQL Server 2012 is running on remote machine with quadcore, 16GB RAM)

SELECT [SalesOrderNumber], sum([SalesAmount])

FROM [AdventureWorksDW2012].[dbo].[FactInternetSalesBig]

group by SalesOrderNumber

It succesfully returns 14161408 rows in 3 minutes.

When I ran the same Query vs. my Local copy of AdventureWorksDW2012 database (but without Columnstore Index and with no LAN involved (everything LOCAL on my Workstation, including SQL Server 2012) it took 6 minutes. Configuration of my Workstation is quadcore, 16GB RAM.

Andrei,

http://apandre.wordpress.com/

re: Geek City: Build a Big Table with a Columnstore Index

Kalen Delaney — Wed, 09 May 2012 23:06:43 GMT

Thanks for the data point, Andrei!

re: Geek City: Build a Big Table with a Columnstore Index

Andrei — Thu, 10 May 2012 18:58:04 GMT

Hi Kalen:

just in order to compare apples vs. apples, I rerun the same query locally on server I mentioned above (so no LAN involved). It returns those 14161408 rows in less then 100 seconds, almost twice faster then over LAN.

Andrei,

http://apandre.wordpress.com/

re: Geek City: Build a Big Table with a Columnstore Index

Paul Brewer — Wed, 23 May 2012 15:45:21 GMT

Hi Kalen,

We managed to get Star optimizations appearing in our BI query execution plans at work after much database redesign. Attempted to compare the performance of these against column store indexes was a nightmare though. With no decent documentation to go on, it's impossible trying to reverse engineer the query optimizer logical as to and when, how and why it makes best use of column store indexes.

I did try, see http://paulbrewer.dynalias.com/SitePages/CS_Index.aspx

Really looking forward to seeing your book when it comes out. I've read all your previous ones and they are the backbone of my skillset. I just pasted the Microsoft Certified Master of SQL Server 2008 knowledge exam thanks in no small part to your books.

Regards

Paul

re: Geek City: Build a Big Table with a Columnstore Index

Darryll Petrancuri — Fri, 13 Jul 2012 02:19:35 GMT

Kalen: I have a question for you that unfortunately is likely to have an 'it depends' answer. That being said, I'd like your first blush, off the cuff answer nonetheless.

I am working in an environment where, for better or worse, the BI queries done against our DW are entirely ad-hoc (ROLAP via Microstrategy). We have used ColumnStore Indexes in a partitioned Fact table, partitioned at the Day grain and this is workinq very nicely for us.

The question I have is regarding our Dimension tables. Because the queries are entirely ad-hoc, every single one of the attributes in our dimensions are key / value pairs, and every single one of the keys (integers) are indexed in separate indexes. Upserts take a long time because of the sheer number of attributes / indexes. Read-Only considerations due to the use of ColumnStore Indexes not withstanding, do you think we'd see better performance if we basically truncated a dimension, did straight Inserts and then added a ColumnStore for all the key columns instead of relying on more 'traditional' approaches to dimensional maintenance / ETL?

I look forward to your learned feedback.

Respectfully,

Darryll