March 4, 2010 at 5:52 am
Hi folks,
I have a query that returns 540,000 rows in 46 seconds
It uses a base table and joins to 2 others
The execution plan shows my joins as both occuring via a 'Clustered Index Seek' which I'm happy with.
The base table is access for the primary key by an 'Index Seek' too.
These Index seeks cost 4% , 4% and 1% respectively.
The columns returned from the base table force a Key Lookup (Clustered) (am using sql 2008) to return the 10 columns from my base table. The cost for this is 90%
I've read that creating either an index with these columns, or an index with these columns as 'Included' are my options but I don't want to duplicate data into another index. It seems like a massive waste of disk space!
Any inspiration or guidance greatly appreciated.
r
March 4, 2010 at 6:34 am
r5d4 (3/4/2010)
The base table is access for the primary key by an 'Index Seek' too.These Index seeks cost 4% , 4% and 1% respectively.
The columns returned from the base table force a Key Lookup (Clustered) (am using sql 2008) to return the 10 columns from my base table. The cost for this is 90%
Widen whichever index that index seek was on and put the columns that are looked up into the INCLUDE columns of that index
I've read that creating either an index with these columns, or an index with these columns as 'Included' are my options but I don't want to duplicate data into another index. It seems like a massive waste of disk space!
Indexing is always a tradeoff. Disk space vs performance, modification speed vs data retrieval speed.
It's up to you where you draw the line. If you don't want to 'waste' the disk space, accept that you will have poorer performance then you may have had had you widened the index. If you want best performance, accept that you need to 'waste' space on indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply