Optimise multiple joins between two tables

Question

Optimise multiple joins between two tables

chris.king

SSCrazy

Points: 2007
More actions
September 28, 2010 at 4:06 am

#387884

Hi folks,
I am currently building a query that involves multiple inner joins between two tables.
Table A contains seven foreign key fields that reference the primary key of Table B.
Table B contains data that I need to return in my result set.
As it stands, my SQL will have to look something like..
SELECT join1.Field1,
join2.Field1,
join3.Field1,
join4.Field1,
join5.Field1,
join6.Field1,
join7.Field1
FROM tableA
INNER JOIN tableB as join1
ON tableA.FK1 = join1.PK
INNER JOIN tableB as join2
ON tableA.FK2 = join2.PK
INNER JOIN tableB as join3
ON tableA.FK3 = join3.PK
INNER JOIN tableB as join4
ON tableA.FK4 = join4.PK
INNER JOIN tableB as join5
ON tableA.FK5 = join5.PK
INNER JOIN tableB as join6
ON tableA.FK6 = join6.PK
INNER JOIN tableB as join7
ON tableA.FK7 = join7.PK
Whilst I am confident that this will work, I was wondering if there is a more efficient way in which I could get the values I need from Table B without having to join to it seven times?
TIA,
Chris

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

varunfilim Mr or Mrs. 500 Points: 594 More actions · Answer 2

Hi chris.king..

I think there is no another way.Joining tables will not decrease the Performance.

Regards

varun R

www.sqlinfo.in

chris.king SSCrazy Points: 2007 More actions · Answer 3

Chris Morris-439714 (9/28/2010)
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.

The fields in Table A all reference the same field in Table B, the primary key.

Does that make a difference.

I'm really struggling to see how I can do this without seven seperate joins.

TIA,

Chris

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 4

Hi Chris,

If your table A only has the FK1-FK7 columns and Table b has the PKey and definition, than that is the only way.If not, please post your table structures.

Apart from a stored proc which might run a bit faster because of the caching. Your table structure is normalized and you need a denormalized view.

cheers,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

chris.king (9/28/2010)
Chris Morris-439714 (9/28/2010)
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times with seven different aliases. Looks like your Table B is a catch-all lookup.
The fields in Table A all reference the same field in Table B, the primary key.
Does that make a difference.
I'm really struggling to see how I can do this without seven seperate joins.
TIA,
Chris

You can use trickery to get around it, but to be honest Chris, this looks correct. I don't think you are doing anything wrong (based upon what you've posted so far). You might want to check that you do in fact need inner joins between your main table and your lookup.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

chris.king SSCrazy Points: 2007 More actions · Answer 6

Thanks guys.

I guess I'll have to live with the seven joins.

The only other option would be to de-normalise the data from Table B into Table A.

Chris

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

chris.king (9/28/2010)
Thanks guys.
I guess I'll have to live with the seven joins.
The only other option would be to de-normalise the data from Table B into Table A.
Chris

If the data from Table B is small, needed regularly, and rarely modified, this isn't necessarily a bad idea. Just make sure your update scripts touch both locations.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA