在SQL Server 2008中优化大型数据集的更新查询

Question

我在一个表的update触发器中有一个查询(见下文)，并根据trigger.For中当前更新的内容更新大型数据集上的某些字段，这是一组100000条记录，大约需要22秒，我希望对此进行更多优化

update RED
set EDR_IsLock = @il
where RED.docid in 
      (select a.DocID
       from 
           (select distinct DocID 
            from UVD
            where UVD.UVID in (select i.UVID from inserted i)) a 
       left outer join
           (select distinct UVD.DocID 
            from UVD
            inner join UVH on UVD.UVID = UVH.UVID 
            where UVD.UVID not in (select i.UVID from inserted i) 
              and UVH.IsLock = 1) b on a.DocID = b.DocID
       where b.DocID is null) 

PS: Table Red包含超过万亿条记录

Answer 1

尝试使用Join而不是in子句。试试下面的一个，它可能会对你有帮助。

Update  r
Set     EDR_IsLock = @il
From    RED As r
        Join
        (
            Select  a.DocID
            From    (   Select  Distinct DocID 
                        From    UVD
                                Join inserted i On UVD.UVID = i.UVID
                    ) a 
                    left outer join
                    (
                        Select  Distinct 
                                UVD.DocID 
                        From    UVD
                                join UVH on UVD.UVID = UVH.UVID 
                        Where   UVD.UVID Not In (Select i.UVID From inserted i)
                                And UVH.IsLock = 1
                    ) b on a.DocID = b.DocID
            Where   b.DocID is null
        ) As t On r.docid = t.DocID

更新：

我不是一个很好的解释者，但是，如果“不存在”而不是“在子句中”产生意义差异，那么我会重写上面的查询，如下所示：

Set Nocount On;

Declare @UVD Table
(
    DocID           Int
)

Declare @UVDWithUVH Table
(
    DocID           Int
)

Insert Into @UVD(DocID)
Select  Distinct 
        DocID 
From    UVD As u With (Nolock)
        Join inserted i On u.UVID = i.UVID

Insert Into @UVDWithUVH(DocID)
Select  Distinct
        u.DocID 
From    UVD As u With (Nolock)
        Join UVH As uh With (Nolock) on u.UVID = uh.UVID 
Where   Not Exists (Select 1 From inserted As i Where i.UVID = u.UVID)
        And uh.IsLock = 1

Update  r
Set     EDR_IsLock = @il
From    RED As r
        Join
        (
            Select  a.DocID
            From    @UVD As a
                    Left Outer Join @UVDWithUVH As b On a.DocID = b.DocID
            Where   b.DocID Is Null
        ) As t On r.docid = t.DocID

在这个解决方案中，我建议使用@Table变量，它将驻留在Ram中，而不是物理存储中。并在连接时消除在内部查询中查询distinct的开销。

也试一下这个更新的版本，想知道它对提高触发器的性能有帮助吗？

Answer 2

我个人会将其重写为：

UPDATE  RED
SET     EDR_IsLock = @il
WHERE   RED.DocID IN
        (   SELECT  UVD.DocID
            FROM    UVD
            WHERE   RED.DocID = UVD.DocID
            AND     NOT EXISTS (SELECT 1 FROM inserted i WHERE i.UVID = UVD.UVID)
            AND     NOT EXISTS
                    (   SELECT  UVD.DocID
                        FROM    UVD AS UVD2
                        WHERE   UVD2.DocID = UVD.DocID
                        AND     EXISTS (SELECT 1 FROM UVH WHERE UVH.UVID = UVD2.UVID AND UVH.IsLock = 1)
                        AND     NOT EXISTS (SELECT 1 FROM inserted i WHERE i.UVID = UVD2.UVID)
                    )
        );

在所有实例中，我都用EXISTS替换了JOIN和IN。作为documented here，SQL Server在使用LEFT JOIN/IS NULL删除记录时遇到了问题，在最好的情况下，您将获得与NOT EXISTS相同的性能，但有时LEFT JOIN会更差。LEFT JOIN/IS NULL不能使用反半连接(一旦找到一条记录就会停止查找/扫描)与EXISTS is.With您当前方法的方式相同，您从UVD中选择所有符合条件的记录，然后对它们进行排序，以便可以删除重复记录，然后使用这些结果丢弃在子查询a中找到的记录。

类似的逻辑也适用于INNER JOIN，在用EXISTS替换时，您是在告诉SQL Server，您并不关心UVH中的记录是什么，您只关心有一条记录。

我真正做的唯一其他更改是将NOT IN更改为NOT EXISTS，这可能什么都不做，但如果存在NULL记录，NOT IN将导致意外行为。

完成这些更改后，您应该运行查询，并显示实际的执行计划。这将有助于识别瓶颈，SQL Server甚至可以建议索引来加快查询速度。