C# & SQL Server - The timeout period elapsed prior to completion of the operation or the server

0
votes

The question involves SQL Server and C#.

I am trying to execute multiple delete statements in one query. The format has the following structure:

DELETE from dbo.table where column = 'value'

repeated more than 100000 times

I build the command through a StringBuilder and call it that way in my C# code:

cmd.Connection = con;
int rows = cmd.ExecuteNonQuery();

However it takes a lot of time to execute and ends with this error:

The timeout period elapsed prior to completion of the operation or the server. Executing through Management Studio takes also a lot of time, and let me think that the query isn't performant enough. However, in this case the query is executed successfully.

Obviously, using fewer DELETE statements, the query ends properly due it's a very simple query. What is the best way to execute multiple statements and avoid this error?

1
c#sql-server-2008timeout
Another point to consider: by default, SQL Server uses row-level locks and locks only those rows affected by e.g. a DELETE statement. However, if you pass 5'000 rows in a single transaction, SQL Server will do a lock escalation and move the lock to the entire table which becomes exclusively locked until the transaction finishes. This means: until the transaction finishes, no one can even read from that table! So maybe you need to batch up your DELETE operations into smaller batches to avoid an exclusive table lock – marc_s

1 Answers

0
votes

Can't you do only a single DELETE execution with the "IN" instead of "=" in your query? Do something like:

DELETE from dbo.table where column in ('Param1','Param2','(...)')

Also, you could check this article: http://social.technet.microsoft.com/wiki/contents/articles/20651.sql-server-delete-a-huge-amount-of-data-from-a-table.aspx

Cheers!