jasona.work (10/25/2016)

---

Lynn Pettis (10/25/2016)

---

jasona.work (10/25/2016)

---

Brandie Tarvin (10/25/2016)

---

jasona.work (10/25/2016)

---

So this ought to be fun...

Dev asked me to take a look at a view that isn't performing as well as it did on the system it used to be on. I pulled up the view definition, turns out it joins to a couple other views, so we've got nested views.

Now, that's not a guaranteed performance killer, but it's not a good start. Which is when I find out from the dev that the views that get joined to themselves have nested views, 5-6 levels deep all told...

Sounds like one of my environments. Views built by a power user using SQL Designer because he can't write code, but he knows what data he wants. Then he makes his team use the views when they could write a query joining 3 tables instead of 15 nested views to get the same info.

The "good" news is, the dev who asked about this is also now the guy in charge of it, so he can change it (as long as it doesn't break the app.)

Ought to be interesting to see what comes out of all this...

Considering that my run of the query is *still* going right now, so we're at ~50 minutes + to run (of course, I didn't put a where clause on the query, that might've helped. Maybe I'll kill it and try again with a where, or a top 1)

That could change the execution plan.

Hmm. Hadn't thought of that.

I'll have to try it again tomorrow, as-is, and see what comes up. I did manage to get a result back putting in a where to limit the return, it took 16 seconds (roughly per SSMS) to pop up the result.

I think the solution is going to be a re-write, frankly.