[ACCEPTED]-Which is better: Bookmark/Key Lookup or Index Scan-optimization

Accepted answer
Score: 53

Index seek, every time.

Lookups are expensive, so 12 this is covering indexes and especially 11 the INCLUDE clause was added to make them 10 better.

Saying that if you expect exactly 9 one row, for example, a seek followed a 8 lookup can be better than trying to cover 7 a query. We rely on this to avoid yet another 6 index in certain situations.

Edit: Simple 5 talk article: Using Covering Indexes to Improve Query Performance

Edit, Aug 2012

Lookups happen 4 per row which is why they scale badly. Eventually, the 3 optimiser will choose a clustered index 2 scan instead of a seek+lookup because it's 1 more efficient than many lookups.

Score: 10

Key lookup is very similar to a clustered index seek (pre 17 2005 SP2 was named 'seek with lookup'). I 16 think the only difference is that the Key 15 Lookup may specify an additional PRE-FETCH 14 argument instructing the execution engine 13 to prefetch more keys in the cluster (ie. do 12 a clustered index seek followed by scan).

Seeing 11 a Key Lookup should not scare you. Is the 10 normal operator used in Nested Loops, and 9 Nested Loops is the run-of-the-mill join 8 operator. If you want to improve a plan, try 7 improving on the join and see if it can 6 use a merge join instead (ie. both sides 5 of join can provide rows on the same key 4 order, fastest join) or a hash-join (have 3 enough memory for the QO to consider a hash 2 join, or reduce the cardinality by filtering 1 rows before the join rather than after).

Score: 1

This SO question mentions that key lookups are something 2 to avoid. An index seek is going to definitely 1 be the better performing operation.

More Related questions