August 20, 2014 at 9:18 am
This result should tell you:
Query Rows Time of execution
w/o compressionwith compressionwith Columnar Indexes select * from warehouse.[dbo].[Timekeeper_Matter_Time_Summary_Period] 1,102,894 69 371
select * from warehouse.[dbo].Matter_Timekeeper_History 2,796,014 78 7878
basically the query runs approximately 5.5 times longer with columnar index in one table and no improvement in the second one.
anybody have any clues as to where I should look?
Cheers
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 20, 2014 at 9:37 am
Paresh Motiwala (8/20/2014)
This result should tell you:Query Rows Time of execution
w/o compressionwith compressionwith Columnar Indexes select * from warehouse.[dbo].[Timekeeper_Matter_Time_Summary_Period] 1,102,894 69 371
select * from warehouse.[dbo].Matter_Timekeeper_History 2,796,014 78 7878
basically the query runs approximately 5.5 times longer with columnar index in one table and no improvement in the second one.
anybody have any clues as to where I should look?
Cheers
Paresh
Quick thought, have you compared this using aggregation? You should look into the intended use of this feature.
😎
August 20, 2014 at 11:18 am
I am just running a bench marking test. With and without columnstore indexes....
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 20, 2014 at 11:37 am
Paresh Motiwala (8/20/2014)
I am just running a bench marking test. With and without columnstore indexes....
Columnstore index will not aid normal row retrieval, the test may therefore portray the negative impact if appropriate indexes are not in place. Curious about the purpose of the exercise.
😎
August 20, 2014 at 12:39 pm
Hi SSCommitted,
I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.
Citing this as one of the advantages.
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 20, 2014 at 12:45 pm
Paresh Motiwala (8/20/2014)
Hi SSCommitted,I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.
Citing this as one of the advantages.
Paresh
Good stuff, there are great advantages in terms of DW's but one has to make certain that the application of the technology fits both the purpose and the target. A conventional model might and most certainly will not be optimal so that would be a place to start the restructure.
😎
August 21, 2014 at 3:17 am
Paresh Motiwala (8/20/2014)
Hi SSCommitted,I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.
Citing this as one of the advantages.
Paresh
Column Store Indexes do great things, with aggregate queries and other types of reporting queries. Simple SELECT statements and individual row queries, they don't help with. Nor were they designed for those. So, your tests are testing the one thing they're bad at. If that's your standard query pattern, then they won't help you. But, if your datawarehouse is primarily used for aggregate style queries, test the columnstore with those and you'll see a massive improvement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2014 at 5:53 am
Thanks Grant!!!
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
August 27, 2014 at 4:05 am
Hi,
In addition... when You do the "select *" DB still have to grab all the data from disk... If columnstore index would include all columns then in theory this operation could be faster (because of compression) ... but if Your query grab another columns not included in columnstore then DB probably is doing full scan...
August 27, 2014 at 5:24 am
Kulgan_ (8/27/2014)
Hi,In addition... when You do the "select *" DB still have to grab all the data from disk... If columnstore index would include all columns then in theory this operation could be faster (because of compression) ... but if Your query grab another columns not included in columnstore then DB probably is doing full scan...
Well, that depends. With 2014 and the introduction of the clustered columnstore, all the columns are stored with the columnstore index. There is no additional lookup to any other resource.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply