CODEWITHSUNDEEP
sqlt-sqlleft-joinquery-performancesql-server-2017
Furkan Gözükara
Furkan Gözükara

Reputation: 23800

Left join causes huge performance drop. How to fix it

I have 3 separate queries and when I merge them via left join, the performance drop is huge

Any ideas why it might be happening or how to solve this issue?

Here the queries

first query

SELECT MONEY,
       AccountLevel,
       BadgeCount,
       EloWin,
       TournamentOfficBattleTotal,
       MaximumTowerLevel,
       NpcBattleVictoryCount
FROM table_2
WHERE table_2.UserId = 465903

second query

  SELECT COUNT(DISTINCT table_1.PokemonId) AS PokedexOwnage
  FROM table_1 WHERE UserId = 465903

third query

  SELECT COUNT(*) AS PokedexEvent
  FROM table_3 WHERE UserId = 465903

and here my solution to merge all 3 into one

  SELECT MONEY,
         AccountLevel,
         BadgeCount,
         COUNT(DISTINCT table_1.PokemonId) AS PokedexOwnage,
         COUNT(DISTINCT table_3.PokemonId) AS PokedexEvent,
         EloWin,
         TournamentOfficBattleTotal,
         MaximumTowerLevel,
         NpcBattleVictoryCount
  FROM table_2
  LEFT JOIN table_1 ON table_1.UserId = table_2.UserId
  LEFT JOIN table_3 ON table_3.UserId = table_2.UserId 
  WHERE table_2.UserId = 465903
GROUP BY AccountLevel,
         BadgeCount,
         EloWin,
         TournamentOfficBattleTotal,
         MaximumTowerLevel,
         NpcBattleVictoryCount,
         MONEY

When i compare via execution plan, the first 3 queries take 0% relative cost to the last query

enter image description here

enter image description here

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Aggregate before joining. One method is uses correlated subqueries:

SELECT MONEY, AccountLevel, BadgeCount, EloWin,
       TournamentOfficBattleTotal, MaximumTowerLevel, NpcBattleVictoryCount,
       (SELECT COUNT(DISTINCT t1.PokemonId)
        FROM table_1 t1
        WHERE t1.UserId = t2.UserId
       ) as PokedexOwnage,
       (SELECT COUNT(*)
        FROM table_3 t3
        WHERE t1.UserId = t3.UserId
       ) as PokedexEvent
FROM table_2 t2
WHERE t2.UserId = 465903;

This will probably be fine. But for better performance, be sure you have indexes on table_2(UserId, PokemonId) and table_3(UserId).

Upvotes: 2

Related Questions