要执行SQL查询需要很长时间

问题描述：

我有两个表。表2包含更新的记录。表1有900K记录和表2大致相同。要执行SQL查询需要很长时间

要执行下面的查询大约需要10分钟。大多数查询（在执行下面的查询时）到表1给出超时例外。

DELETE T1 
    FROM Table1 T1 WITH(NOLOCK) 
    LEFT OUTER JOIN Table2 T2 
    ON T1.ID = T2.ID 
    WHERE T2.ID IS NULL AND T1.ID IS NOT NULL

有人可以帮我优化上面的查询或写更有效的东西吗？另外如何解决超时问题的问题？

的数据库引擎（SQL服务器？）？桌上有什么索引？ – 2011-06-15 01:43:19

SQL服务器。我是否需要在两个表中的ID列上添加索引？ – 2011-06-15 01:46:00

为什么“T1.ID不是NULL”永远是假的？它在加入的LHS – 2011-06-15 01:46:06

答

优化器可能会选择阻止整个表，因为如果需要删除多个行，它更容易完成。在这样的情况下，我以大块的方式删除。

while(1 = 1) 
begin 
    with cte 
    as 
    (
     select * 
     from Table1 
     where Id not in (select Id from Table2) 
    ) 
    delete top(1000) cte 

    if @@rowcount = 0 
     break 

    waitfor delay '00:00:01' -- give it some rest :) 
end

所以查询一次删除1000行。优化器可能会锁定一个页面来删除行，而不是整个表。

该查询执行的总时间将更长，但不会阻止其他呼叫者。

声明：假定为MS SQL。

另一种方法是使用SNAPSHOT事务。当行被删除时，这种方式的表读取器不会被阻塞。

MS SQL对于标记为“tsql”的问题确实是一个合理的假设;） – 2011-06-15 02:01:25

Alex只想确认。你确定select语句与我的查询结果相同吗？ – 2011-06-15 02:06:40

我在两个表上都添加了索引。尝试执行您的查询。在执行尝试选择一条记录的时间内，会发出超时。你知道什么是错的吗？ – 2011-06-15 02:19:27

答

等一下，你想做到这一点...

DELETE Table1 WHERE ID NOT IN (SELECT ID FROM Table2)

？

如果是这样，那我就是这么写的。您也可以尝试更新两个表上的统计信息。当然，Table1.ID和Table2.ID上的索引可以大大加快速度。

编辑：如果您从设计师获取超时，增加SSMS中的“设计器”超时值（默认值为30秒）。工具 - >选项 - >设计器 - >“覆盖表设计器更新的连接字符串超时值” - >输入合理的数字（以秒为单位）。

我试图在SSMS中添加索引，并且所有时间都超出锁定请求超时期限 – 2011-06-15 01:51:22

我更新了我的答案。 – 2011-06-15 02:00:06

答

两个ID列需要指数

然后用简单的SQL

DELETE Table1 WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table1.ID = Table2.ID)

我试图运行您的查询需要同一时间。表1中的大部分查询都超时。我知道我做错了什么。它是一个生产数据库，因此它有很多查询 – 2011-06-16 14:25:35

@Andrew Venture：索引？ – gbn 2011-06-16 14:27:33

是的，我在这两个表上都有索引 – 2011-06-17 01:50:49

要执行SQL查询需要很长时间

相关推荐