T-SQL Tuesday 200: When I Look at a Query …

First up, congrats to Adam Machanic and Steve Jones on reaching 200 episodes of T-SQL Tuesday; you can be proud of yourselves! I’ve always loved the variety of topics, the different views of the bloggers, and the learning that can be gained from all the posts. I’ve even had the honour of hosting.

Anyway, Brent Ozar invites us this month to write about the most horrible things we’ve encountered in a query.

The situation

Let me take you back about ten years. I was called in on a reporting issue. The client complained (rightfully) that reports were taking ages to load and asked if I could take a look at their database and configuration, and maybe give some advice on scaling up.

Now, it’s very easy, especially in these days of unlimited cloud resources (different discussion, but with AI taking over, the cloud does start to hit its limits), you slide a slider to the right and all issues disappear. Right? Well, maybe if you have an unlimited credit card.

In this case, I started out with the server and database configurations. Even though not everything was in line with best practices, there were no red flags.

The issue

So, I asked the report person to send me the query that was executed on the database to create all the nice, fancy graphics. You won’t be surprised that there was more than one. If I remember correctly, there were ten queries.

When I opened them, there was a long SELECT list, 7 tables with LEFT OUTER joins and some filtering. The joins and filters were what I’d expect from a reporting query. Far from optimal, but again, not something that should cause great issues. I did notice something weird in the SELECT list, though. Something that got confirmed when looking at the execution plans.

User Defined Functions

In an effort to make code reusable, someone decided to use Scalar User-Defined Functions. About ten of them. And each function was called in the SELECT list of each query sent to the database.

There was no parallelism to be found; every query went row by agonising row.

The fix

To check if I was correct, I removed the UDF’s, replaced them with the query logic and ran the query again. It was so much faster.

I changed the code for all ten queries and ran the report again. And from a long wait, the report refreshed in under 5 seconds. I’m not sure what the aim was, but the customer was very happy indeed. Not only were report refreshes fast, but they also didn’t have to go through a lot of hoops to upgrade their server.

Concluding

User-defined functions: they probably have some good use cases, and with recent versions of SQL Server, they’re not as bad as they were, but these are still the first ones I’m looking for.