T-SQL Speed Phreakery Explained

, 2010-02-08

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!





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads