Blog
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.
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: programming, SQL
Comments:
Post a Comment
© 2011 Andrew Huey
