|
|
Posted by Erland Sommarskog on 11/22/23 11:36
manstein (jkelly.admin@gmail.com) writes:
> page 488 - 489 The Guru's Guide to SQL Server Architecture and
> Internals. Ken Henderson gives this code as an example of folding:
>
> SELECT *
> FROM Orders
> WHERE ISNULL(Orderdate,getdate())>'2003-04-06 19:55:00.000'
>
> Now, if you check the execution plan, you will see that there is an
> index scan on the Orderdate column. Apparently Henderson considers
> this an efficient use of an index; hence a good example of optimization
> through folding. Similarly, my code also uses a index scan which is
> far better than a table scan. Though you do make the distinction above
> between index scan and index seek, based on the examination of the
> execution plans, your suggestion that my code is in-efficient is
> unwarrented. Thanks!
As Alexander points out, the difference between an Index Seek and an
Index Scan can be considerable. A table scan may be 1000 pages reads,
an index scan 100 page read, and index seek can be 3 page access.
And if the index in question is the clustered index, the index scan is a
table scan.
Note also that in case of a non-clustered index, there is also the risk
that the optimizer estimates that the number of bookmark lookups will
be so many, that it may go for a clustered index scan anyway.
This is not to say that index scans are always bad. But for the query
at hand, it's possible to write a query that can utilize an index seek,
why it's risky to wrap the column in an expression.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|