Understanding Scans And Seeks

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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |