Views, Functions, And Other Abstractions Don’t Improve SQL Server Query Performance

Maybe She’s Born With It

I get called in to tune a lot of pretty crazy queries. Hands down, the most common scenario is that at some point someone decided to abstract away some of the logic.

Sometimes it’s views, CTEs, or derived tables. Sometimes it’s functions. obviously functions can have a weirder set of effects, but the general idea is the same.

If you start chaining things, or nesting them together, you’re making the optimizer’s job harder and likely introducing a lot of overhead.

Deep Thinkers

There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.

Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.

Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.

It’s a logical separation, not a physical one.

Scustin’

With functions, I mean, one is generally bad enough for a demo. When you start nesting them, introducing loops or recursion, or even mixing scalar and multi-statement functions, things get way worse.

Depending on where the compute scalar that handles the function is placed in the query plan, it can end up “only” running once per row returned by the query.

This is true of scalar valued functions, and MSTVFs that are cross applied. MSTVFs that are simply joined may not exhibit this behavior, though inner joins may be optimized as lateral (apply) joins under different circumstances. So uh. Yeah. Keep fighting that fight.

Oldies, Goodies, Fritchies

An article that I read in…. 2012: The Seven Sins against TSQL Performance

Is, shockingly, still relevant today. A question I’ve started asking people is something along the lines of “when you’re writing a query, or trying to figure out why a query’s slow, do you ever search around for articles about SQL Server performance?”

The answer usually isn’t “yes”. A lot of the problem is that people don’t know what to search for.

They use <some other programming language> and functions are just fine.

Why would functions be bad in a database?

As another example, if you remove a bunch of elements from an array, you have an array without those elements.

When you filter a bunch of rows out of a query with a CTE (or whatever), you don’t have a copy of the table without those rows in it.

Terminus

A lot of people have been trying to get this information in front of as many people as possible for a long time.

I used to think it was just a matter of blogging, presenting, or recording more videos to get people to stop making the same mistakes.

Now I think it’s mostly a case of “I want someone else to do this for me”, and all those things are your street cred.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.