#004N+1 Query in Subquery Loop
sql.hardSQL
1CREATE PROCEDURE GetUserStats()
2BEGIN
3 DECLARE done INT DEFAULT FALSE;
4 DECLARE uid INT;
5 DECLARE cur CURSOR FOR SELECT id FROM users;
6
7 OPEN cur;
8 read_loop: LOOP
9 FETCH cur INTO uid;
10 IF done THEN LEAVE read_loop; END IF;
11
12 SELECT uid,
13 (SELECT COUNT(*) FROM orders WHERE user_id = uid) AS cnt,
14 (SELECT SUM(total) FROM orders WHERE user_id = uid) AS rev;
15 END LOOP;
16 CLOSE cur;
17END;
no lines flagged
#004PracticeHard30 min · 200 XP
N+1 Query in Subquery Loop
This stored procedure runs a subquery inside a loop for each user, causing severe performance degradation at scale.
Flagged lines
No lines flagged yet
What's wrong?
Flag a line or write a note to submit.