SQL Server Performance Forum – Threads Archive

Nested Views

Am analyzing a legacy vb6,sql7 application.
It makes elaborate use of nested views (one DB has 120 table and 240+ views and another db has 45 tables with 170+ views).Moreover all these reside on the prod server which experiences heavy volume of transactions. Some of the reports (crystal7)are generated from views in Database2, which make use of multiple tables & views from Database1.(7-8 tables being joined is common)in addition to 3-4 tables from Database2. am currently thinking of using derived tables instead of the nested(inner) views and pick only relevant columns from all the nested views- this will constitute a stored procedure. also some views are executed as sql stmts directly from VB code using ADO methods 1. am i on the right track? is there any way of rewriting query as derived tables make the final query very bulky and difficult to follow? 2. any idea what sort of an improvement i will get by rewriting nested views as derived tables and executing entire query as a stored proc? 3. Can someone provide me some internal workings of views, nested views,derived tables and plain sql in stored procedures? (how they relatively fare regd performance) thanks
Anand

Generally, when you see nested views, this is because the person who created them was not very experienced and didn’t know they were a poor idea. For best performance, I suggest all your code be in a stored procedure, and using derived tables is generally a good option from a performance perspective, considing the alternatives. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

You could use the work tables but just remember to delete them when you are finished. If you will frequently use these work tables you can use the TRUNCATE statement to clean the tables and reclaim the space, while retaining the table structure. These nested view are also know as second level views, if you have two views that join five tables and then create another query that joins the first two views what you’ll end up with is a 10 table join don’t be fooled by thinking that sql server will create the joins first on the two views then just join the third together, what SQL server does is merge the whole query diffinition and run the complete query as one.

List of articles for reference:
http://www.sql-server-performance.com/views.asp
http://www.databasejournal.com/features/mssql/article.php/3319481
http://www.codeproject.com/database/nestedsets.asp HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>