Clustered Index Scan or Index Seek + Key Lookup

Question

Clustered Index Scan or Index Seek + Key Lookup

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 28, 2016 at 1:31 am

#320930

Experts,
Which is better ? A Clustered Index scan or Index Seek + Key lookup on clustered index.
Seek Keys[1]: Prefix: [PROD].[dbo].[Alankar].OId = Scalar Operator([PROD].[dbo].[Alankar].[OId] as [DO].[OId])

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Depends on the table size and number of rows needing a lookup. Test for your data set and see which works better for you.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

GilaMonster (8/28/2016)
Depends on the table size and number of rows needing a lookup. Test for your data set and see which works better for you.

Thanks Gail.

For large data scan is better and is its less then Seek + Key lookup is fine right? Please correct me if I am wrong.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It depends both on the total size of the table, and on the number of rows needing a lookup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

VastSQL (8/28/2016)
GilaMonster (8/28/2016)
Depends on the table size and number of rows needing a lookup. Test for your data set and see which works better for you.
Thanks Gail.
For large data scan is better and is its less then Seek + Key lookup is fine right? Please correct me if I am wrong.

As Gail said, it depends. The only way to know is to test it insitu.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

Define "better".

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho