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
SteveFriday, September 30, 2011 11:24 AM
Here is the table and column store definitions
The query is just a select all
steveSunday, 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, email@example.comSunday, October 02, 2011 6:52 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, firstname.lastname@example.orgSunday, October 02, 2011 10:01 PM