How to Get the Best Out of Seek
In the last example, if we replace “ProductID / 3 = 0” with “ProductID < 3” the
operation will be faster as it enables SQL Server to identify the end of the search.
SELECT/span> TransactionID
FROM Production.TransactionHistory
WHERE TransactionID >= 100000 AND TransactionID <= 100100
SELECT TransactionID
FROM Production.TransactionHistory
WHERE TransactionID IN (100000,100001,100002,100003,100004,100005,100006,100007,100008,100009,100010,100011,100012,100013,
100014,100015,100016,100017,100018,100019,100020,100021,100022,100023,100024,100025,100026,100027,
100028,100029,100030,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,
100042,100043,100044,100045,100046,100047,100048,100049,100050,100051,100052,100053,100054,100055,
100056,100057,100058,100059,100060,100061,100062,100063,100064,100065,100066,100067,100068,100069,
100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,
100084,100085,100086,100087,100088,100089,100090,100091,100092,100093,100094,100095,100096,100097,
100098,100099,100100)
When the second query is used, because there are many distinct values to be searched, SQL Server creates a constant list and executes the seek operation for each constant once. That generates 101 executions of the seek operation where the first query simply returns the rows in a single pass. Even though both are seek operations we may be able to reduce the total cost drastically.
(397 row(s) affected)
Table 'TransactionHistory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0
(1 row(s) affected)
(397 row(s) affected)
Table 'TransactionHistory'. Scan count 99, logical reads 228, physical reads 0, read-ahead reads 0
(1 row(s) affected)
Conclusion
· The Scan operation reads either from beginning or from the end of the object, whereas Seek starts at a particular location in the object.
· Both Seek and Scan continue their operation until either the conditions are met or the entire object is scanned.
· Even though Seek is considered as a better option than scan, it should be analyzed based on the query and the data involved.
]]>