| Author |
Topic |
|
henryos
Starting Member
6 Posts |
 Posted - 2009-11-29 : 09:30:11
|
| Hi anybody can explain me what is the difference between RID lookup and Key lookup in permonfance Terms? What is better? Why?A RID lookup do a table scan with the RID information?Thx for read me and i hope that anybody can help me. Hernán |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-29 : 13:00:55
|
| see thishttp://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ad81aabc-4f2c-4cd1-a2bf-c7ec690d7217 |
 |
|
|
henryos
Starting Member
6 Posts |
Posted - 2009-11-29 : 16:22:05
|
| thx visakh16but basicly i can't understand how much is the cost of a RID lookup... assuming no increase the size of the data in a row, the cost of the RID lookup is equal to table scan on the heap with the RID informacion?Thx Again and i hope your answer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-11-29 : 16:39:15
|
None of them are good. RID Lookup, Key Lookup and Bookmark Lookup are essentially all the same thing.It means Query Optimizer used an index to get as less IO as possible, and the column marked for Lookup wasn't part of the index.So Query Optimizer marked the records for a second round.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
henryos
Starting Member
6 Posts |
Posted - 2009-11-29 : 17:41:41
|
| Yeah Peso "None of them are good"... but i wanna know what is better: a RID lookup or a Key lookup?I had understood that RID and Key lookup were different...Thx again... and i hope your answer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-11-30 : 03:59:52
|
They are the same. The terminology has changed between the different versions of Microsoft SQL Server (2000, 2005 and 2008).
N 56°04'39.26"E 12°55'05.63" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-30 : 05:07:20
|
| A RID lookup occurs when the underlying table is a heap. A key lookup occurs when the underlying table has a clustered index. They're both single-row lookups to the underlying table. In SQL 2000, both fell under the term 'Bookmark lookup'--Gail ShawSQL Server MVP |
|
|
|
henryos
Starting Member
6 Posts |
Posted - 2009-11-30 : 06:31:32
|
| Yeah... i know when occurs each lookup ;)... but i read that Nonclustered index on Clustered Index(key lookup) is better that Non clustered Index on Heap(RID lookup)... there comes my question. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-30 : 07:36:54
|
| Why don't you test it out and see. There shouldn't be much of difference, they're both dog slow on larger numbers of rows.--Gail ShawSQL Server MVP |
|
|
|
henryos
Starting Member
6 Posts |
Posted - 2009-11-30 : 07:53:46
|
| yep... they are both slow, but that isn't my doubt, my doubt is: the seek Key lookup is cross the tree non-clustered, true? so the seek by RID lookup is table scan with the RowID or is other seek by tree or what is? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-30 : 08:52:05
|
| The key lookup is equivalent to a single row clustered index seek. The RID lookup is not a table scan, it's a fetch of a single row on a single page in the heap. At the point that the lookup is needed, the query engine has the RID (Row Identifier) of the row that it needs. That's a combination of file id, page id and slot index, which identifies exactly where the row that it wants is. So the RID lookup is the direct retrieval of say row 7 on page 52213 in file 1--Gail ShawSQL Server MVP |
|
|
|
henryos
Starting Member
6 Posts |
Posted - 2009-11-30 : 09:13:17
|
| Yeah! now i understand :)... Key lookup= single row clustered index seek and RID Lookup=access direct to information with RID.Thanx very muchhhhhh for all and special to GilaMonster for your help. |
|
|
|
|
RID and Key Lookup