March 8, 2007 at 7:55 am
Guys,
I have a select query which joins mutilple tables, each time when I try search for a record the select query is executed.
Is it advantageous to build a view on top of the select query and do a simple select from the view.
All I can think of is the view is going to save the sql parsing time. Am I right ? any suggestions/inputs on select from multiple tables Vs view would help.
Thanks
March 9, 2007 at 5:49 am
There are normally no issues in SQL Server in using views, or in using a hierarchy of views. SQL Server will merge the syntax of your ultimate SQL query with the definitions of all the views it references to produce the 'final form' query. It will then base all its optimisation on the final form query.
Some DBMSs (I think Oracle still does this) materialise every view referenced, so a hierarchy of views on those DBMSs can badly harm performance.
I have found issues in SQL 2000 where a large number of partitions are joined by a UNION ALL view. SQL is very prone to extract all data to a temporary table, then apply filtering to the temp table. I have heard that the fastfirstrow hint can overcome this, and the issue has been reported to Microsoft.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply