So our aggregated databases have experienced some loading errors lately, and until that gets fixed, I have to collect the data straight from the field databases for each report.
That is, go in the database, pull the data, load into temp.
Go in the next database, pull the data, load into temp.
Go in next, pull, process. Go in next, pull, process.
Sounds familiar? That’s a classic cursor right there.
Sample:
CREATE TABLE pureHatred (
[DBName] varchar(50),
[text] varchar(max)
)
DECLARE @name VARCHAR(50)
DECLARE @sql VARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
While @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ['+@name+']
INSERT INTO pureHatred(DBName, [text])
SELECT DB_NAME(), v.name
from sys.[syscomments] sc
JOIN sys.views v ON v.object_id = sc.id
WHERE sc.text like ''%fill my sproc with hatred%'''
exec (@SQL)
FETCH NEXT FROM db_cursor INTO @name
END
Close db_cursor
DEALLOCATE db_cursor
select * from pureHatred
drop table pureHatred
Now you’ve heard a million times that cursors are a bad approach, and I won’t even deny that fact. They are. They lock rows and load everything into the memory. Cursors also include multiple steps, that is selecting the data, then processing the data, and the lock remains there for the duration of all steps. Also, the server has to execute the plan for each row, each database. (case in point: pull 10K orders each from 500 databases. Oh look! Large numbers.)
I have the luxury to do so in our environment, because we are loaded with memory already. My box still completes the 5 million pull-process cursors in less than 10 seconds, but imagine the peak in memory processes as it does so!* However, we could actually use that memory for other processes, so my solution is only for ad-hoc reports, and only until I fix the data imports. I slap my wrist every time I write up a cursor and hope that the lord of database administrators doesn’t find out about my sin.
But! I don’t know of any other way to jump databases. Feel free to suggest a solution, if you. If you have to repeat the same process in 500-1000-5000 databases, you need a cursor to repeat said process.
So again, should someone toss in the question on your next interview: cursors are bad for using up resources and locking rows.
There are times you can’t avoid them (jumping databases), but if you want to, read up on set-based approaches. I’ll write another article on the matter soon.
Conclusion: If you have to jump databases, you will use UNION ALL 5000 times, or you will load the data in a temp table with a cursor. In this one case I approve of using a cursor, else I you should avoid it.
*You know what? Let me run a query on sys.dm_exec_query_memory_grants next time I set up a mildly complicated cursor, and I tell you just how much memory it costs indeed to use it.
