CODEWITHSUNDEEP
sqlado.net
user3477335
user3477335

Reputation: 165

Batch Deletes in ADO.NET

I want to delete a set of records based on id's passed.

Example: I have id's in a list which am iterating to add parameter to delete query and executing delete statement each time.

for(i=0;i<n;i++)
{
Delete from tbl_abc where id=i;
}

I want to make a string of id's as comma seperated and pass to delete query to as IN Statement.

Delete from tbl_abc where id in (0,1,2,....n);

What is performance gain if I use IN in delete query without iterating list and pass Id as parameter?

Upvotes: 2

Views: 207

Answers (1)

Muhammad Nagy
Muhammad Nagy

Reputation: 226

Lets say that we have 1 million rows in the tbl_abc and we want to delete 10,000 rows. If you execute 10,000 delete statement this mean that you make 10,000 round trips to the database and executing 10,000 statement and waiting for the result of each one before executing the next one. In addition, if you use a column in where condition that is not indexed this will lead to table scan 10,000 times which is a very slow operation.

On the other hand, If we send a single delete statement. We will scan the table one time, doing atomic database operation which will save us from some concurrency deletion issue, and will have only one database round trip.

Upvotes: 2

Related Questions