SQL Bulk Delete Queries

Question

I have following queries:

DELETE FROM [Entry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (3, 4, 6, 7, 14, 17, 20, 21, 22,....100 more) 
  AND Entry_Date = '2016-12-01' 
  AND Entry_Method = 'I'

DELETE FROM [WrongEntry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (4, 7, 14, 17, 20, 21, 22, 24....100 more) 
  AND Entry_Date = '2016-12-01' 
  AND Entry_Method = 'I'

DELETE FROM [Entry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (1, 4, 6, 7, 14, 17, 20, 21, 22,....100 more) 
  AND Entry_Date = '2016-12-02' 
  AND Entry_Method = 'I'

DELETE FROM [WrongEntry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (5, 7, 14, 17, 20, 21, 22, 24....100 more) 
  AND Entry_Date = '2016-12-02' 
  AND Entry_Method = 'I'

Like this I have for complete month from date 01-Dec-2016 to 31-Dec-2016.

When I run these queries, it takes a lot of time & also while executing these queries, the table gets locked for the time until the execution is complete.

What is the best way to bulk delete in the above scenario? Is there any other way? How can I bulk delete these queries quickly?

Note: EmployeeId are always different at all the time

What is the rational in `EmployeeId`? Is that not always the same list? — HoneyBadger, Dec 29 '16 at 12:37
Exactly. That's my problem. EmployeeId is always different at all the times. — Anup, Dec 29 '16 at 12:39
missed a BIG detail there bud. thanks @HoneyBadger for picking it up — Stephen, Dec 29 '16 at 12:40
Can you easily build a temporary table with the `employeeId` and `Entry_Date` combinations that you need to delete? If so, you can easily join that table to the table(s) where you need to delete (or use an `exists` or something). How do you know which `employeeId` list you need? — HoneyBadger, Dec 29 '16 at 12:40
Does `SELECT` with the same `WHERE` clauses take the same amount of time? — Giorgi Nakeuri, Dec 29 '16 at 12:45
@GiorgiNakeuri I haven't check that, but if answer is No then? or if answer is Yes then? — Anup, Dec 29 '16 at 12:47
@Anup what is the logic behind generating the employeeID list? is there a flag, or is it all hard coded? — Stephen, Dec 29 '16 at 12:48
@Anup, see if this answer helps: http://stackoverflow.com/a/1158922/1220550 — Peter B, Dec 29 '16 at 12:51
@Anup, if answer is yes, then engine makes a full table scan which will be too slow in case [Entry] and [WrongEntry] tables are big. In this case you can add an index on CompanyId, EmployeeId, Entry_Date, Entry_Method. \ — Giorgi Nakeuri, Dec 29 '16 at 12:52

score 3 · Answer 1 · answered Dec 29 '16 at 12:31

You can change your query to look for a date range instead of a specific date.

Try this.

DELETE FROM [Entry] WHERE CompanyId=1 AND EmployeeId IN(3,4,6,7,14,17,20,21,22,....100 more) 
AND Entry_Date >= '2016-12-01' AND Entry_Date <= '2016-12-31'
AND Entry_Method = 'I'

DELETE FROM [WrongEntry] WHERE CompanyId=1 AND EmployeeId IN(4,7,14,17,20,21,22,24....100 more) 
AND Entry_Date >= '2016-12-01' AND Entry_Date <= '2016-12-31'
AND Entry_Method = 'I'

Please note that depending on your field datatypes you may need to convert the strings to dates in the query.

score 2 · Answer 2 · answered Dec 29 '16 at 12:28

2

Perhaps using inequalities does what you want:

DELETE FROM [Entry]
    WHERE CompanyId=1 AND
          EmployeeId IN (3,4,6,7,14,17,20,21,22,....100 more) AND
          Entry_Date >= '2016-12-01' AND
          Entry_Date < '2017-01-01' AND
          Entry_Method = 'I';

answered Dec 29 '16 at 12:28

Gordon Linoff

1,242,037
58
646
786

This won't do for me. i got lots of inequalities. – Anup Dec 29 '16 at 12:29
1

@Anup . . . What does that mean? If you are trying to delete different ids on each date, then separate queries are probably the simplest method. – Gordon Linoff Dec 29 '16 at 12:55

score 0 · Answer 3 · edited Dec 29 '16 at 13:06

My first recommendation is to put the list into a temporary table or a table variable, this is EmployeeId => tempEmployees . The second is to apply a chunk delete, this technique is very efficient and you can avoid the problems associated with lock, here a sample:

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

In simple words:

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (5000) FROM TableToDelete;

    SET @r = @@ROWCOUNT;

    COMMIT TRANSACTION;
END

SQL Bulk Delete Queries

3 Answers3