Éviter les anti-modèles SQL

Les bonnes pratiques suivantes fournissent des conseils pour éviter les antipatrons de requête qui ont un impact sur les performances de BigQuery.

Autojointures

Bonne pratique : évitez les autojointures. Utilisez plutôt un fenêtrage.

En règle générale, les autojointures sont utilisées pour calculer les relations dépendantes des lignes. L'utilisation d'une autojointure peut potentiellement doubler le nombre de lignes de sortie. Cette augmentation des données de sortie peut entraîner de mauvaises performances.

Au lieu d'utiliser une autojointure, utilisez le fenêtrage (analytique) pour réduire le nombre d'octets supplémentaires générés par la requête.

Décalage de données

Bonne pratique : Si votre requête traite des clés particulièrement asymétriques, filtrez vos données le plus tôt possible.

Le décalage de partition, parfois appelé décalage de données, se produit lorsque les données sont partitionnées en segments de taille très inégale. Cela crée un déséquilibre dans la quantité de données envoyées entre les emplacements. Vous ne pouvez pas partager les partitions entre les emplacements, donc si une partition est particulièrement volumineuse, elle peut ralentir ou même planter l'emplacement qui traite la partition surdimensionnée.

Les partitions deviennent volumineuses lorsque votre clé de partition a une valeur qui apparaît plus souvent que toute autre valeur. Par exemple, le regroupement par un champ user_id où il existe de nombreuses entrées pour guest ou NULL.

Lorsque les ressources d'un emplacement sont submergées, une erreur resources exceeded se produit. Si un emplacement atteint la limite de redistribution des données (2 To en mémoire compressée), les redistributions peuvent être réalisées sur le disque, ce qui affecte encore davantage les performances. Les clients disposant d'une tarification à taux fixe peuvent augmenter le nombre d'emplacements attribués.

Si vous examinez le plan d’explication des requêtes et constatez une différence significative entre les temps de calcul moyen et max, vos données sont probablement asymétriques.

Pour éviter les problèmes de performances résultant d'un décalage des données :

  • Utilisez une fonction d'agrégation approximative telle que APPROX_TOP_COUNT pour déterminer si les données sont asymétriques.
  • Filtrez vos données le plus tôt possible.

Jointures déséquilibrées

Un décalage des données peut également apparaître lorsque vous utilisez des clauses JOIN. Comme BigQuery redistribue les données sur chaque partie de la jointure, toutes les données ayant la même clé de jointure se retrouvent dans la même partition. Cette redistribution des données peut surcharger l'emplacement.

Pour éviter les problèmes de performances associés aux jointures déséquilibrées :

  • Pré-filtrez les lignes de la table avec la clé déséquilibrée.
  • Si possible, divisez la requête en deux requêtes.

Jointures croisées (produit cartésien)

Bonne pratique : Évitez les jointures qui génèrent plus de sorties que d'entrées. Lorsqu'une requête CROSS JOIN est requise, pré-agrégez vos données.

Les jointures croisées sont des requêtes pour lesquelles chaque ligne de la première table est jointe aux lignes de la seconde table (des clés non uniques sont présentes des deux côtés). Une sortie est défavorable lorsque le nombre de lignes dans la table de gauche est multiplié par le nombre de lignes dans la table de droite. Dans les cas extrêmes, la requête peut ne pas aboutir.

Si la requête aboutit, l'explication du plan de requête affiche les lignes de sortie par rapport aux lignes d'entrée. Vous avez la possibilité de confirmer un produit cartésien en modifiant la requête pour imprimer le nombre de lignes de chaque côté de la clause JOIN, regroupées par la clé de jointure.

Pour éviter les problèmes de performance associés aux jointures qui génèrent plus de sorties que d’entrées :

  • Utilisez une clause GROUP BY pour pré-agréger les données.
  • Utilisez un fenêtrage. Les fenêtrages s'avèrent souvent plus efficaces que les jointures croisées. Pour en savoir plus, consultez la rubrique sur les fonctions analytiques.

Les instructions LMD qui mettent à jour ou insèrent des lignes simples

Bonne pratique : Évitez les instructions LMD spécifiques à un point (mise à jour ou insertion d'une ligne à la fois). Organisez vos mises à jour et insertions par lots.

L'utilisation d'instructions LMD spécifiques à un point constitue une tentative de traiter BigQuery comme un système de traitement transactionnel en ligne (OLTP). BigQuery se concentre sur le traitement analytique en ligne (OLAP) et utilise des analyses de table et non des recherches de points. Si vous avez besoin d'un comportement de type OLTP (mises à jour ou insertions sur une seule ligne), envisagez une base de données compatible avec les cas d'utilisation OLTP tels que Cloud SQL.

Les instructions LMD de BigQuery sont destinées aux mises à jour groupées. Les instructions LMD UPDATE et DELETE dans BigQuery sont orientées vers des réécritures périodiques de vos données, et non des mutations de ligne unique. L'instruction LMD INSERT doit être utilisée avec modération. Les insertions consomment les mêmes quotas de modification que les tâches de chargement. Si votre cas d'utilisation implique des insertions fréquentes de ligne unique, envisagez plutôt une diffusion en streaming de vos données.

Si le traitement par lots de vos instructions UPDATE génère de nombreux tuples dans des requêtes très longues, vous pouvez vous rapprocher de la limite de longueur de requête de 256 Ko. Pour contourner la limite de longueur de requête, déterminez si vos mises à jour peuvent être gérées sur la base de critères logiques et non d'une série de remplacements directs de tuples.

Par exemple, vous pouvez charger votre ensemble d'enregistrements de remplacement dans une autre table, puis écrire l'instruction LMD pour mettre à jour toutes les valeurs de la table d'origine si les colonnes non mises à jour correspondent. Par exemple, si les données d'origine se trouvent dans la table t et que les mises à jour sont effectuées dans la table u, la requête devrait ressembler à ceci :

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.