CODEWITHSUNDEEP
databaset-sqlsql-delete
enamrik
enamrik

Reputation: 2312

Given 10,000 record IDs, which way is the correct way to delete the records?

Which is better, a delete statement with a where clause containing 10,000 or filters (e.g. delete from table where id=1 or id=2 or id=3, etc) or a executing 10,000 delete statements (e.g. delete from table where id=1; delete from table where id=2;, etc)? The database is MS SQL Server.

EDIT

The IDs are not sequential and there is no where clause I can use to catch all of them (e.g where id > 100 and id < 50) because I have no idea how these IDs are related (besides the fact that they're all from the same table). So it's really down to a question of performance.

Upvotes: 0

Views: 370

Answers (3)

HLGEM
HLGEM

Reputation: 96600

For performance the worst method is to delete one row at a time. However, deleting too many at a time can also cause user issues with locking and blocking. Often the best bet is a hybrid of the two where you delete a batch (say about a 1000 records) at a time.

Now this gets much more complex when you consider that the records being deleted may have child records in a another table. Some people do the deletes then through cascade delete and others start from teh child tables and work up. Either situation may mean you have considerably more more than 10000 records to delete and then the batches would need to be smaller to avoid locking. Some people might want to do one record at a time in this case to ensure that the failure if an existing child record was not deleted first, the other records are stil inseterd and you know which records failed. this is particularly true of the types of records whenre the very existance of child record means that you should not delte the parent (you can't delete a customer who has actually bought things or the finanical records get all messed up for instance).

Upvotes: 2

Brandon Galbraith
Brandon Galbraith

Reputation: 474

Are the IDs sequential? If so, you could run the following SQL statement:

DELETE FROM table_name 
WHERE ID BETWEEN id_lowest and id_highest;

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416055

Where'd you get the IDs to delete? Surely with that many they're in the database somewhere. If that's the case, you do it like this:

DELETE FROM MyTable 
WHERE ID IN ( /* Select query that returns your 10000 IDs here */ )

If they're not in the database, add them, even if it means creating a table to hold them that will only exist long enough to complete this.

Upvotes: 6

Related Questions