CODEWITHSUNDEEP
sql-serverperformance
user2964559
user2964559

Reputation: 77

SQL Server : Delete statement between 1ms and

I use a SqlTransaction in my C# project, and I use a Delete statement with an EcexuteNonQuery call.

This works very well and I have always the same amount of rows to delete, but 95% of the time, this needs 1 ms and approx 5% of the time, it is between 300 - 500 ms.

My code:

using (SqlTransaction DbTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
      SqlCommand dbQuery = conn.CreateCommand();
      dbQuery.Transaction = DbTrans;
      dbQuery.CommandType = CommandType.Text;
      dbQuery.CommandText = "delete from xy where id = @ID";
      dbQuery.Parameters.Add("ID", SqlDbType.Int).Value = x.ID;
      dbQuery.ExecuteNonQuery();
}

Is something wrong with my code?

Upvotes: 1

Views: 63

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Read Understanding how SQL Server executes a query and How to analyse SQL Server performance to get you started on troubleshooting such issues.

Of course I assume you have an index on xy.id. Your DELETE is likely blocking from time to time. This an be caused by many causes:

  • data locks from other queries
  • IO block from your hardware
  • log growth events
  • etc

The gist of it is that using the techniques in the articles linked above (specially the second one) you can identify the cause and address it appropriately.

Changes to your C# code will have little impact, if any at all. Using a stored procedure is not going to help. You need to root cause the problem.

Upvotes: 3

Related Questions