CODEWITHSUNDEEP
sql-serverdatabaset-sqlcursor
CheckRaise
CheckRaise

Reputation: 550

Could this cursor be optimized or rewritten for optimum performance?

There is a need to update all of our databases on our server and perform the same logic on each one. The databases in question all follow a common naming scheme like CorpDB1, CorpDB2, etc. Instead of creating a SQL Agent Job for each of the databases in question (over 50), I have thought about using a cursor to iterate over the list of databases and then perform some dynamic sql on each one. In light of the common notion that cursors should be a last resort; could this be rewritten for better performance or written another way perhaps with the use of the undocumented sp_MSforeachdb stored procedure? 

DECLARE @db VARCHAR(100) --current database name
DECLARE @sql VARCHAR(1000) --t-sql used for processing on each database

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name LIKE 'CorpDB%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'USE ' + @db +
    ' DELETE FROM db_table --more t-sql processing'
    EXEC(@sql)
    FETCH NEXT FROM db_cursor INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor

Upvotes: 3

Views: 778

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

This will collect the set of delete statements and run them all in a single sequence. This is not necessarily going to be better performance-wise but just another way to skin the cat.

DECLARE @sql NVARCHAR(MAX); -- if SQL Server 2000, use NVARCHAR(4000)

SET @sql = N'';

SELECT @sql = @sql + N';DELETE ' + name + '..db_table -- more t-sql'
  FROM master.sys.databases
  WHERE name LIKE N'CorpDB%';

SET @sql = STUFF(@sql, 1, 1, '');

EXEC sp_executesql @sql;

You may consider building the string in a similar way inside your cursor instead of running EXEC() inside for each command. If you're going to continue using a cursor, use the following declaration:

DECLARE db_cursor CURSOR 
  LOCAL STATIC FORWARD_ONLY READ_ONLY
  FOR

This will have the least locking and no unnecessary tempdb usage.

Upvotes: 2

Bryan
Bryan

Reputation: 17693

Cursors are bad when they are used to tackle a set-based problem with procedural code. I don't think a cursor is necessarily a bad idea in your scenario.

When operations need to be run against multiple databases (backups, integrity checks, index maintenance, etc.), there's no issue with using a cursor. Sure, you could build a temp table that contains database names and loop through that...but it's still a procedural approach.

For your specific case, if you're not deleting rows in these tables based on some WHERE clause criteria, consider using TRUNCATE TABLE instead of DELETE FROM. Differences between the two operations explained here. Note that the user running TRUNCATE TABLE will need ALTER permission on the affected objects.

Upvotes: 5

Related Questions