SQL DATEDIFF（year，...，...）是昂贵的计算吗？ [英] Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?

问题描述

我正在尝试优化一些非常复杂的SQL查询，因为它需要很长时间才能完成。

I'm trying to optimize up some horrendously complicated SQL queries because it takes too long to finish.

在我的查询中，我动态创建了很多SQL语句相同的功能，因此我创建了一个临时表，其中每个功能仅被调用一次，而不是多次调用-这将我的执行时间缩短了3/4。

In my queries, I have dynamically created SQL statements with lots of the same functions, so I created a temporary table where each function is only called once instead of many, many times - this cut my execution time by 3/4.

所以我的问题是，如果说将1000个datediff计算范围缩小到100个，我能期望看到很大的不同吗？

So my question is, can I expect to see much of a difference if say, 1,000 datediff computations are narrowed to 100?

编辑：
查询看起来像

The query looks like this :

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 

其中，这些是作为字符串动态生成的（一点一点地拼在一起），然后执行，以便每次迭代都可以更改参数-主要是最后一个li nes，其中包含各种DATEDIFF查询。

where these are being generated dynamically as strings (put together in bits and pieces) and then executed so that various parameters can be changed along each iteration - mainly the last lines, containing all sorts of DATEDIFF queries.

大约有420个这样的查询，其中这些datediff的计算方式如下。我知道我可以轻松地将它们全部拉入临时表（1,000 datediffs变为50）-但这值得吗，在几秒钟内有什么区别吗？我希望有比十分之一秒更好的改进。

There are about 420 queries like this where these datediffs are being calculated like so. I know that I can pull them all into a temp table easily (1,000 datediffs becomes 50) - but is it worth it, will it make any difference in seconds? I'm hoping for an improvement better than in the tenths of seconds.

推荐答案

这取决于您要做什么

例如，如果您在WHERE子句中使用DATEDIFF（或者实际上是任何其他函数），那么这将导致性能降低因为这样会阻止在该列上使用索引。

For example, if you are using DATEDIFF (or indeed any other function) within a WHERE clause, then this will be a cause of poorer performance as it will prevent an index being used on that column.

例如基本示例，查找2009年的所有记录

e.g. basic example, finding all records in 2009

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

不会充分利用DateColumn上的索引。更好的解决方案是，提供最佳的索引用法是：

would not make good use of an index on DateColumn. Whereas a better solution, providing optimal index usage would be:

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

我最近通过博客进行了介绍（如果有兴趣的话）（与性能统计/执行计划比较）。

I recently blogged about the difference this makes (with performance stats/execution plan comparisons), if you're interested.

这比将DATEDIFF作为结果集中的列返回要花费更多。

That would be costlier than say returning DATEDIFF as a column in the resultset.

我首先要确定花费时间最多的单个查询。检查执行计划以查看问题所在，并从那里进行调整。

I would start by identifying the individual queries that are taking the most time. Check the execution plans to see where the problem lies and tune from there.

编辑：
根据您给出的示例查询，这里是您可以尝试在WHERE子句中删除对DATEDIFF的使用的方法。查找在给定日期有10岁的每个人的基本示例-我认为数学是正确的，但是无论如何您都会明白这一点！快速测试，看起来还不错。应该很容易适应您的情况。如果您想在给定的日期找到15至17岁之间的人，那么使用这种方法也是可行的。

Based on the example query you've given, here's an approach you could try out to remove the use of DATEDIFF within the WHERE clause. Basic example to find everyone who was 10 years old on a given date - I think the maths is right, but you get the idea anyway! Gave it a quick test, and seems fine. Should be easy enough to adapt to your scenario. If you want to find people between (e.g.) 15 and 17 years old on a given date, then that's also possible with this approach.

-- Assuming @Date2 is set to the date at which you want to calculate someone's 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

重要的补充是，对于DOB的年龄计算，这种方法是更准确。您当前的实施方式仅考虑出生年份，而不考虑实际日期（例如，2009年12月1日出生的人在2010年1月1日之前显示为1岁，而直到2010年12月1日才是1岁）。

An important note to add, is for age caculates from DOB, this approach is more accurate. Your current implementation only takes the year of birth into account, not the actual day (e.g. someone born on 1st Dec 2009 would show as being 1 year old on 1st Jan 2010 when they are not 1 until 1st Dec 2010).

希望这会有所帮助。

这篇关于SQL DATEDIFF（year，...，...）是昂贵的计算吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！