Les Equi-Joins comptent-ils toujours ?

De temps en temps, je tombe sur des affirmations sur le fonctionnement des bases de données et la façon dont elles devraient être interrogées. Et j’aime lire ces recommandations. Surtout si elles s’accompagnent d’une explication théorique. Mais j’aime encore plus lire ces commentaires lorsqu’ils sont accompagnés de tests qui prouvent ce que la théorie prédit. Le meilleur cas est toujours quand les scripts sont publiés et reproductibles.

Mais il arrive souvent que de telles affirmations sur les performances soient faites de manière générale sans aucune preuve : il est toujours vrai que le pattern x est plus performant que y. Et cela peut tenir si vous avez plus de connaissances sur le contenu de vos données que la base de données pourrait en avoir. Ou s’il y a une explication mathématique pour un tel comportement.

À mon avis, dans de nombreux cas concernant l’indexation ou les stratégies de join, les déclarations devraient être limitées à des technologies et versions de bases de données. De même, les déclarations sans tests reproductibles ne devraient pas être prises pour acquises mais testées.

Si je teste quelque chose sur SQL Server 2008 et le compare à SQL Server 2019, j’obtiendrai généralement des comportements complètement différents puisque SQL Server a massivement évolué. Si je compare Postgres avec Snowflake et Exasol, j’obtiens des comportements différents puisque ces derniers ont été créés avec les requêtes analytiques en tête.

C’est la raison pour laquelle je veux remettre en question l’affirmation :

« Les Equi-Joins (inner joins) sont toujours meilleurs que les left joins »

Pourquoi cela compte

Si le conseil d’utiliser toujours un certain pattern est suivi sans test, cela peut conduire à de mauvaises performances. Comme nous sommes dans le business de l’automatisation avec notre outil Datavault Builder, choisir le mauvais pattern est multiplié des centaines voire des milliers de fois.

Pourquoi les left joins peuvent être meilleurs

Premièrement : dans certaines conditions, les inner joins sont meilleurs. Par exemple, ils permettent à l’optimiseur de requête de commencer les requêtes à l’une ou l’autre extrémité d’une chaîne de join, et si cela permet de limiter massivement le résultat intermédiaire, cela aidera beaucoup. Il y a beaucoup d’autres scénarios.

Mais cela ne tient pas toujours :

Imaginez 2 scénarios :

• vous avez 10 tables. Une a 15 millions d’enregistrements et vous avez 9 autres tables avec 45 millions d’enregistrements. Tous les links partent de votre table de 15m directement vers les tables environnantes. Tous les 15m enregistrements ont une entrée correspondante dans les 9 autres tables.

• vous avez 10 tables. Une a 30 millions d’enregistrements et vous avez 9 autres tables avec 45 millions d’enregistrements. Tous les links partent de votre table de 30m directement vers les tables environnantes. 15m enregistrements ont une entrée correspondante dans les 9 autres tables. 15m n’en ont pas.

C’est le scénario que j’ai exécuté sur Snowflake le 26 août 2021 (c’est pertinent car Snowflake s’améliore constamment).

Mon hypothèse était : les left joins performent mieux pour ce type de requête. Pourquoi ? J’ai fait les mêmes tests pour SQL Server (2017) et Oracle (12c) il y a 2 ans et conclu que les LEFT JOINS performent mieux dans ce scénario.

La théorie ici : au moins dans le second cas, la table 30m peut être filtrée avant d’être jointe, donc cela devrait mieux performer. Pour le cas où tout correspond, je m’attendrais à la même performance ou peut-être légèrement pire (quelques pourcents).

Nous verrons que mes attentes correspondent à mes mesures. En cas d’utilisation d’un left join, je peux aussi stocker moins de données, car je peux utiliser une valeur NULL pour indiquer qu’il n’y a pas d’enregistrement correspondant.

Tests

J’ai désactivé les caches de résultat. Mais je n’ai pas vidé tous les caches de données après chaque requête. Tous les tests ont été exécutés au moins 3 fois. Les résultats étaient acceptés si la déviation entre les exécutions était inférieure à 10%.

Résultats

J’ai fait 2 types de tests : utiliser 2 colonnes pour joindre entre la table 15m/30m et les 9 autres, ou seulement 1 colonne (en utilisant une surrogate key pour combiner les deux premiers champs).

Dans tous les cas avec mon volume de données sur Snowflake aujourd’hui, la version avec left joins a performé à peu près de la même façon OU mieux.

Pour être juste : l’INNER JOIN sur une colonne scanne environ 20-25% moins de données (puisqu’il n’a pas besoin de filtrer). Mais comme c’est un scan local, ça ne fait pas mal et accélère la partie JOIN.

Aussi, le join sur 1 colonne est, comme attendu, beaucoup plus efficace que le join sur 2 colonnes.

Résumé

Dans certains cas, les INNER JOINs ont une meilleure performance que les left joins. Mais pas dans tous. J’ai montré avec des tests reproductibles que dans certaines conditions, les LEFT JOINs ne performent pas moins bien, voire bien mieux que les INNER JOINs.

Je ne veux pas faire une affirmation universelle sur mon scénario. Je dis simplement : sur certaines bases de données dans certaines versions pour certaines requêtes, les LEFT JOINs pourraient être la meilleure option.

Aussi, nous avons vu que sur Snowflake, le join utilisant 1 colonne est bien meilleur que celui utilisant 2 colonnes.

Testez vous-même

Comme il est possible que j’ai raté quelque chose. Ai-je mal défini certaines clés ? Ai-je vidé les caches de la mauvaise façon ou écrit mon SQL de manière non optimale ? N’hésitez pas à reproduire mes résultats vous-même.

Le script de test peut être téléchargé ici :

AUTOMATED_PIT_TEST