March 16, 2018 at 6:12 pm
I can’t find a lot on the Internet on nested views. Was wondering if other companies nest views and how deep they nest them? Are there policy limits companies use?
Below is a view a developer recently put into production. It still runs quickly actually.
For this view to run it takes 13 other views to run, 1 table function, 2 scalar functions, and 4 inline select statements.
vw_FacAllList is run 3 times in different nesting levels whichin turn runs vw_AAA_List, vw_BBB_List , 3 times indifferent nesting levels. These 2 views are then run again in a differentnest, now running 4 times.
vw_ContFac (uses below)
vw_Contacts (uses below)
vw_FacALLList (uses below)
vw_AAA_List
vw_BBB_List
fnUsr_ReturnConNameLF() (Scalar Function)
vw_FacilityZZALLList (uses below)
vw_FacALLList (uses below)
vw_AAA_List
vw_BBB_List
vw_AAA_List
vw_BBB_List
fnDepttoFacLatest() (tablefunction) (uses below)
vw_FacAllList (uses below)
vw_AAA_List
vw_BBB_List
1 inline selectstatement
4 inline selectstatements
fnCon_ReturnName_LastFirst() (Scalar function)
March 20, 2018 at 6:47 am
Well, I'm not sure there's any specific guidance on this, but I have to wonder about a database design that fosters the creation of such a convoluted query... Without considerably more detail, it's difficult, if not impossible, to offer much in the way of detailed advice. My biggest question is going to be in the category of "What's the objective, and why can't it be satisfied more succinctly?", and whose answer may be "the existing database design".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2018 at 7:15 am
Nested views are the spawn of the devil. They will leech the performance right out of your server. They seem so logical but in reality they are truly awful. Grant has a great write up just how truly bad nested views really are.
https://www.red-gate.com/simple-talk/sql/performance/the-seven-sins-against-tsql-performance/#seven
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply