March 31, 2011 at 5:15 am
If I have a view and run 'SELECT * FROM MyView WHERE ID = 123' does the query engine run the whole view then apply the WHERE clause or incorporate it in the query comprising the view itself?
March 31, 2011 at 5:44 am
Let's assume that the view is fairly straight forward. It's a query against a table or a small set of tables that is properly configured. In that case, the optimizer will break the view down and only access exactly what it needs to, moving only the applicable data around. But, if the view is complex, or even worse, it's selecting from and joining other views (called nesting views, a bad thing), then the reverse can occur.
The only way to be sure what is happening is to look at the execution plan for your query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2011 at 9:21 am
A view is a select statement from one or many tables.
When you query the view, the SQL Query optimizer chooses the best execution plan based on the least cost.
So, if you have good indexes on the tables that form the view, SQL will use those indexes instead of scanning all the tables.
In other words, if there are no indexes on the tables, the Yes, SQL will scan the tables and will filter as data is processed based on the WHERE condition you are passing.
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply