columnstore index issue

  • 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

  • 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.

    😎

  • I am just running a bench marking test. With and without columnstore indexes....

    Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

  • 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.

    😎

  • 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

  • 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.

    😎

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks Grant!!!

    Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

  • 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...

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply