September 28, 2012 at 9:44 am
Hi,
I have a table with approximately 40 columns and a query that returns about 20 of them.
There's an index on the search column so an Index Seek is made but to get the other information it makes a Key Lookup.
A way of not having the key lookup is to "include" the output fields on the index with the search columns.
If I make this for every possible combination of search columns and output columns I'll get a huge number of indexes, since the output columns are dynamic (according to the UI displayed fields).
Is Key Lookup realty that bad?!
The "general" way I see of avoiding the Key Lookup is by including on every indexes the other table fields...
Thanks,
Pedro
September 28, 2012 at 10:02 am
Including 20 columns out of 40 might be a bit of an overhead.
If you create a covering index, the order of the included columns doesn't matter as long as the queries use the same key part.
You can't set the clustered index to cover this can you?
Is it one search column, or are there many search columns? If all searches use Date, for example, you could make Date the first column in the key & force all queries to start searches with Date - from 01/01/1900 - 01/01/2100 if there's no real date selection...
September 28, 2012 at 10:09 am
How many rows does the query return? How many in the table?
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
September 28, 2012 at 10:22 am
This is used in a grid...
The columns are configured, so any column can be displayed there, and any column can be filtered on...
Probably the solution would be creating a clustered index with all the columns and have the PK not clustered... but is this a good idea?! If I make an UPDATE on any column the row would be deleted and inserted again since the index has all the columns...
I made a simple test with an index with all 4 columns from a test table:
CREATE TABLE tblIndexTest2 (Id INT NOT NULL, Name VARCHAR(40), Age INT, Address NVARCHAR(100), CONSTRAINT PK_tblIndexTest2 PRIMARY KEY (Id))
INSERT INTO tblIndexTest2 VALUES (1, 'Pedro', 30, 'Address'), (2, 'Pedro', 30, 'Address'), (3, 'Pedro', 30, 'Address')
CHECKPOINT
UPDATE tblIndexTest2 SET Age = Age +1 WHERE Id = 2
SELECT [Current LSN], [Operation], AllocUnitName, t.Description, t.[Transaction Name], t.[Begin Time], t.[End Time] FROM fn_dblog (NULL, NULL) t;
--TABLE WITH FULL CLUSTERED INDEX
CREATE TABLE tblIndexTest (Id INT NOT NULL, Name VARCHAR(40), Age INT, Address NVARCHAR(100), CONSTRAINT PK_tblIndexTest PRIMARY KEY NONCLUSTERED (Id))
CREATE CLUSTERED INDEX IDX_tblIndexTest ON tblIndexTest (Id, Name, Age, Address)
INSERT INTO tblIndexTest VALUES (1, 'Pedro', 30, 'Address'), (2, 'Pedro', 30, 'Address'), (3, 'Pedro', 30, 'Address')
CHECKPOINT
UPDATE tblIndexTest SET Age = Age +1 WHERE Id = 2
SELECT [Current LSN], [Operation], AllocUnitName, t.Description, t.[Transaction Name], t.[Begin Time], t.[End Time] FROM fn_dblog (NULL, NULL) t;
The first query output is:
00001938:000012d0:0007LOP_BEGIN_CKPTNULL
00001938:000012d3:0001LOP_END_CKPTNULL
00001938:000012d4:0001LOP_BEGIN_XACTNULL
00001938:000012d4:0002LOP_MODIFY_ROWdbo.tblIndexTest2.PK_tblIndexTest2
00001938:000012d4:0003LOP_COMMIT_XACTNULL
the second one is:
00001938:000012db:000bLOP_BEGIN_CKPTNULL
00001938:000012df:0001LOP_END_CKPTNULL
00001938:000012e0:0001LOP_BEGIN_XACTNULL
00001938:000012e0:0002LOP_DELETE_ROWSdbo.tblIndexTest.IDX_tblIndexTest
00001938:000012e0:0003LOP_MODIFY_HEADERUnknown Alloc Unit
00001938:000012e0:0004LOP_SET_BITSdbo.tblIndexTest.IDX_tblIndexTest
00001938:000012e0:0005LOP_INSERT_ROWSdbo.tblIndexTest.IDX_tblIndexTest
00001938:000012e0:0006LOP_DELETE_ROWSdbo.tblIndexTest.PK_tblIndexTest
00001938:000012e0:0007LOP_INSERT_ROWSdbo.tblIndexTest.PK_tblIndexTest
00001938:000012e0:0008LOP_COMMIT_XACTNULL
There's a lot more "work" being done...
Regards,
Pedro
September 28, 2012 at 10:25 am
GilaMonster (9/28/2012)
How many rows does the query return? How many in the table?
It can have as much as 100.000 rows and returns not more than 10, we apply paging before getting the output fields (have a CTE with ROWNUMBER() OVER (..) that returns only the row PK and the join the CTE with the table to get the output fields.. that's when it makes the key lookup..
September 28, 2012 at 10:33 am
It it's only returning 10 rows I wouldn't worry about the key lookups to be honest.
Key lookups are nasty, but on 10 rows it's not a huge overhead.
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
September 28, 2012 at 10:40 am
GilaMonster (9/28/2012)
It it's only returning 10 rows I wouldn't worry about the key lookups to be honest.Key lookups are nasty, but on 10 rows it's not a huge overhead.
Thanks...
I read you blog about wide indexes and key / rid lookups..
If the number of rows returned is 20% on a table with over 1000 rows, then lookups are bad, right?
Thanks,
Pedro
September 28, 2012 at 11:16 am
If more than about 1% of the rows will be returned, SQL won't do key lookups, it'll revert to a scan.
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
September 28, 2012 at 11:49 am
Thanks for the explanation...
Keylookups aren't that bad if a small number of records is returned.
Thanks,
Pedro
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply