SQL Server Performance Forum – Threads Archive
function of index column
One of my customers has a query runs frequently, something like: where cast(getdate() + @inputtime – timestamp as dec(10,2)) > @thresholdvaluefrom myTable where tmpestamp is an indexed column in table myTable.
How likely SQL will run this by using (1) index search on the index on timestamp
(2) index SCAN on the index on timestamp
(3) table scan by discarding the index timestamp ? I don’t have access to their data so I couldn’t run it on the exec plan.
Thanks. -mingus
You can ask them to run SET SHOWPLAN_ALL in query analyzer and get the output to assess.
In this case I think it if the ORDERED clause is not present, the storage engine will scan the index
in the optimal way (not guaranteeing the output to be sorted). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I will do that. But in the meantime, did you mean it could
be an index scan, not the best solution of (1), and not
the worst solution of (3)? I read some articles on functions of indexed columns. A lot
of them result in index scan, instead of the index-seek/search
where the index is purposely built for.
I think this will result in an Index scan or table scan depending on how SQL Server decides. If the indx scan will give all the data for the query and there will not be nay bookmark lookups, index scan will be used. Else Table Scan. Try rewriting the logic of the query so that the functions cast etc. are applied on the varibale and not the column in the table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
I agree, and actually that’s my concern(
esp if it results in a table scan), they
should do the func on the right hand side (input value)
instead of the left hand side (the select value).
Thanks.
quote:Originally posted by gaurav_bindlish
I think this will result in an Index scan or table scan depending on how SQL Server decides. If the indx scan will give all the data for the query and there will not be nay bookmark lookups, index scan will be used. Else Table Scan.
Try rewriting the logic of the query so that the functions cast etc. are applied on the varibale and not the column in the table.
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
An index seek will be performed if the query is selective. Index seek will result in a performance boost if, say, 95% of the rows can be excluded by your where clause. The best point would be to check execution plan for the query involved. For information review this articlehttp://www.winnetmag.com/SQLServer/Article/ArticleID/7632/7632.html HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>