Multiple Table join - Time consuming-CodePudding

I'm trying to excute the below query in MS SQL sever and it 's taking huge time For just top 10 rows it's taking more time and without top 10 command it took time upto an hour still didn't gave me result. Basically, what i'm trying to do here is to get count of medals. I've tables as below mentioned Countries - 2 Columns - NOC and Region One table for each type of medal with other columns and NOC is common key.

SELECT 
top 10 
C.NOC,C.Region,Count(Gold.Medal) as TotalGM, 
Count(Silver.Medal) as TotalSM,Count(Bronze.Medal) as TotalBM 
FROM Countries C
LEFT JOIN Gold ON C.NOC= Gold.NOC
LEFT JOIN Silver ON C.NOC= Silver.NOC
LEFT JOIN Bronze ON C.NOC=Bronze.NOC
GROUP BY C.NOC,C.Region

What could've gone wrong here or is there any other method to do it? How it can be optimized. Kindly help me here.

CodePudding user response：

Despite your protestations you are getting cartesian joins within each NOC.

If an NOC has 1,173 gold medals, 953 silver medals and 833 bronze medals then your current query would generate 931,184,877 rows for just that NOC and then collapse them down and generate an incorrect count.

I'd probably write the query as follows.

WITH Medals(Class, NOC) As
(
SELECT 'G', NOC
FROM Gold
UNION ALL
SELECT 'S', NOC
FROM Silver
UNION ALL
SELECT 'B', NOC
FROM Bronze
), AggMedals AS
(
SELECT 
    NOC,
    SUM(CASE WHEN Class = 'G' THEN 1 ELSE 0 END) as TotalGM,
    SUM(CASE WHEN Class = 'S' THEN 1 ELSE 0 END) as TotalSM,
    SUM(CASE WHEN Class = 'B' THEN 1 ELSE 0 END) as TotalBM
FROM Medals
GROUP BY NOC
)
SELECT *
FROM Countries C
LEFT JOIN AggMedals AM ON AM.NOC = C.NOC

you could also do individual group by NOC on all three medal tables (in a CTE or with derived tables) and then left join your country table onto those results.

CodePudding user response：

I got the required output finally by using below query.

SELECT C.NOC,
C.Region,
G.Total_GM,
S.Total_SM,
B.Total_BM from Countries C
LEFT JOIN (SELECT NOC,Count(Medal) as Total_GM from Gold
           Group by NOC) G
ON C.NOC = G.NOC
LEFT JOIN (Select NOC,Count(Medal) as Total_SM from Silver
           Group by NOC) S
ON C.NOC = S.NOC
LEFT JOIN (SELECT NOC,Count(Medal) as Total_BM from Bronze
           Group by NOC) B
ON C.NOC = B.NOC
Order by Total_GM desc, Total_SM desc, Total_BM desc;```