Mark Wilson
asked on
Alternatives to Datediff
Datediff, seems to slow the queries I write down i.e.
Datediff(s,field1,field2)
Is there another more efficient way to calculate date and time differences in a query
Datediff(s,field1,field2)
Is there another more efficient way to calculate date and time differences in a query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Is there another more efficient way to calculate date and time differences in a query<<
Yes. Try avoiding using a function on the right hand side of WHERE clause.
Yes. Try avoiding using a function on the right hand side of WHERE clause.
>>Try avoiding using a function on the right hand side of WHERE clause.<<
And I obviously do not know my right from my left. It should read:
"Try avoiding using a function on the LEFT hand side of WHERE clause."
And I obviously do not know my right from my left. It should read:
"Try avoiding using a function on the LEFT hand side of WHERE clause."
acperkins wrote:
>"Try avoiding using a function on the LEFT hand side of WHERE clause."
So, are saying that:
WHERE 30 < Datediff(s,field1,field2)
Is more performance than:
WHERE Datediff(s,field1,field2) > 30
It seems like the optimizer would be able to figure out something that simple?!?!?
David
>"Try avoiding using a function on the LEFT hand side of WHERE clause."
So, are saying that:
WHERE 30 < Datediff(s,field1,field2)
Is more performance than:
WHERE Datediff(s,field1,field2) > 30
It seems like the optimizer would be able to figure out something that simple?!?!?
David
yeah but
where field2 > dateadd(s,30,field1) is probably simpler...
and why is that an easy transform?
where field2 > dateadd(s,30,field1) is probably simpler...
and why is that an easy transform?
>>It seems like the optimizer would be able to figure out something that simple?!?!?<<
It will figure it out just fine, you don't have to worry about that. It will just result in a table scan and any index will be totally ignored.
It will figure it out just fine, you don't have to worry about that. It will just result in a table scan and any index will be totally ignored.
In any case, neither of the two options are much help. Lowfatspread has given you the right solution.
If you are just worried about things that happened more than thirty seconds apart, a fast solution might be to pre-calculate that 30 seconds as a DATETIME, thus preventing DATEADDs happening all the time:
-- at the top of the script
DECLARE @Secs30 DATETIME, @TD1 DATETIME, @TD2 DATETIME
SET @TD1='01-01-2001 00:00:00'
SET @TD2='01-01-2001 00:00:30'
SET @Secs30=(@TD2 - @TD1)
-- in the comparison
WHERE (field2 - field1) < @Secs30
Now all you're asking the WHERE clause to do is a simple numeric subtraction and compare, which has GOT to be quicker that DATEADD and DATEDIFF, which need to do all sorts of clever inter-type conversion things.
-- at the top of the script
DECLARE @Secs30 DATETIME, @TD1 DATETIME, @TD2 DATETIME
SET @TD1='01-01-2001 00:00:00'
SET @TD2='01-01-2001 00:00:30'
SET @Secs30=(@TD2 - @TD1)
-- in the comparison
WHERE (field2 - field1) < @Secs30
Now all you're asking the WHERE clause to do is a simple numeric subtraction and compare, which has GOT to be quicker that DATEADD and DATEDIFF, which need to do all sorts of clever inter-type conversion things.
>>which has GOT to be quicker that DATEADD and DATEDIFF, which need to do all sorts of clever inter-type conversion things.<<
Actually, in general no. If the columns are indexed then there is no way that it can be used and it will have to resort to a table scan. But don't take my word for it, take a look at the Plan Execution and see for yourself.
Actually, in general no. If the columns are indexed then there is no way that it can be used and it will have to resort to a table scan. But don't take my word for it, take a look at the Plan Execution and see for yourself.
SELECT Date1, Date2, (CAST(Date1 AS DECIMAL) - CAST(Date2 AS Decimal)) AS DaysDifference ...