Home » sql server »sqlserverpedia-syndication »TSQL » Currently Reading:

Detangling Nested Views

June 30, 2010 sql server, sqlserverpedia-syndication, TSQL 21 Comments

I hate nested views. I hate them, I hate them, I hate them. This is the Nth shop I have been in that makes use of one view that pulls FROM and JOINs other views, which in turn pull from other views, and it’s foot-stompingly frustrating to work with.

I’ve talked before about how inappropriate views hurt performance, and I will talk in the future specifically about how nested views hurt performance, but today I just want to give you the start of a solution.  In time, I hope to develop this code into something beautiful, into a tool that will rewrite nested views into their simplest components, whilst simultaneously washing your dog and paying your overdue utility bills. But for now, this is what we have.

Note: This script is workable, but not complete. It doesn’t currently take different schemas into account, and there’s a lot more functionality I want to add to it.  Consider this a draft, or an early beta, and use it as intended.

A nested view is like a tree: the top node (the parent view) references one or more child nodes (child tables and views), which in turn may reference another level of child nodes, and so on.  Here we have a script that takes a given view and works through each level of nodes, to ferret out all the referenced objects:

----------------------------------------------------------------------------
-- Create temp table, variables
----------------------------------------------------------------------------
-- Create a temp table to hold the view/table hierarchy
CREATE TABLE #viewHierarchy
( id INT IDENTITY(1,1)
, parent_view_id INT
, referenced_schema_name NVARCHAR(255)
, referenced_entity_name NVARCHAR(255)
, join_clause NVARCHAR(MAX)
, [LEVEL] TINYINT
, lineage NVARCHAR(MAX)
)
DECLARE @viewname NVARCHAR(1000),
@count INT,        -- Current ID
@maxCount INT    -- Max ID of the temp table

-- Set the name of the top level view you want to detangle
SELECT @viewName = N'vw_stadium_tickets_current_football',
@count = 1
----------------------------------------------------------------------------
-- Seed the table with the root view, and the root view's referenced tables.
----------------------------------------------------------------------------
INSERT INTO #viewHierarchy
SELECT NULL parent_view_id
, 'dbo' referenced_schema_name
, @viewName referenced_entity_name
, NULL join_clause
, 0 [LEVEL]
, '/' lineage

INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, 1 [LEVEL]
, '/1/' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')

SELECT @maxCount = MAX(id)
FROM #viewHierarchy
----------------------------------------------------------------------------
-- Loop through the nested views.
----------------------------------------------------------------------------
WHILE (@count < @maxCount) -- While there are still rows to process...
BEGIN
SELECT @count = @count + 1

    -- Get the name of the current view (that we'd like references for)
SELECT @viewName = referenced_entity_name
FROM #viewHierarchy
WHERE id = @count

    -- If it's a view (not a table), insert referenced objects into temp table.
IF EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = 'v')
BEGIN
INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, NULL [LEVEL]
, '' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')
SELECT @maxCount = MAX(id)
FROM #viewHierarchy
END
END
--------------------------------------
--------------------------------------
WHILE EXISTS (SELECT 1 FROM #viewHierarchy WHERE [LEVEL] IS NULL)
UPDATE T
SET T.[Level] = P.[Level] + 1,
T.Lineage = P.Lineage + LTRIM(STR(T.parent_view_id,6,0)) + '/'
FROM #viewHierarchy AS T
INNER JOIN #viewHierarchy AS P ON (T.parent_view_id=P.ID)
WHERE P.[Level]>=0
AND P.Lineage IS NOT NULL
AND T.[Level] IS NULL
SELECT       parent.*
,child.id
,child.referenced_entity_name ChildName
FROM #viewHierarchy parent
RIGHT OUTER JOIN #viewHierarchy child ON child.parent_view_id = parent.id
ORDER BY parent.id, child.id

This generates a table that we join to itself to get a hierarchical view of parent and child nodes:

So far, this is useful in quickly getting an overall picture of how complicated a nested view is, how many times it hits a particular table, etc.  Like I said, this is the basics, and I’d like to do more. If you do more with it, drop a comment or email and let me know what you did! I’m always interested in seeing improvements and expansions.

Feel free to use this work in any way you like, but be kind and slap a link back here in the comments.

Happy days,

Jen

And as always, thanks to the Simple-Talk SQL Prettifier for its help today.

Currently there are "21 comments" on this Article:

  1. Bob Pusateri says:

    Very cool! I never knew about the sys.dm_sql_referenced_entities function before!

  2. [...] This post was mentioned on Twitter by Laerte Junior. Laerte Junior said: RT @MidnightDBA: New blog: Detangling Nested Views http://tinyurl.com/267xktc I hate nested views. I hate them,I hate them,I hate them.T … [...]

  3. Ryan Adams says:

    I can’t wait to play with this when I get the time. I am totally behind you with nested views….they are evil. Go write some SSRS reports for Microsoft Operations Manager and see how slowly they run. Then trace them to see what they call and you will find a nested view firestorm like you have never seen before…and all built in nicely to a Microsoft product. Granted they bought it off of someone else and did minimal changes before releasing it as their own, but what’s new.

    In the words of Chris Farley in Black Sheep….Nested views make me want to spike their heads onto the floor of a nightmare you can’t even imagine and dance with you in this exciting ring of fire unless they vacate the SQL premise far…and fast…now!

  4. Jen McCown says:

    Bob, glad to’ve brought you two together! :)

    Ryan – Cripes, man, you hate nested views NEARLY as much as I do! Let me know if you come up with something cool as a result, and I’m going to keep developing this in my “spare time”, too…

  5. Kevin Karns says:

    What a great idea. I’d forgotten one of my nightmares from my ancient past was troubleshooting a hierarchy of MS Access views that processed a mainframe payroll report. Very unpleasant times indeed. I don’t think we have anything like this now in our SQL Server inventory, but if I find some I’ll test this.

  6. Jen McCown says:

    Kevin, please do!

  7. Sean McCown says:

    Jesus, that much code makes my head spin.

  8. Ryan Adams says:

    If you are using Windows Authentication in SQL server you might have to start caring a little about AD passwords. Go do a search for how LM and NTLM work and you will ask your AD admin to change the domain policy right after changing your password to something longer.

  9. [...] My blogs on performance, views, and nested views: Proving Views Can Hurt Performance, Detangling Nested Views [...]

  10. Nate Hughes says:

    Cool script. Added functions. Been better.

    — If it’s a view (not a table), insert referenced objects into temp table.
    IF EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = ‘v’)
    OR EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = ‘TF’)
    OR EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = ‘FN’)

  11. [...] Adam stated, we must do better. There are options to unnest views and optimize your query. Your fix, of course, depends on the situation. These are a few of the basic options [...]

  12. Kurt says:

    Sorry, but this query doesn’t work, it gets stuck in an infinite loop, repeats itself, and keeps adding the same records to the table. I tried it on a simple three-level view.

  13. […] I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here.  […]

  14. […] 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 […]

  15. Robert says:

    HOLY MARY MOTHER OF GOD! I have hit the jackpot!

    I have a “Web App” developer who thinks she is a SQL Developer because she can force SQL engine to sort a view by nesting 6 views with UNION and CROSS APPLY and such.

    THANK YOU SO MUCH for this code so I can actually show “the powers that be” the EVIL THAT DEVELOPERS DO inside of SQL.

    • Jen McCown says:

      I am so pleased this will be useful for you! I’ve meant (for 4 years now) to do more with this, but even in its beta form, it’s been useful to me too. And sometimes, for reasons similar to yours.

      Good fortune, man. Oddly, I’m just about to publish another blog post (tomorrow) about nested views. Great timing.

  16. […] break down, track down or map out the evil of Nested Views, I came across a 4 year old post, titled Detangling Nested Views from @JenniferMcCown, 1/2 of the @MidnightDBA […]

  17. ck says:

    Robert, tell the half baked developer that they can easily sort views by using the TOP 100% if they want to be a dangus, no nesting required.

  18. […] 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 […]

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 2 days 20 hours 15 minutes 49 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/