Emergency Broadcast:
Last week, I stumbled across a “legacy” system. Small system, few tables, few stored procs, few web pages that let users manage it. The main table had about 40k rows in it, nothing huge. This system gets used sparingly.
Reports of the main page of the system taking 5+ minutes to load, or just timing out. Really? Dug through the code and lo and behold. A cursor.
Looping through 500+ items, and running 8 or so queries in each iteration. The queries were very similar:
SELECT some stuff INTO Cursor
WHILE LOOPING THROUGH CURSOR
SELECT STUFF 1
SELECT STUFF 2
..
..
END
I ended up re-writing the query to just grab all the data and did some case statement in the SELECT. They query went from 5+ minutes to less than 1 second.
Yes, CURSORs are bad. Avoid them. Rewrite your SQL. It amazes me that with all the good info out there on the web around SQL that people still write CURSORs in their queries. I asked the DBA to look at all procs on all systems and find all the CURSORs so we can root them out once and for all.
End of Emergency Broadcast
Steve Novoselac's Blog 