July 20, 2004 at 8:35 am
Hi all. Just a quick one. I have a basic query against a 1M+ row table.
select count(*) from MYTABLE.
If I just have a clustered index against MYTABLE (across three columns) the query runs in 13 seconds... IO cost is 29.5
If I add an identical non-clustered unique index (across the same three columns) and run the SQL - it runs in less than 1 second and IO cost is 0.86.
Now this does seem conclusive but I would imagine the clustered to be quicker.... Is this not the case?
Any pointers or good articles on this would be appreciated as I believe some of our design can be improved...
Thanks,
Steve
July 20, 2004 at 1:33 pm
When you look at the execution plan take note of any book mark look ups and plan your indexes around them.. The optimal solution here would be a clustered index on a column with high cardinality and a covered non-clustered index which is to say.
Ex: Select distinct store, SUM(revenue) from accounting where region=A and accounting_id < 100
Clustered index on accouting_id asc.
Non clustered index on region, accounting_id, store, revenue in that order is covered.
Check your peformance on that.
============================
Richard S. Hale
Senior Database Marketing Developer
Business Intelligence
The Scooter Store
RHale@TheScooterStore.com
DSK: 830.627.4493
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply