Is SQL DATEDIFF (year, ..., ...) an expensive calculation?

Question

I am trying to optimize some terribly complex SQL queries because it takes too much time to complete.

In my queries, I dynamically created SQL statements with many identical functions, so I created a temporary table where each function is called only once, and not many, many times - this reduced my execution time by 3/4.

So my question is: can I expect most of the difference if, say, 1000 dated calculations narrow to 100?

EDIT: The query is as follows:

SELECT DISTINCT M.MID, M.RE FROM #TEMP INNER JOIN M ON #TEMP.MID=M.MID WHERE ( #TEMP.Property1=1 ) AND DATEDIFF( year, M.DOB, @date2 ) >= 15 AND DATEDIFF( year, M.DOB, @date2 ) <= 17 

where they are dynamically generated as strings (combined into bits and pieces), and then executed so that various parameters can be changed along each iteration - mainly the last lines containing all kinds of DATEDIFF queries.

There are about 420 queries in which these datifixes are computed as follows. I know that I can easily put them all together in a temp table (1000 datifixes - 50) - but is it worth it, will there be a difference in seconds? I hope to improve better than in tenths of a second.

Answer 1

It depends on what you do to be honest about the degree of performance hit.

For example, when using DATEDIFF (or even any other function) in the WHERE clause, this will cause lower performance, as this will prevent the index used on this column.

eg. basic example, search for all records in 2009

 WHERE DATEDIFF(yyyy, DateColumn, '2009-01-01') = 0 

will not use the DateColumn index. While the best solution to ensure optimal use of the index would be:

 WHERE DateColumn >= '2009-01-01' AND DateColumn < '2010-01-01' 

I recently blogged about the difference this makes (with a comparison of performance / statistics performance plan) if you are interested.

That would be more expensive than saying returning DATEDIFF as a column in the result set.

I would start by identifying the individual queries that take up the most time. Check your execution plans to see where the problem is, and tune in from there.

Edit: Based on the example query you specified, here you can try to remove the use of DATEDIFF in the WHERE clause. The main example is to find everyone who was 10 years old on a given date - I think the math is right, but you still get the idea! Gave him a quick test, and it seems wonderful. It should be easy enough to adapt to your scenario. If you want to find people between (for example) 15 and 17 years old on a certain date, then this is also possible with this approach.

 -- Assuming @Date2 is set to the date at which you want to calculate someone age DECLARE @AgeAtDate INTEGER SET @AgeAtDate = 10 DECLARE @BornFrom DATETIME DECLARE @BornUntil DATETIME SELECT @BornFrom = DATEADD(yyyy, -(@AgeAtDate + 1), @Date2) SELECT @BornUntil = DATEADD(yyyy, -@AgeAtDate , @Date2) SELECT DOB FROM YourTable WHERE DOB > @BornFrom AND DOB <= @BornUntil 

An important note to add is that, with the age of caculate from DOB, this approach is more accurate. Your current implementation takes the year of birth into account, not the actual day (for example, someone born December 1, 2009 will show how 1 year is on January 1, 2010, when they are not until December 1, 2010).

Hope this helps.

Answer 2

DATEDIFF is quite efficient compared to other methods of processing date and time values, such as strings. ( see this answer "SO" ).

In this case, it sounds like you are sorting through the same data, which is probably more expensive than using the temp table. For example, statistics will be generated.

Answer 3

One thing you could do to improve performance is to set the index in the temp table to MID.

Check the execution plan to see if it helps (it may depend on the number of rows in the temp table).