时间戳

首页 > 解决方案 > SQL Server 中的 WHERE EXISTS 慢

sql-server - SQL Server 中的 WHERE EXISTS 慢

问题描述

我有一个使用的删除查询，WHERE EXISTS但速度很慢。

delete 
from dwload.dbo.TF_Full_Backup_AT
where exists (select *
              from dwload.dbo.TFTimesheetTemp t
              inner join dwload.dbo.TFEmpTemp e on t.EmployeeCode = e.EmployeeCode
              where dwload.dbo.TF_Full_Backup_AT.TFSourceID = e.TFSourceID
                and dwload.dbo.TF_Full_Backup_AT.StartTime = t.StartTime
                and dwload.dbo.TF_Full_Backup_AT.EndTime = t.EndTime
                and dwload.dbo.TF_Full_Backup_AT.ActivityCode = t.ActivityCode
                and dwload.dbo.TF_Full_Backup_AT.PaymentCode = t.PaymentCode
                and dwload.dbo.TF_Full_Backup_AT.BranchCode = t.BranchCode)

如何重写此查询以更快地运行？

标签： sql-servertsqlwhereexists

解决方案

很难从您的查询中看出发生了什么。

尝试这样的事情：

DECLARE @ToDelete TABLE
(
    TF_Full_Backup_AT_ID INT PRIMARY KEY
)

DECLARE @LastTimeStamp DATETIME = GETUTCDATE()

INSERT INTO @ToDelete (TF_Full_Backup_AT_ID)
SELECT DISTINCT b.TF_Full_Backup_AT_ID
from TF_Full_Backup_AT b
    JOIN dwload.dbo.TFTimesheetTemp t ON b.StartTime = t.StartTime
                                        and b.EndTime = t.EndTime
                                        and b.ActivityCode = t.ActivityCode
                                        and b.PaymentCode = t.PaymentCode
                                        and b.BranchCode = t.BranchCode
    inner join dwload.dbo.TFEmpTemp e on t.EmployeeCode = e.EmployeeCode
where dwload.dbo.TF_Full_Backup_AT.TFSourceID = e.TFSourceID

PRINT 'Time taken to populate the temporary table: ' + CONVERT(varchar, CAST((GETUTCDATE()-@LastTimeStamp) as time(3)))
SET @LastTimeStamp = GETUTCDATE()

DELETE b
FROM @ToDelete d
    JOIN dwload.dbo.TF_Full_Backup_AT b ON b.TF_Full_Backup_AT_ID = d.TF_Full_Backup_AT_ID


PRINT 'Time taken to delete the rows: ' + CONVERT(varchar, CAST((GETUTCDATE()-@LastTimeStamp) as time(3)))

注意：我假设TF_Full_Backup_AT_ID您的表上有一个主键。

如果您发现实际删除（而不是查找要删除哪一行的查询）花费大量时间，那么您对查询无能为力。您可以批量删除，例如：

WHILE 1 = 1
    BEGIN
        DELETE TOP (5000) b
            FROM @ToDelete d
                JOIN dwload.dbo.TF_Full_Backup_AT b ON b.TF_Full_Backup_AT_ID = d.TF_Full_Backup_AT_ID

        IF @@ROWCOUNT = 0
            BREAK

    END

推荐阅读