July 27, 2015 at 8:30 am
[font="Verdana"]Query Performance.
For example in a Select Statement we have many tables and we have Where Clause with many conditions with AND operations. Do the SQL SERVER would apply the Where clause after all fetch or can dynamically decide about to include the related Tables from Select Statement Orderly with respect to where clause predicates? (SQL SERVER would not fetch data of those tables for its Select, where the AND condition in Where clause fails or by logic would be fruitless/not-related.)
Please?
What is the default behavior of DBMS in it. Especially of the Modern Version.
[/font]
July 27, 2015 at 11:19 am
SQL Server resolves your query into an execution plan. That shows how it chooses to retrieve the information. It tries to filter the information as it retrieves it, rather than put all the information together all at once and then filter it. But, that completely depends on the query you've written, your statistics, your indexes, constraints, foreign keys, etc. If there are good indexes available, it will filter the data as it retrieves it. If there are not, or, because of how you've written the query, it could go to scans, etc.
"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
July 27, 2015 at 10:38 pm
Grant Fritchey (7/27/2015)
It tries to filter the information as it retrieves it, rather than put all the information together all at once and then filter it.
[font="Verdana"]Great assistance!
Meanwhile i would share some background of this question. FYI only.
That in WHERE (with multiple AND clauses) clause some tables are even used with IN Clause.
Those tables were changed to join statements (in FROM query joins), but query performance downgraded due to all technical scenarios behind.
Thank You, Again![/font]
July 28, 2015 at 2:58 am
Not really surprising. IN is usually a tad faster than a join (a very, very small amount). Changing the IN to a join may have also changed the logic of the query, resulted in more rows, etc, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2015 at 3:07 am
GilaMonster (7/28/2015)
Not really surprising. IN is usually a tad faster than a join (a very, very small amount). Changing the IN to a join may have also changed the logic of the query, resulted in more rows, etc, etc.
Gila, Thank you for your valuable feedback; as always. 🙂
July 28, 2015 at 3:25 am
Please see if the article below can help you in understanding the query execution architecture of SQL server DBMS. Thanks!
http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/
A.I.K
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply