Éliminer le travail répété et optimiser les sous-requêtes — Exemples pratiques

Les données utilisées dans cet article — perf_sales et employee

Dans l'article précédent tu as étudié la réécriture de IN / EXISTS et des sous-requêtes corrélées.

Cet article s'appuie là-dessus avec des réécritures qui évitent de calculer la même agrégation deux fois (replier les agrégations répétées en un seul résultat intermédiaire) et matérialiser les tables dérivées (construire une sous-requête dans une clause FROM une fois et la tenir temporairement) — pratiqué à travers des exemples concrets.

Avant de te lancer dans les exercices, regarde les définitions de colonnes et les données d'exemple des deux tables que cet article utilise — perf_sales et employee.

① Utilise PRAGMA table_info(perf_sales); et PRAGMA table_info(employee); pour vérifier les définitions de colonnes des deux tables.

② Utilise SELECT * FROM perf_sales LIMIT 5; et SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes. perf_sales a 50 000 lignes, donc attache toujours LIMIT lors d'une prévisualisation.

N'écris pas la même agrégation deux fois — replie la duplication en un seul résultat intermédiaire

Quand tu veux demander « le total des ventes de chaque région est-il supérieur au total moyen sur toutes les régions ? », écrire le total et la moyenne comme deux sous-requêtes séparées scanne perf_sales deux fois.

Si tu replies la même agrégation dans un CTE et la calcules une fois, puis réutilises le résultat, tu ne scannes qu'une fois.

Le motif de réécriture est : repère le cas où « la même agrégation GROUP BY apparaît à la fois dans la requête principale et dans une sous-requête », hisse cette agrégation dans un seul CTE, et fais que la requête principale référence le CTE à la place.

Le résultat est identique, et le scan en double disparaît du plan.

-- Avant : total et « moyenne sur les régions » agrégés séparément (perf_sales scanné deux fois)
SELECT region, SUM(amount) AS total
FROM perf_sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(t) FROM (
    SELECT SUM(amount) AS t FROM perf_sales GROUP BY region
  )
);

-- Après : agréger par région une fois dans un CTE
WITH region_total AS (
  SELECT region, SUM(amount) AS total
  FROM perf_sales
  GROUP BY region
)
SELECT region, total
FROM region_total
WHERE total > (SELECT AVG(total) FROM region_total)
ORDER BY region;

Exécute la question « quelles régions ont un total de ventes au-dessus du total moyen sur toutes les régions ? » sous deux formes — la version à agrégation dupliquée et la version CTE — et compare les plans.

① Préfixe la requête avec EXPLAIN QUERY PLAN et affiche le plan de la version qui écrit l'agrégation GROUP BY region à la fois dans la requête principale et à l'intérieur de la sous-requête HAVING.

② Replie la même agrégation de région dans un seul CTE, prends la moyenne depuis ce CTE avec AVG, et ordonne les régions au-dessus avec ORDER BY region. Affiche le plan de cette version aussi avec EXPLAIN QUERY PLAN.

③ Confirme que SCAN perf_sales apparaît plusieurs fois dans la version avant et tombe à une seule fois dans la version après.

Effondrer les sous-requêtes corrélées en une agrégation en un seul coup

Quand une condition comme « le total des ventes de chaque région est-il supérieur au total maximum par employé dans cette région ? » est calculée comme une sous-requête corrélée par ligne externe de région, la requête interne est ré-évaluée une fois pour chaque région.

Cela peut aussi être replié en une agrégation unique clé par région, puis jointe en retour.

Le motif de réécriture est : prends un (SELECT agrégat ... WHERE child.key = parent.key) appelé par ligne externe et transforme-le en « construis une table d'agrégat GROUP BY key une fois, puis joins en retour à la parente ».

Les ré-évaluations par région sont remplacées par une seule agrégation plus une jointure.

-- Avant : corréler par région pour trouver le « total max par employé »
SELECT region,
  (SELECT MAX(s) FROM (
     SELECT SUM(amount) AS s FROM perf_sales p2
     WHERE p2.region = p1.region GROUP BY emp_id
  )) AS max_emp_total
FROM perf_sales p1
GROUP BY region;

-- Après : agréger une fois par (region, emp_id), puis prendre le MAX par région
WITH emp_region AS (
  SELECT region, emp_id, SUM(amount) AS s
  FROM perf_sales
  GROUP BY region, emp_id
)
SELECT region, MAX(s) AS max_emp_total
FROM emp_region
GROUP BY region
ORDER BY region;

Exécute « le total maximum de ventes par employé dans chaque région » sous deux formes — la version corrélée et la version agrégation en un seul coup — et compare les plans.

① Préfixe la requête avec EXPLAIN QUERY PLAN et affiche le plan de la version qui utilise une sous-requête corrélée pour calculer le « total max par employé à l'intérieur de cette région » par ligne externe de région.

② Construis un CTE qui fait GROUP BY sur `(region, emp_id)` exactement une fois, puis GROUP BY region sur ce CTE pour prendre le max. Affiche le plan de cette version aussi avec EXPLAIN QUERY PLAN.

③ Confirme que le mot CORRELATED dans le plan corrélé disparaît dans le plan réécrit.

Matérialiser les tables dérivées — construire la sous-requête de clause FROM une fois

Une table dérivée — une sous-requête écrite dans une clause FROM — référencée en deux endroits peut parfois être recalculée deux fois par l'optimiseur.

Si tu la construis une fois et la tiens temporairement (la matérialise), les références ne la reconstruisent pas à chaque fois.

En SQLite, référencer un CTE plusieurs fois fait apparaître EXPLAIN QUERY PLAN une ligne MATERIALIZE — confirmant que le CTE est matérialisé exactement une fois.

Dans le dernier exercice de cet article tu prendras une table dérivée lourde qui agrège perf_sales par emp_id et est référencée deux fois, la transformeras en CTE matérialisé une fois, et confirmeras que MATERIALIZE apparaît dans le plan et que le scan ne se produit qu'une fois.

-- Avant : la même table dérivée référencée dans deux clauses FROM
SELECT a.emp_id, a.tot, b.avg_tot
FROM (SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id) a
CROSS JOIN (
  SELECT AVG(tot) AS avg_tot FROM (
    SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id
  )
) b
ORDER BY a.emp_id;

-- Après : table dérivée en CTE référencé deux fois (matérialisé une fois)
WITH emp_total AS (
  SELECT emp_id, SUM(amount) AS tot
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.tot, (SELECT AVG(tot) FROM emp_total) AS avg_tot
FROM emp_total e
ORDER BY e.emp_id;

Exécute « le total des ventes de chaque employé à côté de la moyenne globale sur les employés » sous deux formes — la version qui référence la table dérivée deux fois et la version CTE — et compare les plans. C'est le dernier exercice de l'article.

① Préfixe la requête avec EXPLAIN QUERY PLAN et affiche le plan de la version qui référence la table dérivée agrégée par emp_id en deux endroits dans la clause FROM.

② Replie la même agrégation dans un seul CTE, prends la moyenne du même CTE via (SELECT AVG(tot) FROM même_CTE), et ordonne par emp_id. Affiche le plan de cette version aussi avec EXPLAIN QUERY PLAN.

③ Confirme que MATERIALIZE apparaît dans le plan réécrit et que SCAN perf_sales tombe à une seule fois.