:confused: :confused: Hi All,
I am new to SQL Server. I want to know about SQL Query execution and compilation sequence.
Consider the following case:
SELECT A.COL1, B. COL2
FROM TAB1 A
INNER JOIN TAB2 B
ON A.COL5 = B.COL5
WHERE A.COL3 = 'A' AND B.COL3 = 'B'
Now as per my records into TAB1 and TAB2, condition B.COL3 = 'B' can reduse more number of rows then condition A.COL3 = 'A'.
So, please let me know above way to write the query is proper or not in given senario.
It would be great if one can provide the details how Sql Server 2000 compile and execute the query and some relavent data to optimize the performance while writting SQL query.
Thanks in Advance,
JaiCreate an index on TAB1 (COL5, COL3, COL1) and an index on TAB2 (COL5, COL3, COL2)|||:confused: :confused: :confused: Thanks for your reply.
My main query is,
Suppose there are three conditions in WHERE clause like...
SELECT ....
FROM ...
WHERE
CONDITION1 AND CONDITION2 AND CONDITION3
Now as per the data in my DB I know that CONDITION2 will reduce more number of rows then CONDITION1 AND CONDITION3. As will CONDITION1 will reduce more number of rows then CONDITION3 but NOT more then CONDITION2.
So, in such senario, the sequence of the condition mentioned above is proper or not? Is there any specific rule to mention condition in the specific sequence while writting WEHRE clause in T-SQL?
Please help asap.
Thanks in advance.|||This is not your father's oracle ;)
SQL Server does not parse the query from bottom to top like some other products. Simply stated, the optimizer reviews many possible paths, looking at indexes, primary keys, statistics, and a whole slew of other variables and then designs an execution plan.
An article I read recently stated that prior to SQL 7, if the optimizer came up with alternate plans for a given query, it was considered a bug. With the advent of 7 though, the optimizer became nuch more complex.
So ... code your queries using ANSI standard joins, place your conditions in the predicate, and leave the optimization to SQL Server!|||Are you simply looking to see the order in which the query parts are processed (which you can see via the show plan), or are you looking to actively change the order (which is rarely a good idea)?
-PatP
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment