Why don’t they make the optimizer faster? (An essay on nested views)

A tired optimizer gives up on nested views

A tired optimizer gives up on nested views

We got a question recently that basically boiled down to this: Nested views shouldn’t be such a performance suck. Why doesn’t Microsoft make the optimizer deal with nested views better?

We’ve looked into this a good deal – nested views are one of my pet peeves, and I’ve blogged about it at least a couple of times (see Detangling Nested Views and Proving Views Can Hurt Performance). What it boils down to is, detangling nested views is way harder than you would think, even for a computer.

“It’s not that hard…”

The straight set math gets exponentially more complicated with every additional level of nesting. You can see this pretty easily if you put together a nested view of 2 levels deep, vs 3 or 4 levels, and then try to pick apart the logic. Plus, each view references anywhere from 1, to some ludicrously high number of tables, often repeating joins to the same tables in different ways. So, it’s even more complex than it first appears to a casual observer.

Go ahead, take an afternoon or seven and try refactoring a nested view manually. You’ll start to feel a lot sorrier for the optimizer.

“Yeah but, the computer could do it, right?”

I’ve talked to the optimizer team about this sort of thing, because it really does seem mathematically possible to work out the best possible plan for a nested view…something that eliminates unnecessary joins, simplifies duplicate result sets, and so on.  I’d be willing to bet that it is  mathematically possible. But the query optimizer itself gives up trying to find a better plan after a certain amount of time*, and that’s by design. In most cases, it’s not worth it to spend 20 seconds (or 90 minutes) finding the perfect, best optimized plan, if you’re taking the execution time from 4 seconds down to 3 with that better plan.

(By they way, take a quick look at this blog post by Jason Strate…pay attention to the bit with “StatementOptmEarlyAbortReason”…what we’re talking about is either “TimeOut” or “GoodEnoughPlanFound”: http://www.jasonstrate.com/2010/12/can-you-dig-it-stmtsimple-element/ ).

So yeah, most likely the optimizer could certainly find some absolutely fabulous plan through the much of a deeply nested view, but it simply isn’t worth it in most cases. It quickly gets to the point where an optimization like that might (theoretically) take hours, if not days to work out the perfect plan. And then in the future, when the plan is pushed out of cache, the optimizer will have to do it all over again. And then, again. And on it goes.

“Well FINE then. So what do I do with these 9-layer views?”

It might be nice if MS gave us the option of turning on a “find best plan at all cost” settings, if only for troubleshooting and refactoring, but it certainly hasn’t happened yet.  I myself have taken a couple of pokes at starting a tool that could do some of the refactoring for you, but only ever got so far as to list out the referenced elements of a nested view.  In all, your best bet is to take the time to work out what it is that the system needs, and refactor your nested views yourself. Your queries, and your optimizer, will thank you for it.

Happy days,
Jen McCown


* Technically this isn’t really a timeout, but a number of attempts to find the plan. The number of attempts the optimizer makes depends on query complexity. Once it runs through that number of attempts, it gives up…but they call it a timeout. Whatever works, I guess. Thanks to Grant Fritchey for that clarification!

Speaking of Grant: For an excellent article on how the SQL Server optimizer chooses when to give up on your ridiculously overcomplex unique snowflake of a query, see Grant Fritchey’s post “Reason for Early Termination of Statement“.

High Tea 2014

Tea_at_the_Rittenhouse_Hotel“The time has come,” the Walrus said,
“To talk of many things:
Of shoes–and ships–and sealing-wax–
Of cabbages–and kings–
And why the sea is boiling hot–
And whether pigs have wings.”

The yearly SQL nerdfest that is the PASS Summit is approaching, and of course that means it’s nearly tea-time!

Let’s put the important stuff up here at the top:

  • We’re scaling down this year, because Reasons. It’s going to be a smaller, more intimate affair. So to speak.
  • This year, High Tea is an invitation only event.
  • So, email me with your request for an invitation by October 15. I’m still Jen at MidnightDBA.com; please put “High Tea Request” in the subject.
  • Only request an invitation if you’ll be in Seattle, WA the week of November 4-7. And you should enjoy adult humor and offensive language. This should all go without saying, but I said it anyway.

Selection: The selection process will be heavily favored toward those who have attended or presented, but don’t think this is a members only club. You guys can all curry quite a lot of favor by showing us you really want to go…say, offer an abstract to speak. Or explain why you deserve to go and others don’t. Or write a love letter to Sean’s hair.  An ode to the show. Your best original limerick.  Whatever, get creative, we’re pretty easily amused.

Call for Speakers: Tell me if you want to speak…we always need good, inappropriate presentations*. Give me a session title and a brief abstract (sessions should be no more than 5-10 minutes long).

Notification: We’ll pick through the invitation requests and choose a bunch of people. Hopefully, we can fit everyone in**.  If we run out of room, we’ll do a lottery or a bidding war or a virtual Jell-O fight or something for the remaining spots.  You’ll have your invitation in email before the Summit, along with the venue and time.

For those who have no idea what I’m talking about: We hold an annual event during the Summit, for like-minded folk. The original idea was “hey, there are all these rules we have to follow at the big conference when we teach a session. Wouldn’t it be great if we could just say and do whatever we felt like? WAIT A MINUTE!!” And then people got together and drank and heard inappropriate things, and had a jolly good time. And so shall we again, my friends. So say we all.

Happy tea time,

*No, your presentation isn’t required to be perverted in nature, or to contain vulgar language. Some of our very best sessions were…nothing at all like you’d expect. Of course, other sessions are probably exactly what you’d expect, so there’s that.


Wendy Pastrick and Grant Fritchey: Optimists in Action #passvotes

wendyIf I had a favorite kind of politician, it would be the kind that, when asked a question, sighs tiredly, and then regains their gleam of manic optimism as they begin to speak about the path ahead.

Given that, my two favorite politicians right now are Wendy Pastrick and Grant Fritchey.

Both of these people are realists, with that unaccountable combination of caution and optimism that I like in friends and technologists alike. Both give to excess to the SQL community. Wendy  speaks, volunteers, works with virtual chapters and user groups, acts as a regional mentor, and joined the PASS Board in 2013. She’s a PASSion Award recipient. Grant speaks, organized and ran a user group, volunteers, has edited the SQL Standard magazine, and on and on.

I’ve talked to multiple board members, past and present; it’s a difficult job. I support Wendy and Grant because they can do the job, do it well, and make a difference. And when their terms are done and we ask them how it went, they will sigh, and then get that manic gleam again…

grantGet information about the PASS elections here, and look for your ballot in  email  around September 24 or so. Vote!

Happy days,
Jen McCown

Real news, real tech.