Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Outer Join performance Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 1:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:54 PM
Points: 52, Visits: 145
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.
Post #1565689
Posted Monday, April 28, 2014 3:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,450, Visits: 12,312
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)
Post #1565718
Posted Monday, April 28, 2014 3:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 3,644, Visits: 7,947
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565721
Posted Tuesday, April 29, 2014 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
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.

Post #1565855
Posted Tuesday, April 29, 2014 12:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 2,086, Visits: 3,133
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)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1566089
Posted Wednesday, April 30, 2014 3:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 1:58 AM
Points: 3, Visits: 46
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
Post #1566272
Posted Wednesday, April 30, 2014 9:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 3,644, Visits: 7,947
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566447
Posted Friday, May 2, 2014 10:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:54 PM
Points: 52, Visits: 145
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.
Post #1567122
Posted Friday, May 2, 2014 11:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,450, Visits: 12,312
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)
Post #1567130
Posted Friday, May 2, 2014 11:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:54 PM
Points: 52, Visits: 145
Yes. Thank you
Post #1567132
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse