Self-join several times on one table

Question

Self-join several times on one table

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
August 26, 2008 at 7:05 am

#71785

I have a view that uses a self-join several times on the same table. It runs very well up to the first three joins, but when 4 and 5 are added, it takes exponentially longer to run (several minutes as opposed to a couple of seconds.) Here is the SQL and any help/suggestions would be greatly appreciated!
Thanks,
Bob
SELECT DISTINCT TOP 100 PERCENT
Mngr1.sales_rep_id,
Mngr1.unit_id,
Mngr1.sales_rep_number,
Mngr1.sales_rep_name,
Mngr1.sales_rep_name_control,
Mngr1.sales_rep_manager AS mngr1_name,
Mngr2.sales_rep_manager AS mngr2_name,
Mngr3.sales_rep_manager AS mngr3_name,
Mngr4.sales_rep_manager AS mngr4_name,
Mngr5.sales_rep_manager AS mngr5_name
FROM dbo.tblSalesReps Mngr1 LEFT OUTER JOIN
dbo.tblSalesReps Mngr2 ON Mngr1.sales_rep_manager = Mngr2.sales_rep_name_control LEFT OUTER JOIN
dbo.tblSalesReps Mngr3 ON Mngr2.sales_rep_manager = Mngr3.sales_rep_name_control LEFT OUTER JOIN
dbo.tblSalesReps Mngr4 ON Mngr3.sales_rep_manager = Mngr4.sales_rep_name_control LEFT OUTER JOIN
dbo.tblSalesReps Mngr5 ON Mngr4.sales_rep_manager = Mngr5.sales_rep_name_control
ORDER BY Mngr1.sales_rep_id

Viewing 4 posts - 1 through 3 (of 3 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Each left join is creating more and more work. Not sure what you can do here since it looks like the structure doesn't lend itself to easy reporting.

What's the indexing like?

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 2

We found the (an) answer... maybe this will help someone else along the way...

We had "n/a" in several fields on the table. I think that maybe the query was doing a lot more self-joins than was needed. Anyway, here is what we changed it to (and the SRS report now runs in less than 10 seconds where it was taking (literally!) 5-7 minutes.

SELECT DISTINCT

TOP 100 PERCENT

Mngr1.sales_rep_id,

Mngr1.unit_id,

Mngr1.sales_rep_number,

Mngr1.sales_rep_name,

Mngr1.sales_rep_name_control,

Mngr1.sales_rep_manager AS mngr1_name,

Mngr2.sales_rep_manager AS mngr2_name,

Mngr3.sales_rep_manager AS mngr3_name,

Mngr4.sales_rep_manager AS mngr4_name,

Mngr5.sales_rep_manager AS mngr5_name

FROM dbo.tblSalesReps AS Mngr1 LEFT OUTER JOIN

dbo.tblSalesReps AS Mngr2 ON Mngr1.sales_rep_manager = Mngr2.sales_rep_name_control AND Mngr1.sales_rep_manager <> 'N/A' LEFT OUTER JOIN

dbo.tblSalesReps AS Mngr3 ON Mngr2.sales_rep_manager = Mngr3.sales_rep_name_control AND Mngr2.sales_rep_manager <> 'N/A' LEFT OUTER JOIN

dbo.tblSalesReps AS Mngr4 ON Mngr3.sales_rep_manager = Mngr4.sales_rep_name_control AND Mngr3.sales_rep_manager <> 'N/A' LEFT OUTER JOIN

dbo.tblSalesReps AS Mngr5 ON Mngr4.sales_rep_manager = Mngr5.sales_rep_name_control AND Mngr4.sales_rep_manager <> 'N/A'

ORDER BY Mngr1.sales_rep_id

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Hi Bob,

I'm happy that u checked for 'N/A' .

but first check for

Mngr1.sales_rep_manager <> 'N/A'

then check for

Mngr1.sales_rep_manager = Mngr2.sales_rep_name_control

bcoz it will take less time for checking N/A than self join and if the first one is not solved it is not going to check the second. So it can move to the next record.

Please check and tell me if you find good performance result on your query

Regards,

Viji