TSQL使用DATEADD在where子句中的性能问题

问题描述：

我有一个使用DATEADD方法的查询需要很多时间。我会尽量简化我们的工作。我们正在监视tempretures并每5分钟我们存储的最高温度和最低温度在表一TSQL使用DATEADD在where子句中的性能问题

日期 | 时间 | MaxTemp | MinTemp
2011-09-18 | 12:05:00 | 38.15 | 38.099
2011-09-18 | 12:10:00 | 38.20 | 38.10
2011-09-18 | 12:15:00 | 38.22 | 38.17
2011-09-18 | 12:20:00 | 38.21 | 38.20
...
2011-09-19 | 11:50:00 | 38.17 | 38.10
2011-09-19 | 12:55:00 | 38.32 | 38.27
2011-09-19 | 12:00:00 | 38.30 | 38.20

日期/时间列的类型日期/时间（而不是日期时间）

在另一表（表B）我们存储一整天，其中每天从中午一些数据（12PM ）到中午（不是午夜到午夜）。

所以表B柱包括：日期（日期只是没有时间）
ShiftManager
MaxTemp（这是整个24小时内最高温度开始在该日中午，直到第二天中午）
MinTemp

我得到表B的所有数据，只需要更新MaxTemp和MinTemp使用表A
例如：对于2011年9月18日我需要的是2011年9月18日12PM和09/19/2011 12PM。
在上面的TableA示例中，returend结果将是38.32，因为它是所需期间的MAX（MaxTemp）。

我使用的SQL：

update TableB 
set MaxTemp = (
select MAX(HighTemp) from TableA 
where 
(Date=TableB.Date and Time > '12:00:00') 
or 
(Date=DATEADD(dd,1,TableB.Date) and Time <= '12:00:00') 
)

而且需要花费大量的时间（如果我删除DATEADD方法是快速）。

下面是一个简单示例，演示我拥有的数据和预期的结果：

DECLARE @TableA TABLE ([Date] DATE, [Time] TIME(0), HighTemp DECIMAL(6,2)); 
DECLARE @TableB TABLE ([Date] DATE, MaxTemp DECIMAL(6,2)); 

INSERT @TableA VALUES 
('2011-09-18','12:05:00',38.15), 
('2011-09-18','12:10:00',38.20), 
('2011-09-18','12:15:00',38.22), 
('2011-09-19','11:50:00',38.17), 
('2011-09-19','11:55:00',38.32), 
('2011-09-19','12:00:00',38.31), 
('2011-09-19','12:05:00',38.33), 
('2011-09-19','12:10:00',38.40), 
('2011-09-19','12:15:00',38.12), 
('2011-09-20','11:50:00',38.27), 
('2011-09-20','11:55:00',38.42), 
('2011-09-20','12:00:00',38.16); 

INSERT @TableB VALUES 
('2011-09-18', 0), 
('2011-09-19', 0); 

-- This is how I get the data, now I just need to update the max temp for each day 

with TableB(d, maxt) as 
(
select * from @TableB 
) 
update TableB 
set maxt = ( 
select MAX(HighTemp) from @TableA 
where 
(Date=TableB.d and Time > '12:00:00') 
or 
(Date=DATEADD(dd,1,TableB.d) and Time <= '12:00:00') 
) 

select * from @TableB

希望我能够explian自己，任何想法我怎么能做到这一点不同？谢谢！

花括号将格式化您的代码。提示：4个主导空间... – gbn

TableB.Date是否在午夜默认时间内进入？ – Paparazzi

答

此插入可能会是一个容易得多，如果你使用的不是这个数据分成DATE/TIME列单SMALLDATETIME列。另外，我假设您使用的是SQL Server 2008，而不是以前的版本，您将DATE/TIME数据存储为字符串。请指定SQL Server的版本和正在使用的实际数据类型。

DECLARE @d TABLE ([Date] DATE, [Time] TIME(0), MaxTemp DECIMAL(6,3), MinTemp DECIMAL(6,3)); 

INSERT @d VALUES 
('2011-09-18','12:05:00',38.15,38.099), 
('2011-09-18','12:10:00',38.20,38.10), 
('2011-09-18','12:15:00',38.22,38.17), 
('2011-09-18','12:20:00',38.21,38.20), 
('2011-09-19','11:50:00',38.17,38.10), 
('2011-09-19','12:55:00',38.32,38.27), 
('2011-09-19','12:00:00',38.30,38.20); 

SELECT '-- before update'; 
SELECT * FROM @d; 

;WITH d(d,t,dtr,maxt) AS 
(
    SELECT [Date], [Time], DATEADD(HOUR, -12, CONVERT(SMALLDATETIME, CONVERT(CHAR(8), 
     [Date], 112) + ' ' + CONVERT(CHAR(8), [Time], 108))), MaxTemp FROM @d 
), 
d2(dtr, maxt) AS 
(
    SELECT CONVERT([Date], dtr), MAX(maxt) FROM d 
    GROUP BY CONVERT([Date], dtr) 
) 
UPDATE d SET maxt = d2.maxt FROM d 
    INNER JOIN d2 ON d.dtr >= d2.dtr AND d.dtr < DATEADD(DAY, 1, d2.dtr); 

SELECT '-- after update'; 
SELECT * FROM @d;

结果：

-- before update 

2011-09-18 12:05:00 38.150 38.099 
2011-09-18 12:10:00 38.200 38.100 
2011-09-18 12:15:00 38.220 38.170 
2011-09-18 12:20:00 38.210 38.200 
2011-09-19 11:50:00 38.170 38.100 
2011-09-19 12:55:00 38.320 38.270 
2011-09-19 12:00:00 38.300 38.200 

-- after update 

2011-09-18 12:05:00 38.220 38.099 
2011-09-18 12:10:00 38.220 38.100 
2011-09-18 12:15:00 38.220 38.170 
2011-09-18 12:20:00 38.220 38.200 
2011-09-19 11:50:00 38.220 38.100 
2011-09-19 12:55:00 38.320 38.270 
2011-09-19 12:00:00 38.320 38.200

大概要更新MinTemp为好，这也只是：

;WITH d(d,t,dtr,maxt,mint) AS 
(
    SELECT [Date], [Time], DATEADD(HOUR, -12, 
     CONVERT(SMALLDATETIME, CONVERT(CHAR(8), [Date], 112) 
     + ' ' + CONVERT(CHAR(8), [Time], 108))), MaxTemp, MaxTemp 
    FROM @d 
), 
d2(dtr, maxt, mint) AS 
(
    SELECT CONVERT([Date], dtr), MAX(maxt), MIN(mint) FROM d 
    GROUP BY CONVERT([Date], dtr) 
) 
UPDATE d 
    SET maxt = d2.maxt, mint = d2.maxt 
    FROM d 
    INNER JOIN d2 
     ON d.dtr >= d2.dtr 
     AND d.dtr < DATEADD(DAY, 1, d2.dtr);

现在，这是不是真的比你现有的查询更好，因为它仍然会使用扫描来计算聚合和所有需要更新的行。我不是说你应该根本不需要更新表格，因为这些信息总是可以在查询时得到，但如果这是你真正想要做的事情，我会结合这些答案中的建议并考虑修改架构。例如，如果模式为：

USE [tempdb]; 
GO 

CREATE TABLE dbo.d 
(
    [Date] SMALLDATETIME, 
    MaxTemp DECIMAL(6,3), 
    MinTemp DECIMAL(6,3), 
    RoundedDate AS (CONVERT(DATE, DATEADD(HOUR, -12, [Date]))) PERSISTED 
); 

CREATE INDEX rd ON dbo.d(RoundedDate); 

INSERT dbo.d([Date],MaxTemp,MinTemp) VALUES 
('2011-09-18 12:05:00',38.15,38.099), 
('2011-09-18 12:10:00',38.20,38.10), 
('2011-09-18 12:15:00',38.22,38.17), 
('2011-09-18 12:20:00',38.21,38.20), 
('2011-09-19 11:50:00',38.17,38.10), 
('2011-09-19 12:55:00',38.32,38.27), 
('2011-09-19 12:00:00',38.30,38.20);

然后你更新这个简单，并且该计划是好得多：

;WITH g(RoundedDate,MaxTemp) 
AS 
(
    SELECT RoundedDate, MAX(MaxTemp) 
     FROM dbo.d 
     GROUP BY RoundedDate 
) 
UPDATE d 
    SET MaxTemp = g.MaxTemp 
    FROM dbo.d AS d 
    INNER JOIN g 
    ON d.RoundedDate = g.RoundedDate;

最后的原因之一现有查询很可能要花这么长时间是你每次都在更新所有的时间。上周的数据是否发生变化？可能不会。那么为什么不把WHERE条款限制在最近的数据呢？除非你不断地接受上周二中午有多么温暖的修正估计，否则我认为没有必要比昨天更早地重新计算任何事情。那么，为什么当前查询中没有WHERE子句，为了限制它尝试执行此项工作的日期范围？你真的想更新全能吗，每次？这可能是你应该每天一次，下午某个时间进行一次，以便在昨天进行更新。所以无论是需要2秒还是2.5秒都不是问题。

我看不到你在做什么来获得该错误。你能在某处显示代码吗？我上面列出的代码已经过测试，并且我没有看到我在DATE使用HOUR的任何地方（除非您更改了它）。 –

对不起，这是我的坏，我没有注意到这是smalldatetime的类型。我喜欢RoundDate的想法，它的工作原理非常好，并有助于其他查询。我确实需要一个单独的日期和时间字段，但我设法创建计算字段，如下所示： [RoundedDay] AS（CONVERT（[date]，dateadd（hour，-12，CONVERT（[datetime]，[Date]， 0）+ CONVERT（[datetime]，[Time]，0）），0））PERSISTED 您怎么看？ – YWsecond

您可能想使用'DATEADD（MINUTE，-765，'，根据我尝试根据您的更新进行的一些更进一步的查询。另外，您的公式似乎不正确...您尝试过吗？看起来像RoundedDay对于中午行是错误的一天，如果你使用聚集的startdatetime列开始，而不是单独的日期/时间列，它可能会更有效一些。这些数据类型有它们的位置，但它似乎不是 –

答

列上的函数通常会导致性能下降。所以可以或。

但是，我假设你想要AND而不是OR，因为它是一个范围。

所以，应用一些逻辑和仅具有一个计算

update TableB 
set MaxTemp = 
(
    select MAX(HighTemp) from TableA 
    where 
    (Date + Time - 0.5 = TableB.Date) 
)

(Date + Time - 0.5)将改变中午到中午是午夜到午夜（0.5 = 12小时）。更重要的是，你可以使这个computed column and index it

更正确，Date + Time - 0.5是DATEADD(hour, -12, Date+Time)假设Date和Time是真正的日期/时间，而不是为varchar ...

编辑：这个答案是错的，但我会离开它为“不要做什么”

为见http://sqlblog.com/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx?CommentPosted=true#commentmessage更多

请小心'DATEADD'的简写。如果任何一列是新的日期/时间数据类型（'DATE' /'TIME' /'DATETIME2' /'DATETIMIEOFFSET'），它将会失败并显示'Msg 8117，Level 16，State 1，Line 1 - Operand data type日期对于添加操作员无效。“＃ –

@Aaron Bertrand：好点。我们只有一个日期时间专栏当然... – gbn

是的，这是真的。只是试图踢人的习惯，因为如果/当他们迁移到新数据类型时代码会中断，或者有人出现并且已经在使用新类型，他们可能不会理解错误。如果你拼出'DATEADD'，它可以在任何地方使用。 –

答

您可能需要使用-12根据日期为起始日期或结束日期的中午到中午我nternal。

update tableA 
    set tableAx.MaxTemp = MAX(TableB.HighTemp) 
    from tableA as tableAx 
    join TableB 
    on tableAx.Date = CAST(DATEADD(hh,12,TableB.[Date]+TableB.[Time]) as Date) 
    group by tableAx.Date

因为12个小时的偏差不知道有多少会会直接把表B日期加时间日期时间字段获得的。即使进入函数的参数被编入索引，也无法脱离DATEADD，并且函数的输出未被编入索引。您可能能够创建的计算列=日期+时间+/- 12小时并为该列编制索引。

就像Arron的建议，只更新那些没有值。

update tableA 
    set tableAx.MaxTemp = MAX(TableB.HighTemp) 
    from tableA as tableAx 
    join TableB 
    on tableAx.Date = CAST(DATEADD(hh,12,TableB.[Date]+TableB.[Time]) as Date) 
    where tableAx.MaxTemp is null 
    group by tableAx.Date

或新的日期

insert into tableA (date, MaxTemp) 
    select CAST(DATEADD(hh,12,TableB.[Date]+TableB.[Time]), as Date) as [date] , MAX(TableB.HighTemp) as [MaxTemp] 
    from tableA as tableAx 
    right outer join TableB 
    on tableAx.Date = CAST(DATEADD(hh,12,TableB.[Date]+TableB.[Time]) as Date) 
    where TableB.Date is null 
    group by CAST(DATEADD(hh,12,TableB.[Date]+TableB.[Time]) as Date)

TSQL使用DATEADD在where子句中的性能问题

相关推荐