Règles générales pour simplifier les instructions SQL

Pour le dire différemment: une requête (complexe) avec JOIN, SUBSELECT, UNION est-elle possible (ou non) de la réduire à une instruction SQL plus simple et équivalente, produisant le même résultat, en utilisant des règles de transformation?

C’est exactement ce que font les optimiseurs pour gagner leur vie (ce n’est pas que je dis qu’ils le font toujours bien).

Étant donné que SQL est un langage basé sur un ensemble, il existe généralement plusieurs façons de transformer une requête en une autre.

Comme cette requête:

 SELECT * FROM mytable WHERE col1 > @value1 OR col2 < @value2 

peut être transformé en ceci:

 SELECT * FROM mytable WHERE col1 > @value1 UNION SELECT * FROM mytable WHERE col2 < @value2 

ou ca:

 SELECT mo.* FROM ( SELECT id FROM mytable WHERE col1 > @value1 UNION SELECT id FROM mytable WHERE col2 < @value2 ) mi JOIN mytable mo ON mo.id = mi.id 

, qui ont l'air plus moche mais peuvent donner de meilleurs plans d'exécution.

L'une des tâches les plus courantes consiste à remplacer cette requête:

 SELECT * FROM mytable WHERE col IN ( SELECT othercol FROM othertable ) 

avec celui-ci:

 SELECT * FROM mytable mo WHERE EXISTS ( SELECT NULL FROM othertable o WHERE o.othercol = mo.col ) 

Dans certains RDBMS (comme PostgreSQL ), DISTINCT et GROUP BY utilisent les différents plans d’exécution, il est donc parfois préférable de remplacer l’un par l’autre:

 SELECT mo.grouper, ( SELECT SUM(col) FROM mytable mi WHERE mi.grouper = mo.grouper ) FROM ( SELECT DISTINCT grouper FROM mytable ) mo 

contre.

 SELECT mo.grouper, SUM(col) FROM mytable GROUP BY mo.grouper 

Dans PostgreSQL , DISTINCT sortinge et hash GROUP BY .

MySQL manque de FULL OUTER JOIN , il peut donc être réécrit comme suit:

 SELECT t1.col1, t2.col2 FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id = t2.id 

contre.

 SELECT t1.col1, t2.col2 FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id UNION ALL SELECT NULL, t2.col2 FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id IS NULL 

, mais consultez cet article sur mon blog pour savoir comment le faire plus efficacement avec MySQL :

• Émuler FULL OUTER JOIN dans MySQL

Cette requête hiérarchique dans Oracle :

 SELECT DISTINCT(animal_id) AS animal_id FROM animal START WITH animal_id = :id CONNECT BY PRIOR animal_id IN (father, mother) ORDER BY animal_id 

peut être transformé en ceci:

 SELECT DISTINCT(animal_id) AS animal_id FROM ( SELECT 0 AS gender, animal_id, father AS parent FROM animal UNION ALL SELECT 1, animal_id, mother FROM animal ) START WITH animal_id = :id CONNECT BY parent = PRIOR animal_id ORDER BY animal_id 

, le dernier étant plus performant.

Voir cet article sur mon blog pour les détails du plan d'exécution:

• Demande de généalogie sur les deux parents

Pour rechercher toutes les plages qui chevauchent la plage donnée, vous pouvez utiliser la requête suivante:

 SELECT * FROM ranges WHERE end_date >= @start AND start_date <= @end 

, mais dans SQL Server cette requête plus complexe permet d'obtenir plus rapidement les mêmes résultats:

 SELECT * FROM ranges WHERE (start_date > @start AND start_date <= @end) OR (@start BETWEEN start_date AND end_date) 

, et croyez-le ou non, j'ai aussi un article sur mon blog à ce sujet:

• Plage de chevauchement: SQL Server

SQL Server ne dispose pas non plus d'un moyen efficace de faire des agrégats cumulatifs. Par conséquent, cette requête:

 SELECT mi.id, SUM(mo.value) AS running_sum FROM mytable mi JOIN mytable mo ON mo.id <= mi.id GROUP BY mi.id 

peut être plus efficacement réécrit en utilisant, Lord aidez-moi, les curseurs (vous m'avez bien entendu: les cursors , more efficiently et SQL Server en une phrase).

Voir cet article sur mon blog pour savoir comment le faire:

• Plates-formes d'aplatissement: SQL Server

Il existe un certain type de requête communément rencontré dans les applications financières qui recherchent le taux effectif pour une devise, comme celle-ci dans Oracle :

 SELECT TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999') FROM t_transaction x JOIN t_rate r ON (rte_currency, rte_date) IN ( SELECT xac_currency, MAX(rte_date) FROM t_rate WHERE rte_currency = xac_currency AND rte_date <= xac_date ) 

Cette requête peut être fortement réécrite pour utiliser une condition d'égalité qui permet un HASH JOIN au lieu de NESTED LOOPS :

 WITH v_rate AS ( SELECT cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate FROM ( SELECT cur_id, dte_date, ( SELECT MAX(rte_date) FROM t_rate ri WHERE rte_currency = cur_id AND rte_date <= dte_date ) AS rte_effdate FROM ( SELECT ( SELECT MAX(rte_date) FROM t_rate ) - level + 1 AS dte_date FROM dual CONNECT BY level <= ( SELECT MAX(rte_date) - MIN(rte_date) FROM t_rate ) ) v_date, ( SELECT 1 AS cur_id FROM dual UNION ALL SELECT 2 AS cur_id FROM dual ) v_currency ) v_eff LEFT JOIN t_rate ON rte_currency = cur_id AND rte_date = rte_effdate ) SELECT TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999') FROM ( SELECT xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt FROM t_transaction x GROUP BY xac_currency, TRUNC(xac_date) ) JOIN v_rate ON eff_currency = xac_currency AND eff_date = xac_date 

En dépit de son encombrement, cette dernière requête est 6 fois plus rapide.

L'idée principale ici est de remplacer <= with = , ce qui nécessite la construction d'une table de calendrier en mémoire. rejoindre avec.

• Conversion de devises

Voici quelques exemples d’utilisation d’Oracle 8 & 9 (bien sûr, parfois, le contraire pourrait simplifier ou accélérer la requête):

Les parenthèses peuvent être supprimées si elles ne sont pas utilisées pour remplacer la priorité des opérateurs. Un exemple simple est celui où tous les opérateurs booléens de votre clause where sont identiques: where ((a or b) or c) est équivalent à where a or b or c .

Une sous-requête peut souvent (sinon toujours) être fusionnée avec la requête principale pour la simplifier. Selon mon expérience, cela améliore souvent considérablement les performances:

 select foo.a, bar.a from foomatic foo, bartastic bar where foo.id = bar.id and bar.id = ( select ban.id from bantabulous ban where ban.bandana = 42 ) ; 

est équivalent à

 select foo.a, bar.a from foomatic foo, bartastic bar, bantabulous ban where foo.id = bar.id and bar.id = ban.id and ban.bandana = 42 ; 

L’utilisation des jointures ANSI sépare une grande partie de la logique “code monkey” des parties vraiment intéressantes de la clause where: La requête précédente est équivalente à

 select foo.a, bar.a from foomatic foo join bartastic bar on bar.id = foo.id join bantabulous ban on ban.id = bar.id where ban.bandana = 42 ; 

Si vous voulez vérifier l’existence d’une ligne, n’utilisez pas count (*) , utilisez plutôt soit rownum = 1 ou placez la requête dans une clause where exists pour extraire une seule ligne au lieu de toutes.

• Je suppose que le plus évident est de chercher des curseurs qui peuvent être remplacés par une opération basée sur SQL ‘Set’.
• Ensuite, dans ma liste, recherchez les sous-requêtes corrélées qui peuvent être réécrites en tant que requête non corrélée.
• Dans les procédures stockées longues, séparez les instructions SQL en leurs propres procédures stockées. De cette façon, ils auront leur propre plan de requête mis en cache.
• Recherchez les transactions dont la scope peut être raccourcie. Je trouve régulièrement des déclarations dans une transaction qui peut être en sécurité en dehors.
• Les sous-sélections peuvent souvent être réécrites sous la forme de jointures simples (les optimiseurs modernes sont efficaces pour identifier les simples)

Comme l’a mentionné @Quassnoi, l’Optimiseur fait souvent du bon travail. Une façon de l’aider consiste à s’assurer que les index et les statistiques sont à jour et que des index appropriés existent pour votre charge de travail de requête.

J’aime remplacer toutes les sortes de sous-sélection par une requête de jointure.

Celui-ci est évident:

 SELECT * FROM mytable mo WHERE EXISTS ( SELECT * FROM othertable o WHERE o.othercol = mo.col ) 

par

 SELECT mo.* FROM mytable mo inner join othertable o on o.othercol = mo.col 

Et celui-ci est sous estimation:

 SELECT * FROM mytable mo WHERE NOT EXISTS ( SELECT * FROM othertable o WHERE o.othercol = mo.col ) 

par

 SELECT mo.* FROM mytable mo left outer join othertable o on o.othercol = mo.col WHERE o.othercol is null 

Cela pourrait aider le SGBD à choisir le bon plan d’exécution dans une grande requête.

J’aime tout le monde dans une équipe à suivre un ensemble de normes pour rendre le code lisible, maintenable, compréhensible, lavable, etc. 🙂

• tout le monde utilise le même alias
• pas de curseurs. pas de boucles
• pourquoi même penser à IN quand vous le pouvez EXISTE
• Indent
• Cohérence dans le style de codage

il y a d’autres choses ici Quelles sont certaines de vos normes de firebase database les plus utiles?

Compte tenu de la nature du SQL, vous devez absolument connaître les implications en termes de performances de tout refactoring. Le refactoring des applications SQL est une bonne ressource en matière de refactorisation, qui met fortement l’accent sur les performances (voir le chapitre 5).

Bien que la simplification ne corresponde pas à l’optimisation, la simplification peut être importante pour l’écriture de code SQL lisible, ce qui est essentiel pour vérifier si votre code SQL est correct (pas l’exactitude syntaxique que votre environnement de développement doit vérifier). Il me semble que dans un monde idéal, nous écririons le code SQL le plus simple et le plus lisible, puis l’optimiseur réécrirait le code SQL sous une forme quelconque (peut-être plus verbeuse) qui serait le plus rapide.

J’ai trouvé que penser aux instructions SQL comme étant basées sur la logique des ensembles est très utile, en particulier si je dois combiner des clauses where ou déterminer une négation complexe d’une clause where. J’utilise les lois de l’algèbre booléenne dans ce cas.

Les plus importantes pour simplifier une clause where sont probablement les lois de DeMorgan (notez que “·” est “AND” et “+” est “OR”):

• NOT (x · y) = NOT x + NOT y
• NOT (x + y) = NOT x · PAS y

Cela se traduit en SQL par:

 NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2 NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2 

Ces lois peuvent être très utiles pour simplifier les clauses where avec beaucoup de parties AND et OR nestedes.

Il est également utile de rappeler que l’instruction field1 IN (value1, value2, ...) est équivalente à field1 = value1 OR field1 = value2 OR ... Cela vous permet de nier l’ IN () de deux manières:

 NOT field1 IN (value1, value2) -- for longer lists NOT field1 = value1 AND NOT field1 = value2 -- for shorter lists 

Une sous-requête peut être envisagée de cette manière également. Par exemple, cette clause where annulée:

 NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2)) 

peut être réécrit comme suit:

 NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2)) 

Ces lois ne vous expliquent pas comment transformer une requête SQL utilisant une sous-requête en une requête utilisant une jointure, mais la logique booléenne peut vous aider à comprendre les types de jointure et la nature de votre requête. Par exemple, avec les tables A et B , un INNER JOIN est comme A AND B , un LEFT OUTER JOIN est comme (A AND NOT B) OR (A AND B) ce qui simplifie à A OR (A AND B) et FULL OUTER JOIN est A OR (A AND B) OR B qui simplifie à A OR B

Mon approche est d’apprendre la théorie relationnelle en général et l’algèbre relationnelle en particulier. Ensuite, apprenez à repérer les constructions utilisées dans SQL pour implémenter des opérateurs à partir de l’algèbre relationnelle (eg quantification universelle ou division) et du calcul (par exemple quantification existentielle). Le gotcha est que SQL a des fonctionnalités que l’on ne trouve pas dans le modèle relationnel, par exemple les null, qui sont probablement mieux refaites de toute façon. Lectures recommandées: Théorie SQL et relationnelle: Comment écrire du code SQL précis par date de CJ .

Dans le même ordre d’idées, je ne suis pas convaincu que «le fait que la plupart des SUBSELECT puissent être réécrits en tant que JOIN» représente une simplification.

Prenez cette requête par exemple:

 SELECT c FROM T1 WHERE c NOT IN ( SELECT c FROM T2 ); 

Réécrire en utilisant JOIN

 SELECT DISTINCT T1.c FROM T1 NATURAL LEFT OUTER JOIN T2 WHERE T2.c IS NULL; 

La jointure est plus verbeuse!

Sinon, reconnaissez que la construction implémente un antijoin sur la projection de c par exemple pseudo algrbra

 T1 { c } antijoin T2 { c } 

Simplification à l’aide d’opérateurs relationnels:

 SELECT c FROM T1 EXCEPT SELECT c FROM T2;