Watch Brent Tune Queries: Fixing Nested Functions

T-SQL
15 Comments

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch.

However, there are some cases where I just can’t work around a perfect storm of anti-patterns. To understand why, let’s take an example. I’ve kept the general idea the same, but I’ve rewritten the entire example in the Stack Overflow database to protect the innocent.

The most resource-intensive query was a stored procedure – let’s say it looked for interesting users who live in the same location as you, who you might like to meet, because they’ve commented on the same answers that you have:

Uh oh – it called a table-valued function, ufn_UsersYouMightLike. What’s in there?

Uh oh – it’s getting worse. It populates a table variable, and we know those have massive problems. Then, it queries that table variable, and it has a scalar function in the where clause. What’s in that fn_HasCommentedOnAnAnswer?

Hoo boy. We’ve got some work to do. We have a few problems to fix:

  • A table variable – producing bad row estimates, underestimating costs, and probably stopping us from getting parallelism
  • A multi-statement table-valued function – which makes performance tuning way harder for my developers, who couldn’t see what was really happening inside the stored procedure
  • A scalar function – which the above function is going to run row by row, and it’s going to inhibit parallelism too

I’m going to unpack all of ’em, and I could probably fix ’em in any order. Let’s start with the scalar.

Fixing the scalar function by inlining it

My general advice on scalars is to take their logic out and try to inline them wherever they’re called. In our case, the calling query looks like this:

So in a perfect world, I’d open up fn_HasCommentedOnAnAnswer, copy the logic out, and paste it directly into the WHERE clause here. Let’s open it up and see if I can do it:

Drat – they’ve got multiple statements in here. They’re setting a configuration variable, and then using that variable in a subsequent query. The more queries you find in the scalar, the harder the rewrite is going to be.

In this case, I’m going to do a very fast and dirty rewrite. I bet the @PostTypeId is a config variable that doesn’t even change very often, and I could probably hard-code it, but I’m not going to go quite that dirty. Here, I’m just going to dump the config variable into a subquery.

Instead of this:

I’m going to do this:

I know – not elegant – but it does get my scalar function down to just one query:

Which means that now I can take that one SELECT query, copy it out, and paste it into the query where it was being called, ufn_UsersYouMightLike. Here’s the part of that function that was calling the scalar:

If I copy out the scalar’s logic and embed it into the WHERE clause, it’ll look like this:

Ugh. I’m not proud of that, but at least the scalar’s out of the way for now. Next up…

Fixing the multi-statement TVF by inlining it

After inlining the scalar, I’m dealing with an outer function that looks like this:

If you’ve been through the functions module in Fundamentals of Query Tuning or in Mastering Query Tuning, you’ll recognize the problem: this function has multiple queries in it, meaning it’s a perilous multi-statement TVF. Its true cost and work won’t show up in the calling query (the proc), so our developers had no idea how bad it really was.

Like with scalars, my general advice here is to try to turn them into inline (single-statement) functions. Again, the more statements you have, the harder this is – but here, it’s not too bad. Let’s just take the statement that was populating the table variable:

And shove that into the FROM clause, which used to be:

The fast duct-tape way of doing it would be to simply paste it in:

But let’s clean that up a little:

Now I can set aside the table variable, and this whole thing turns into one statement, a magical inline table-valued function:

We still have the stored proc calling a function, mind you – but I’m trying to change the bare minimum of things I can to suddenly get the client across the finish line.

So how does it perform?

I really wanted to show you before-and-after numbers, dear reader, but the “before” query was still going after 45 minutes – and I’m only using the small 10GB StackOverflow2010 database here!

The improved version runs in under a second! Granted, the execution plan isn’t pretty:

And there are obvious improvements we could make to the code and indexes, but…who cares? IT RUNS IN UNDER A SECOND! Rather than chasing further improvements to that one, I’d hand that to my developers, walk them through the changes we made, and then talk about the next terribly long query to go tune. In most cases, the big bang for the buck will be taking those 45-minute-long queries and turning them into just 1 second, rather than turning 1 second queries into 200ms queries.

Recap and lessons learned

When I show this to a developer, there are usually hugs and sobbing involved. They believed – rightfully – that if a feature ships in SQL Server, then it should perform well. That just isn’t the reality, sadly: table variables, scalar UDFs, multi-statement TVFs – these are all things that the documentation don’t warn you about. Sure, they compile – but they sure don’t scale.

These two function examples were simple on purpose because this is a blog post, not a doctoral thesis. When you get into functions that have several statements, each building on the next, building tables and fetching configuration data, this just gets more laborious. It’s not mentally hard, mind you, it’s just hard work, rolling up your sleeves to combine queries into one big ugly monster – or rewriting it from row-by-row functions to a set-based approach inside a stored procedure.

The earlier a senior database developer can get involved in the design and code review process, the less work gets wasted by other developers who don’t know that SQL Server just can’t handle those, nor implicit conversions, dynamic WHERE clauses with OR/ISNULL/COALESCE, etc, things that compile – but don’t scale.

To learn more about why – and how to work around ’em – check out my free Watch Brent Tune Queries videos, and then graduate to the Fundamentals of Query Tuning course.

Previous Post
Tuning Dynamic SQL by Hand with Short Circuits
Next Post
How to Think Like the Engine – 2019 Edition

15 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.