2

First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface.

First few weeks with this client and users are complaining about performance constantly with even the most bread and butter reporting functionality. I finally get DB access and discover the worst set of nested views (Is nested view a good database design?) I've seen in my life. We're talking a tree of views that is 5 nodes deep and each node has 3-5 leaves. So a single query has 20+ views behind it. It's mind bogglingly bad. The explain plan looks like a fractal after ten million iterations. I can't even read it.

I need to untangle at least this one monster view. But I have no ideas on how. I found this post (http://www.midnightdba.com/Jen/2010/06/detangling-nested-views/) which has a handy SQL script that lists the referred objects but that information doesn't get me terribly far on getting the actual table references for each field in the parent view. If I were to do it by hand, it would probably take 2 weeks. If I got it down to inline form, are there any tools that could remove redundancies and unused fields and optimize the query?

  • 2
    The free SQL Sentry Plan Explorer will at the very least show you the base tables being used (run a select from the view and switch to the join diagram tab), but even that may not prove to be overly helpful, depending on how complicated that diagram ends up being... Disclaimer: I work for SQL Sentry. – Aaron Bertrand Apr 24 '14 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.