SP: Execute long-running queries in small chunks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

SP: Execute long-running queries in small chunks

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-07 : 13:35:02

Here's a little SP to break up those long-running, massively-locking, bring-app-to-a-halt queries. By default it does 500 rows at a time and allows for a maximum SQL query size of 4000 characters; it should be trivial to adjust those.

Cheers
-b


CREATE PROCEDURE p_BatchExecute (@vcSQL varchar(4000)) AS
set nocount on
DECLARE @iRows int
select @iRows=1
SET ROWCOUNT 500
WHILE @iRows>0
  BEGIN
    print 'Executing batch of 500...'
    exec (@vcSQL)
    set @iRows=@@ROWCOUNT
  END
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 14:08:29

I might be stoned, but won't this just return the same 500 rows over and over again? Each EXEC statement forms its own batch, so there's no relation between multiple calls. Wouldn't that also lead to an infinite loop?

Edited by - robvolk on 05/07/2002 14:10:45

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-07 : 16:26:25

I should have titled this, "execute long-running deletes." Yes, for selects or updates, it would likely get the same rows over and over again, and it would certainly create a loop.

For deletes, though, it works like a charm.

Cheers
-b

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-23 : 14:32:19

You could use this for selects if it moved 500 records to another table at a time. Assuming it "marked" the source records as processed in each pass and each pass onyl looks at unprocessed records.

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources