May 25, 2012 at 12:01 pm
--Table Driven version.
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
WHERE ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
Becase first is not true left join and is more like an inner join.The second is true left join...an inner and left join doesnt returns same output unless both tables have same data for joining columns..
here is how otpimizer does processing.
It first join table T1 with T2 based on join clause. Then on the output of the join it applies where clause to filter the data.
This is happening in first case.
in second case...there is no where calause so no filtering is applied. If join type were "inner" both query would have returned same results.
Try following
--just run this
SELECT T1.ID, T1.CHILD_ID, T2.ID,T1.ISDELETED,T2.ISDELETED
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
--the try this
SELECT T1.ID, T1.CHILD_ID, T2.ID,T1.ISDELETED,T2.ISDELETED
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
and ISNULL(T1.ISDELETED,0)=0
--then use the where clause as well..You will see that the rows with t2.isdeleted = 1 are no longer appearing
SELECT T1.ID, T1.CHILD_ID, T2.ID,,T1.ISDELETED,T2.ISDELETED
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
ISNULL(T1.ISDELETED,0)=0
WHERE
ISNULL(T2.ISDELETED,0)=0
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
--here there wont be any filtering as there is no where clause...
Hope this will help...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 25, 2012 at 1:20 pm
CptCrusty1 (5/25/2012)
OK... dba says something else is going on, so I'm going to believe him.. BUT.. he also said there could be an issue with the execution sequence which is what this is... now the part I don't understand...Why are these two returning different results.....
--Table Driven version.
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID
WHERE ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
SELECT T1.ID, T1.CHILD_ID, T2.ID
FROM DBO.table_1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)
ON T1.CHILD_ID = T2.ID AND
ISNULL(T1.ISDELETED,0)=0
AND ISNULL(T2.ISDELETED,0)=0
To help you understand, add all the columns from both tables to both select. Run the first query without the WHERE clause.
May 25, 2012 at 1:23 pm
HOLD ON HOLD ON... I'm about to post my "I'm an IDIOT" reply....
Wait for it.
May 25, 2012 at 1:23 pm
I'M WRONG!!!! OK! I ADMIT IT!!!!! SOMEONE GET A BOWL OF CROW FOR ME TO EAT!!!!
oK.. TONS of research later and I realize my hang up. Oracle Processess from the bottom up... SQL Server uses a different order.... I cracked open my book and found it on page 196. (ISBN 9780735626393)....
I also found an article regarding the query execution order:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
Yes... I'm thick.. but I get it now.....:w00t::w00t::w00t::w00t:
:blush:
Thanks to all that kept kicking me while I was down....LOL... . thanks for the help...Gullimeel, Sean, Lynn...:cool:
May 25, 2012 at 1:30 pm
CptCrusty1 (5/25/2012)
I'M WRONG!!!! OK! I ADMIT IT!!!!! SOMEONE GET A BOWL OF CROW FOR ME TO EAT!!!!oK.. TONS of research later and I realize my hang up. Oracle Processess from the bottom up... SQL Server uses a different order.... I cracked open my book and found it on page 196. (ISBN 9780735626393)....
I also found an article regarding the query execution order:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
Yes... I'm thick.. but I get it now.....:w00t::w00t::w00t::w00t:
:blush:
Thanks to all that kept kicking me while I was down....LOL... . thanks for the help...Gullimeel, Sean, Lynn...:cool:
Okay, you are welcome.
Now we just have to get your sql masters there to also realize that the NOLOCK hint isn't what they think it is either. It introduces potential errors that may not be acceptable. It isn't a magic go faster switch.
May 25, 2012 at 1:39 pm
:ermm: :unsure:
May 25, 2012 at 1:49 pm
You are welcome...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 26, 2012 at 6:49 am
CptCrusty1 (5/25/2012)
I also found an article regarding the query execution order:1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
Please bear in mind that this is the LOGICAL processing order only, and is most useful to understand the binding order. The query optimizer can, and generally will, reorder and/or completely transform logical operations to find a more efficient PHYSICAL execution plan. For example, it is very common for the optimizer to push WHERE clause conditions above a JOIN. This point is very important, please read the following links:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 61 through 67 (of 67 total)
You must be logged in to reply to this topic. Login to reply