November 30, 2010 at 8:26 am
UPDATE tb_Transaction
SET n_TransactionID = 0
FROM tb_Transaction A
INNER JOIN tb_Manager B
ON A.n_ManagerNo = B.n_ManagerNo
LEFT OUTER JOIN tb_TransactionHistory C
ON A.n_TransactionNo = C.n_TransactionNo
WHERE B.s_Name = 'Pravin'
AND C.n_Transaction IS NULL
above example is just for explaining.
Please can you tell
1) which table will first scan by sql server then which one..
2) which join will be considered first.
3) whether order of join causes query performance?
November 30, 2010 at 8:31 am
That is all determined internally by the database engine, not by the order of the joins.
November 30, 2010 at 8:36 am
pravin.sawant99 (11/30/2010)
Please can you tell1) which table will first scan by sql server then which one..
2) which join will be considered first.
3) whether order of join causes query performance?
1) Check the actual plan
2) Check the actual plan
3) No but the order of join predicates ("ON...") can affect query performance because it can affect the type of the join. Setting the order of ON will inevitably constrain the order of the JOINs.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 30, 2010 at 8:43 am
Statement like you shown is not showing any cascading join. By cascading join i means A --> B --> C. If cascading join is used it will be processed by database engine in order.
Recommend to analyse Query plan to see how SQL engine process your query
Good luck
November 30, 2010 at 8:45 am
Cris,
Do you mean query 2 will run fast than query 1
Query1
UPDATE tb_Transaction
SET n_TransactionID = 0
FROM tb_Transaction A
INNER JOIN tb_Manager B
ON A.n_ManagerNo = B.n_ManagerNo
LEFT OUTER JOIN tb_TransactionHistory C
ON A.n_TransactionNo = C.n_TransactionNo
WHERE B.s_Name = 'Pravin'
AND C.n_Transaction IS NULL
AND A.n_OpCode = 3
Query2
UPDATE tb_Transaction
SET n_TransactionID = 0
FROM tb_Transaction A
INNER JOIN tb_Manager B
ON A.n_ManagerNo = B.n_ManagerNo
AND A.n_OpCode = 3
LEFT OUTER JOIN tb_TransactionHistory C
ON A.n_TransactionNo = C.n_TransactionNo
WHERE B.s_Name = 'Pravin'
AND C.n_Transaction IS NULL
November 30, 2010 at 9:01 am
Query1, Query2 and Query3 are functionally the same. If you run these three queries and look at the actual plans for each one, they are very likely to be identical. The optimiser picks a plan to perform the functional equivalent of your query, and it may be very different to that which you expect.
These three queries are quite trivial, there's little room for manoevre. If you are experiencing a performance issue then it's unlikely to be improved by shuffling the joins or filters around, you are more likely to improve performance by improving indexing on the tables. Check that the columns used in the JOINs and also the WHERE clause are indexed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2010 at 5:32 am
For what it's worth, foreign keys, constraints, statistics, and indexes can all affect the execution plan, so simply pointing at a query and asking which table is accessed first can't be answered. You have to go to the execution plan to know.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 6, 2010 at 9:28 pm
Use execution plan to monitor which is first binding and order of joins. Naturally it will join based upon the order you have given but still based upon the primary keys used across the table will naturally impact the joins.
December 7, 2010 at 5:43 am
vinothraj (12/6/2010)
...Naturally it will join based upon the order you have given...
No, not at all. The natural behavior of the optimizer is to rearrange the join order, right out of the gate. It doesn't assume in any way that the order of the joins should be maintained. You can only control this by using the FORCER ORDER hint, and I strongly recommend against using it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply