ASP.NET with C# and SQL SERVER , some vb.net . asp.net examples, asp.net codes, asp.net programs, c# code, sql server queries, important codes.
Friday, 19 October 2012
improve query execution in sql server
There are many times that you want to delete a large batch of records, or do a bulk update of some type.
Many times when you do this you will cause locking / blocking of other users - This can be a VERY bad thing for a production database.
You can avoid this generally using this type of query:
SET ROWCOUNT 500
process_more:
DELETE FROM TableName WHERE Condition = 1
IF @@ROWCOUNT > 0 GOTO process_more
SET ROWCOUNT 0
You can use DELETE or UPDATE with this method, by doing this sql will continue looping until the rows affected = 0, processing 500 at a time (or whatever you set the rowcount to be).
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment