Expansion Diary Part 5
Last week we had Brian Moran here, from Solid Quality Learning. What a great experience. We had about two days of him just looking around, showing me examples of best practices that I had missed, looking into our actual workload, finding optimizations, etc. Of course, there's good news and bad news...
The bad news is that he wasn't able to just look inside, declare that I'm an idiot, and show me a few indexes or other quick fixes that would make all my problems go away :)
The good news is that I'm not an idiot, I guess.
In general, I learned that I was on the right track. I've got plenty of cleanup to do, of course. But I learned a lot just watching his methodology. I definitely feel as if I'm more prepared now to continue the good fight.
Specifically, he confirmed my fears about my UDF's being EVIL. We have a lot of scalar UDF's, and they are used in some of our hardest hitting queries. One stellar example was a page that took anywhere from 10 seconds to a full minute to load, depending on load, simply by changing it's base query from using a UDF to using a correlated sub query, made the page load instantly. That's a lot of bang for the buck. With it being so obvious, why hadn't I tried that before he got here? I don't know :) Maybe because the page was "way fast" originally and only slowed down over time as load and data increased. I have a totally different mindset for dealing with this sort of issue now.
I've definitely got to wear my "SQL hat" more often!