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 

Answer 1

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, [e-mail]

Answer 2

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 

 

 

 

 

 

Answer 3

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, [e-mail]

Answer 4

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 

 

Answer 5

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, [e-mail]