There are no comments yet...Kick things off by filling out the form below.
Reduce Locks for DELETE and UPDATE Commands in SQL Server 2005 with TOP Clause
As you may know, one of the main reasons for deadlocks in a database is long execution of commands especially DELETE commands in a single transaction because a DELETE command runs a single transaction and if it takes a longer time to run then it may end with a long lock in database.
SQL Server 2005 came with the introduction of new T-SQL enhancements including some improvements for TOP clause. Previously you could use TOP clause only in SELECT statements with literal values. In new version of T-SQL you can use it with dynamic values and also apply it to DELETE and UPDATE statements.
One of the techniques that I apply to my projects to reduce the lock on database for DELETE and UPDATE statements is using TOP clause to run my commands in several batches.
This is easy to implement and use and can have a huge impact on database performance when you're dealing with such DELETE and UPDATE commands.
The technique is very simple: you use a TOP clause for your commands in a loop to run your DELETE or UPDATE commands and run this loop until the command executes for all specified rows in the database.
An example would clarify this. Suppose that I have a table named SampleTable with a very simple structure that has an ID and a Value column. Following stored procedure would insert 1000 rows into this table.
CREATE PROCEDURE dbo.InsertData
AS
DECLARE @Counter int
SET @Counter = 1000
WHILE (@Counter > 0)
BEGIN
INSERT INTO SampleTable
(Value) VALUES (@Counter * 2)
SET @Counter = @Counter - 1
END
RETURN
Now to delete these rows in SampleTable you can simply write a single DELETE command but for large number of rows this can yield lots of locks and reduce the overall performance. But here I write another stored procedure that runs such a command with TOP clause in a loop until all rows are deleted. Since this is running for small number of rows this can have a good effect on performance.
CREATE PROCEDURE dbo.DeleteData
@BatchSize int = 100
AS
DECLARE @Rows int
SET @Rows = 1
WHILE (@Rows > 0)
BEGIN
DELETE TOP (@BatchSize) FROM SampleTable
SET @Rows = @@ROWCOUNT
END
RETURN
This latter stored procedure has a very simple logic. It gets a batch size as its parameter and uses it in the DELETE command. It executes this command in a loop until Rows is larger than zero. Since Rows is equal to the number of affected rows in the last execution it will be larger than zero until all rows are deleted!
You can apply same technique to UPDATE commands as well.
[advertisement] Axosoft OnTime 2008 is four developer tools in one: bug tracking, project wiki, feature management, and help desk. It manages your development process so developers can focus on coding. Installed or Hosted – Free Single-user license -- Free 30-day team trial.
No Comments : 03.17.08