March 27, 2014 at 3:35 pm
Hello Data Wizards,
I am looking to simplify some a script with joins. I do understand the logic of a join but I can not visualize the output of data. Here is what I have. I think I can simplify the subquery some more but I am out of ideas. Please help and thank you for taking the time to help a newb.
SELECT W.ID1,
W.ID2
INTO #TempHold -- SELECT COUNT(1)
FROM #TempWork W WITH (NOLOCK)
LEFT JOIN Eventtable S WITH (NOLOCK)
ON S.ID1 = W.ID1
LEFT JOIN StatusStatus L WITH (NOLOCK)
ON L.ID1 = W.ID1
WHERE W.ID2 IN (SELECT P.ID2
FROM Processing P WITH (NOLOCK)
INNER JOIN SpecialEventLoan S WITH (NOLOCK)
ON P.ID1 = S.ID1
INNER JOIN LoanStatus L WITH (NOLOCK)
ON P.ID1 = L.ID1
WHERE S.EventID = 1100
AND L.StatusID = 1102)
THANK YOU SO MUCH!!!
March 27, 2014 at 4:04 pm
The subquery looks fine, you're filtering using JOINs. You could change them to IN or EXISTS and you might have a slight performance gain, but it might not be worth it.
Your LEFT JOINs in the main query might be adding duplicate rows which seem unnecesary, so you could remove them.
The main problem in your query are those NOLOCK hints. This will give you inconsistent information and might generate more problems than they actually solve. Check the following articles:
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
March 27, 2014 at 10:13 pm
yes, left Join in main query is not required, it is doing nothing but may add duplicate rows.
you can write query like below, removing sub query and adding it in main query. Since I don't know about your data, in case you get duplicate records you can simply add distinct clause in the select.
SELECT W.ID1,
W.ID2 INTO #TempHold -- SELECT COUNT(1)
FROM #TempWork W WITH (NOLOCK)
Inner Join Processing P WITH (NOLOCK) on P.ID2=W.ID2
INNER JOIN SpecialEventLoan S WITH (NOLOCK) ON P.ID1 = S.ID1
INNER JOIN LoanStatus L WITH (NOLOCK) ON P.ID1 = L.ID1
WHERE S.EventID = 1100
AND L.StatusID = 1102
Hope this helps you
Avi.
March 31, 2014 at 1:44 pm
Thank you for the help and information. I actually had to do the following:
SELECT Distinct T.ID1,
T.ID2
FROM Processing P WITH (NOLOCK)
INNER JOIN #IDTEMP T
ON P.ID1= T.ID1
LEFT OUTER JOIN Event S WITH (NOLOCK)
ON S.ID2= T.ID2 AND S.EventActionID = 1100
LEFT OUTER JOIN Status L WITH (NOLOCK)
ON L.ID2= T.ID2 AND L.loanstatus = 1102
INNER JOIN Event S2 WITH (NOLOCK)
ON S2.ID2= P.ID2 AND S2.EventID = 1100
INNER JOIN loanstatus L2 WITH (NOLOCK)
ON L2.ID2= P.ID2 AND L2.status = 1102
WHERE S.ID2is null AND l.ID2 is null
March 31, 2014 at 1:44 pm
Thank you for the help but the my last reply was the query that I was looking for.
March 31, 2014 at 1:46 pm
Luis Cazares (3/27/2014)
The subquery looks fine, you're filtering using JOINs. You could change them to IN or EXISTS and you might have a slight performance gain, but it might not be worth it.Your LEFT JOINs in the main query might be adding duplicate rows which seem unnecesary, so you could remove them.
The main problem in your query are those NOLOCK hints. This will give you inconsistent information and might generate more problems than they actually solve. Check the following articles:
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
Thank you! But I found the query that worked and pasted it above.
March 31, 2014 at 2:23 pm
the.roof (3/31/2014)
Luis Cazares (3/27/2014)
The subquery looks fine, you're filtering using JOINs. You could change them to IN or EXISTS and you might have a slight performance gain, but it might not be worth it.Your LEFT JOINs in the main query might be adding duplicate rows which seem unnecesary, so you could remove them.
The main problem in your query are those NOLOCK hints. This will give you inconsistent information and might generate more problems than they actually solve. Check the following articles:
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
Thank you! But I found the query that worked and pasted it above.
I'd like to emphasize my previous post. You don't need the LEFT JOINs nor the NOLOCK hints.
Left joins will only return possible duplicates (which you're eliminating afterwards with DISTINCT)
NOLOCK hints will give you inconsistent results which will become wrong data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply