Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outer Join performance


Outer Join performance

Author
Message
robin.pryor
robin.pryor
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 284
Can someone please give me advise on a better way to write the following?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4

I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.

Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4

I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.

Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.


Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.

Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8554 Visits: 18142
It really looks like a CROSS TABS problem. Read the articles that Sean mentioned and remember that you can use MAX() for strings.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7966 Visits: 9425
If your solution is bad then anything we suggest may not be better, e.g.


SELECT b.BoxId,
MAX(CASE WHEN w.parameter = 1 THEN w.field1 END) AS [value1],
MAX(CASE WHEN w.parameter = 2 THEN w.field1 END) AS [value2],
MAX(CASE WHEN w.parameter = 3 THEN w.field1 END) AS [value3],
MAX(CASE WHEN w.parameter = 4 THEN w.field1 END) AS [value4]
FROM boxes b
LEFT OUTER JOIN widgets w ON w.boxid = b.boxid
GROUP BY b.BoxId



But the performance may be worse


Far away is close at hand in the images of elsewhere.

Anon.


ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
You probably want to do the grouping in the inner query rather than the outer query. Also, can limit parameter values to 1-4 in the inner query itself.


SELECT a.BoxId, b.value1, b.value2, b.value3, b.value4
FROM boxes a
LEFT OUTER JOIN (
SELECT boxid,
MAX(CASE WHEN parameter = 1 THEN field1 END) AS value1,
MAX(CASE WHEN parameter = 2 THEN field1 END) AS value2,
MAX(CASE WHEN parameter = 3 THEN field1 END) AS value3,
MAX(CASE WHEN parameter = 4 THEN field1 END) AS value4
FROM widgets
WHERE
parameter BETWEEN 1 AND 4
GROUP BY boxid
) AS b ON a.boxid = b.boxid




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
rock.liu
rock.liu
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 52
to fix my previous script error

SELECT a.BoxId, MAX(b.field1) as value1 , MAX(c.field1) as value2, MAX(d.field1) as value3, MAX(e.field1) as value4
FROM boxes a LEFT JOIN widgets bb ON a.boxid = bb.boxid AND bb.parameter IN ( 1,2,3,4 )
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 1 ) b
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 2) c
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 3) d
OUTER APPLY( SELECT bb.field1 WHERE bb.parameter = 4) e
GROUP BY a.BoxId


still can't give the same return with the original script ,just to correct my errors .sorry for my fault
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8554 Visits: 18142
rock.liu (4/30/2014)
can you use this one ?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
cross apply (select top 1 b.field1 from widgets b ON a.boxid = b.boxid and b.parameter = 1 ) b
cross apply (select top 1 c.field1 from widgets c ON a.boxid = c.boxid and c.parameter = 2 ) c
cross apply (select top 1 d.field1 from widgets d ON a.boxid = d.boxid and d.parameter = 3 ) d
cross apply (select top 1 e.field1 from widgets e ON a.boxid = b.boxid and e.parameter = 4 ) e

Your code is full of errors and it does not guarantee to return the same results as the original query. You should use outer apply. And the problem remains the same, you're reading widgets table four times.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
robin.pryor
robin.pryor
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 284
Sean Lange (4/28/2014)
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4

I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.

Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.


Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.

Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.


Sorry about the vagueness. I did the best I could. I work for a govt agency and the particulars of this specific thing involve sensitive data and field names that completely give away where I work. Call it paranoia, but my "company" would crap if I put the real structure out there. Do the real field and table names really matter? What I used, while meaningless, could really exist as a table.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
robin.pryor (5/2/2014)
Sean Lange (4/28/2014)
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?

SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4

I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.

Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.


Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.

Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.


Sorry about the vagueness. I did the best I could. I work for a govt agency and the particulars of this specific thing involve sensitive data and field names that completely give away where I work. Call it paranoia, but my "company" would crap if I put the real structure out there. Do the real field and table names really matter? What I used, while meaningless, could really exist as a table.


No of course the real names don't make any difference at all. The problem is that our common language is sql and since we didn't have tables or sample data to work with we have to guess. Notice you have several attempts which may or may not work. Nobody can actually test it because we didn't have tables to work with. While nobody really knows the answer it does seem to be the general consensus that a cross tab is what you want. Did the articles I referenced help?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
robin.pryor
robin.pryor
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 284
Yes. Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search