SQL Server：执行删除大量查询

I have queries like below, which I have more than 50,000. When I start executing these queries all at once, it takes a long time.

我有下面的查询,我有超过50,000。当我一次开始执行这些查询时,需要很长时间。

DELETE FROM [dbo].[WrongEntry] 
WHERE CompanyId=1 AND EmployeeId = 3 AND Entry_Date = '2016-12-01'

DELETE FROM [dbo].[Entry] 
WHERE CompanyId = 1 AND EmployeeId = 3 AND Entry_Date = '2016-12-01' AND Entry_Method = 'I'

DELETE FROM [dbo].[WrongEntry] 
WHERE CompanyId = 1 AND EmployeeId = 4 AND Entry_Date = '2016-12-01'

DELETE FROM [dbo].[Entry] 
WHERE CompanyId = 1 AND EmployeeId = 4 AND Entry_Date = '2016-12-01' AND Entry_Method = 'I'

DELETE FROM [dbo].[WrongEntry] 
WHERE CompanyId = 1 AND EmployeeId = 6 AND Entry_Date = '2016-12-01'

DELETE FROM [dbo].[Entry] 
WHERE CompanyId = 1 AND EmployeeId = 6 AND Entry_Date = '2016-12-01' AND Entry_Method = 'I'

DELETE FROM [dbo].[WrongEntry] 
WHERE CompanyId = 1 AND EmployeeId = 7 AND Entry_Date = '2016-12-01'

DELETE FROM [dbo].[Entry] 
WHERE CompanyId = 1 AND EmployeeId = 7 AND Entry_Date = '2016-12-01' AND Entry_Method = 'I'

DELETE FROM [dbo].[WrongEntry] 
WHERE CompanyId = 1 AND EmployeeId = 14 AND Entry_Date = '2016-12-01'

DELETE FROM [dbo].[Entry] 
WHERE CompanyId = 1 AND EmployeeId = 14 AND Entry_Date = '2016-12-01' AND Entry_Method = 'I'

I have not much hands on SQL Server, please guide me how to do this in some another way so that performance increases.

我没有太多关于SQL Server的动手,请指导我如何以其他方式执行此操作以提高性能。

4 个解决方案

#1

As per your sample queries, considering CompanyId and Entry_Date same for all the EmployeeId's

根据您的示例查询,考虑所有EmployeeId的CompanyId和Entry_Date相同

DELETE FROM [dbo].[WrongEntry]
WHERE  CompanyId = 1
       AND EmployeeId in ( 3 ,4 , 6, 7, 14)
       AND Entry_Date = '2016-12-01'

For the above Delete, creating the below Nonclustered index could speed up the delete

对于上面的删除,创建以下Nonclustered索引可以加快删除速度

CREATE NONCLUSTERED INDEX IX_WrongEntry   
    ON  WrongEntry (CompanyId,EmployeeId,Entry_Date); 

As per your sample queries, Considering CompanyId,Entry_Method and Entry_Date same for all the EmployeeId's

根据您的示例查询,考虑到所有EmployeeId的CompanyId,Entry_Method和Entry_Date相同

DELETE FROM [dbo].[Entry]
WHERE  CompanyId = 1
       AND EmployeeId in ( 3 ,4 , 6, 7, 14)
       AND Entry_Date = '2016-12-01'
       AND Entry_Method = 'I'

For the above Delete, creating the below Nonclustered index could speed up the delete

对于上面的删除,创建以下Nonclustered索引可以加快删除速度

CREATE NONCLUSTERED INDEX IX_Entry   
    ON  Entry (CompanyId,EmployeeId,Entry_Date,Entry_Method); 

If you can have different dates for each employee then add the values to temp table and join to delete the records just like Gordan's answer

如果每个员工可以有不同的日期,那么将值添加到临时表并加入以删除记录,就像Gordan的答案一样

#2

Limit the number of records to be deleted in a single transaction:

限制在单个事务中删除的记录数:

DECLARE @RowCount INT = 1

WHILE @RowCount > 0 BEGIN

DELETE TOP(500) FROM [dbo].[WrongEntry] 
WHERE CompanyId=1 AND EmployeeId IN (3 ,4 , 6, 7, 14) 
    AND Entry_Date = '2016-12-01'

SELECT @RowCount = @@ROWCOUNT

END

SELECT @RowCount = 1

WHILE @RowCount > 0 BEGIN

DELETE TOP(500) FROM [dbo].[Entry] 
WHERE CompanyId=1 AND EmployeeId IN (3 ,4 , 6, 7, 14)
    AND Entry_Date = '2016-12-01'
    AND Entry_Method = 'I'

SELECT @RowCount = @@ROWCOUNT

END

#3

The deletes are very specific, I would look into creating an index to speed this function up.

删除是非常具体的,我会考虑创建一个索引来加速这个功能。

Here is a free resource from a well renowned SQL Resource Guru that can help you identify missing indexes and what ones you should implement.

这是一个来自知名SQL Resource Guru的免费资源,可以帮助您识别缺失的索引以及您应该实现的索引。

In addition, have you looked into partitioning the table depending on the version of your SQL Server? Partitioning based on the datestamp may drastically increase the performance of your SQL, but is a larger architectural change.

另外,您是否考虑过根据SQL Server的版本对表进行分区?基于日期戳的分区可能会大大提高SQL的性能,但是更大的体系结构更改。

SP_BlitzIndex

Ozar on speeding up deletes

Ozar加速删除

#4

Doing 50,000 deletes is going to take a bit of time. One thing that might make them faster is an index on (CompanyId, EmployeeId, Entry_Date) in both tables.

做50,000次删除需要一些时间。可能使它们更快的一件事是两个表中的(CompanyId,EmployeeId,Entry_Date)索引。

A second thing would be to put the values for those columns into a temporary table and use a join for the deletion:

第二件事是将这些列的值放入临时表并使用连接进行删除:

delete we
    from wrong_entry we join
         #todelete d
         on we.companyid = d.companyid and
            we.employeeid = d.employeeid and
            we.entry_date = d.entry_date;

You still want the indexes for this to be fast. But, in addition, this puts all the deletes into the same transaction reducing the transactional overhead.

你仍然希望这个索引很快。但是,此外,这会将所有删除操作放入同一事务中,从而减少事务开销。

Actually, you could wrap your deletes in a transaction, and that might speed your code as well.

实际上,您可以将删除包装在事务中,这也可能会加速您的代码。