SQL DATEDIFF(year, ..., ...) 是一个昂贵的计算吗？

发布于 2024-08-27 07:29:13 字数 649 浏览 10 评论 0原文

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

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

所以我的问题是，如果 1,000 个 datediff 计算缩小到 100 个，我能期望看到很大的差异吗？

编辑：查询看起来像这样：

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

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

大约有 420 个这样的查询，其中这些 datediff 是这样计算的。我知道我可以轻松地将它们全部放入临时表中（1,000 个 datediff 变为 50） - 但这值得吗？它会在几秒钟内产生任何影响吗？我希望能有比几十秒更好的改进。

原文

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

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.

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

EDIT:
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

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黒涩兲箜 2024-09-03 07:29:13

老实说，这取决于您正在做什么，对性能的影响有多大。

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

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

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

并不能很好地利用 DateColumn 上的索引。而更好的解决方案是提供最佳索引使用：

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

这比将 DATEDIFF 作为结果集中的列返回的成本更高。

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

编辑：
根据您给出的示例查询，您可以尝试使用以下方法来删除 WHERE 子句中 DATEDIFF 的使用。查找给定日期 10 岁的每个人的基本示例 - 我认为数学是正确的，但无论如何你都明白了！快速测试了一下，看起来不错。应该足够容易适应您的场景。如果您想在给定日期查找年龄在（例如）15 岁到 17 岁之间的人，那么也可以使用此方法。

-- 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

需要补充的重要一点是，年龄是根据出生日期计算的，这种方法更准确。您当前的实现仅考虑出生年份，而不考虑实际日期（例如，2009 年 12 月 1 日出生的人在 2010 年 1 月 1 日将显示为 1 岁，而在 2010 年 12 月 1 日之前他们还不是 1 岁）。

希望这有帮助。

It depends on exactly what you are doing to be honest as to the extent of the performance hit.

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.

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

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

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.

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.

Edit:
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

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).

Hope this helps.

回复收藏 0 原文