Monday, March 12, 2012

Function Suppressing Index

I have the following query that needs to be rewritten (if possible) so that
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thanks
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
--
Message posted via http://www.sqlmonster.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:
> I have the following query that needs to be rewritten (if possible) so that
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column is
> the goal, but I am not seeing how that might be done in this instance. Thanks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.sqlmonster.com
>

No comments:

Post a Comment