SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

T-SQL Speed Phreakery Explained

If you haven't seen the Simple-Talk newsletter (it's free, so sign up!) you may not have seen my article on their site. http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/  Writing this article has been probably the most fun I have had writing. Basically, I had to take the winning solution to a T-SQL programming challenge and try to figure it out and explain how it works. I also compared the winning solution to three other solutions that are easier to write and understand. If you are having a hard time convincing yourself that row-by-row processing isn't that bad, this article should convince you otherwise.

In my job, I inherited several stored procedures that run once per night that would take 10 minutes or more to run. Besides the time they took to run, they could also leave some of our applications in non-working order if the job happened to fail. For example, the proc to add new users to our document management system (DMS) first would disable all users. Then set up a cursor that updated each user with current information and re-enabled the active employees. If anything went wrong in the job, for example an unexpected NULL value in the data from our HR system, the entire office could be locked out of the DMS. The the proc could never be run during business hours and there was lots of hard-coding that made the proc difficult to maintain.

So, within a few months I rewrote these problematic procs using set-based code instead of cursors. I also corrected the faulty logic so the the procs could run anytime if we needed them to and set up a table to hold the data that used to be hard-coded. A stored procedure that used to take 10 minutes, now runs in about 10 seconds. I didn't use any of the phreaky techniques that the winner of the contest used, but just eliminating the row-by-row processing made my process very efficient!

Advice from Aunt Kathi

Kathi Kellenberger is a Sr. Consultant with Pragmatic Works. She is an author, speaker and trainer.


Posted by Jason Brimhall on 8 February 2010

Amazing how often a DBA gets to optimize code when starting someplace new.  I had a very similar situation just this last week.  A process that inserts new records into a table used to take 10+ minutes.  With just a couple of tweaks - it now runs in under 10 seconds.

Posted by Glenn Berry on 9 February 2010

It seems like lots of developers and Jr. DBAs like to loop through cursors to do things instead of doing it in a set based manner. Good job on getting that cleaned up!

Posted by Jerry Tienter on 12 April 2010

Very, very good.

Leave a Comment

Please register or log in to leave a comment.