Could this cursor be optimized or rewritten for optimum performance?

Question

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

`sp_msforeachdb` and `sp_msforeachtable` are both just wrappers for cursors anyways some performance difference should be minimal. — JNK, Feb 08 '12 at 16:54
Heh go figure.. I never opened them up to see what they did but that does not surprise me. — CheckRaise, Feb 08 '12 at 17:12
I don't think you will upset the SQL-Gods by using a cursor like that. The precense of cursor will not bring your server to a halt, it's what you do with it and how often. In this case you will be looping through 50 rows. I think this is better than any alternatives that I can figure out. — Mikael Eriksson, Feb 08 '12 at 17:21

score 5 · Accepted Answer · answered Feb 08 '12 at 17:31

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.

That's great information beargle that I will definitely keep in mind for future use. In this particular instance, the delete statement will have a where clause. I left it out for brevity. — CheckRaise, Feb 08 '12 at 19:43

score 2 · Answer 2 · answered Feb 08 '12 at 17:59

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.

I appreciate your alternative approach. I wish I could mark both as possible answers! — CheckRaise, Feb 08 '12 at 19:44
Also if undocumented / unsupported aren't enough to dissuade you from using `sp_msforeachdb`, have a look at https://sqlblog.org/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx and http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ — Aaron Bertrand, Feb 08 '12 at 19:50

Could this cursor be optimized or rewritten for optimum performance?

2 Answers2