Wednesday, September 29, 2010
fun with stored procedures
I had some fun today optimizing a stored procedure. It was taking about 10 minutes to run, and I got it down to running in two seconds with a pretty minor change.

Without getting into too many gory details, it was basically doing a somewhat complicated PIVOT. It was using a SUM() call to generate a value that it was really just ignoring, just checking to see if it was null or not. I replaced the SUM() with a COUNT(), and checked for zero instead of null, and bam, 10 minutes down to 2 seconds.

This is the kind of stuff I really like doing -- figuring out how to make a minor change in a bit of code that doesn't affect the output, but results in a measurable improvement in performance. The only thing that's still a little frustrating about this is that I don't completely understand why the difference is so great. I have a general idea of why this worked, but something weird must be going on internally with the PIVOT option in MS SQL for this to have made such a huge difference.

Labels: ,

posted by Unknown 7:24 PM

Yup, I remember you occasionally being able to do this with our code too -- always impressive.
Post a Comment

This page is powered by Blogger. Isn't yours?
© 2011 Andrew Huey