使用WHERE子句删除-日期、时间和字符串比较-非常缓慢

Question

我有一个执行缓慢的查询，希望有更多SQL知识的人能够帮助我提高性能：

我有两个表：Source和Common。我加载一些数据，其中包含日期、时间和字符串（即服务器名称），以及其他一些信息。

Source表可以包含40k+行（它有30个左右的列，包括int、date、time和一些varchar(255)/(Max)类型）。

我使用以下查询来删除Common中与Source相同的数据：

'Delete from Common where convert(varchar(max),Date,102)+convert(varchar(max),Time,108)+[ServerName] in (Select convert(varchar(max),[date],102)+convert(varchar(max),time,108)+ServerName from Source where sc_status < 300)'

Source字段的格式如下：

- ServerName varchar(255)，例如SN1234 - Date varchar(255)，例如2012-05-22 - Time varchar(255)，例如08:12:21

Common字段的格式如下：

- ServerName varchar(255)，例如SN1234 - Date date，例如2011-08-10 - Time time(7)，例如14:25:34.0000000

谢谢。

- TomEaton

---

哪个表是哪个？两个标签都写着“源”...为什么一个表将日期/时间数据存储为大字符串？它们的确切格式是什么？我们无法猜测这些事情。请向我们展示一些样例数据... - Aaron Bertrand

你为什么在日期和时间字段中使用varchar（255）？ - MichelZ

为什么要将它们连接成一个字符串？我强烈建议进一步进行SQL培训，如果在面试测试中做这样的事情，你就完蛋了。 - Tony Hopkinson

数据是IIS日志，日志中的一些条目可能是重复的...因此，我只是根据服务器名称、日期和时间进行操作。这样，我可以每天加载多次日志。我完全同意，我确实需要更多的培训，我正在积极阅读和尝试。我必须尽快完成它，我的MySQL水平很差。我很抱歉。 - TomEaton

@TomEaton。不必道歉，我只是想确保你知道还有一些需要学习的地方。那些认为自己无需学习的人才需要不断地深表歉意。 - Tony Hopkinson

Answer 1

将双方转换为字符串，然后将它们连接成一个大字符串，再比较这些结果并不是非常高效的。只有在必要时才进行转换。尝试本例并查看其比较情况：

DELETE c
  FROM dbo.Common AS c
  INNER JOIN dbo.Source AS s
  ON s.ServerName = c.ServerName
  AND CONVERT(DATE, s.[Date]) = c.[Date]
  AND CONVERT(TIME(7), s.[Time]) = c.[Time]
  WHERE s.sc_status < 300;

- Aaron Bertrand

---

这个解决方案看起来相当不错。主要的是要避免转换和字符串拼接。我想知道是否有必要转换 s.Date 和 s.Time 列。 - bjorsig

如果您不明确地执行它，它将作为隐式转换自动执行。 - Aaron Bertrand

我喜欢显式地表达自己，而且想象一下，如果日志解析器中的日期与数据库中的日期格式不同，至少您可以直接插入预期的格式并获得一个合理的错误消息。 - Tony Hopkinson

Answer 2

所有这些转换成 VARCHAR(MAX) 的操作都是不必要的，而且很可能会拖慢你的速度。我建议你改用以下代码：

DELETE c
from [Common] c
WHERE EXISTS(
    SELECT 1
    FROM Source
    WHERE CAST([Date] AS DATE)=c.[Date]
    AND CAST([Time] AS TIME(7))=c.[Time]
    AND [ServerName]=c.[ServerName]
    AND sc_status < 300
);

- John Dewey

Answer 3

类似于

Delete from Common inner join Source 
On Common.ServerName = Source.ServerName 
and Common.Date = Convert(Date,Source.Date)
and Common.Time = Convert(Time, Source.Time)
And Source.sc_Status < 300

如果在此之后速度仍然太慢，那么您需要在两个表上建立一些索引。

- Tony Hopkinson

---

在“Source.Date”和“Source.Time”上创建索引并不能真正帮助，因为仍然需要进行转换。 - Aaron Bertrand

Answer 4

根据Aaron的回答，删除不必要的转换将会有很大帮助。您还可以考虑在日志表上创建一个索引视图，因为您可能没有太多的灵活性来更改模式或从日志解析器插入DML。

以下是一个简单的示例：

create table dbo.[Source] (LogId int primary key, servername varchar(255), 
   [date] varchar(255), [time] varchar(255));
insert into dbo.[Source]
    values  (1, 'SN1234', '2012-05-22', '08:12:21'),
            (2, 'SN5678', '2012-05-23', '09:12:21')
go

create view dbo.vSource with schemabinding
as
    select  [LogId],
            [servername], 
            [date], 
            [time], 
            [actualDateTime] = convert(datetime, [date]+' '+[time], 120)
    from    dbo.[Source];
go

create unique clustered index UX_Source on vSource(LogId);
create nonclustered index IX_Source on vSource(actualDateTime);

这将为您提供一个索引的日期时间列，从而改善您的查询执行计划，但会以插入性能为代价。

- nathan_jr

---

您可能是想在视图定义中使用 convert(datetime, [date] + ' ' + [time], 120) 。不过，这将是一个非常耗费资源的视图，用于批量加载过程。如果批量加载处理程序无法处理它不能接触的列，则我几乎宁愿在该表中添加一个计算列，而不是整个材料化视图。 - Aaron Bertrand

已修复，谢谢！如果 OP 可以在不破坏其日志解析器的情况下更改模式，则计算列肯定是理想的。亚伦说得好。 - nathan_jr