locked
Project Apollo question : Columnar Index doesn't seem to speed up a query. Am I missing something?

    Question

  • Hi Folks

    I have a 10 million cell table  (1 million rows X 10 columns). The data should compress well down columns (being numeric and not too granular). 

    When I create a "Non-clustered Column Store Index" on the table,   the time to execute a select all query  doesn't decrease.

    Any ideas why why I am not getting any performance gains? I have followed the methodology outlined here : 

    Any advice or insight would be appreciated

    Steve 

    Friday, September 30, 2011 11:24 AM

All replies

  • It certainly could help if you posted the table definition, the definition of your columnstore index and the query. Else we could only guess wildly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, September 30, 2011 10:09 PM
  • Hi Erland 

    Here is the table and column store definitions

    http://imageshack.us/photo/my-images/132/tablestructure.jpg/

    http://imageshack.us/photo/my-images/828/columnstore.jpg/

     The query is just a  select all

    SELECT        dbo.ColumnartestN.*

    FROM            dbo.ColumnartestN

     

    Cheers

    steve 

     

     

     

     

     

    Sunday, October 02, 2011 6:35 PM
  • One possible reason is that the bottleneck is in the client where you receive all this data, rather than in the retrieval from the database.

    You may see a different effect if you do

    SELECT SUM(Data) FROM dbo.ColumnartestN

    You should see a considerable difference when you have an index and when you have not. You should also see a difference if you include an aggregate value for each column in your query, if not that drastic.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 02, 2011 6:52 PM
  • Thanks Erland 

    Can you  explain what you meant by "One possible reason is that the bottleneck is in the client where you receive all this data, rather than in the retrieval from the database."?

    Cheers 

    Steve 


     

    Sunday, October 02, 2011 7:20 PM
  • When you do SELECT * from your big table without any filter, you send a lot of data to the client. The client has to process that somehow. It is perfectly conceivable that it takes longer time for the client to consume the data, than it takes for SQL Server to produce it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 02, 2011 10:01 PM