SQL libéré: 17 façons d'accélérer vos requêtes SQL

Les développeurs SQL sur toutes les plates-formes ont du mal, apparemment coincés dans une DO WHILEboucle qui leur fait répéter les mêmes erreurs encore et encore. C'est parce que le champ de la base de données est encore relativement immature. Bien sûr, les fournisseurs font des progrès, mais ils continuent de s'attaquer aux problèmes les plus graves. La concurrence, la gestion des ressources, la gestion de l'espace et la vitesse pèsent toujours sur les développeurs SQL, qu'ils codent sur SQL Server, Oracle, DB2, Sybase, MySQL ou toute autre plate-forme relationnelle.

Une partie du problème est qu'il n'y a pas de solution miracle, et pour presque toutes les meilleures pratiques, je peux vous montrer au moins une exception. En règle générale, un développeur trouve ses propres méthodes préférées - bien qu'elles n'incluent généralement aucune construction pour les performances ou la concurrence - et ne prend pas la peine d'explorer d'autres options. C'est peut-être un symptôme d'un manque d'éducation, ou les développeurs sont tout simplement trop proches du processus pour reconnaître lorsqu'ils font quelque chose de mal. Peut-être que la requête fonctionne bien sur un ensemble local de données de test mais échoue lamentablement sur le système de production.

Je ne m'attends pas à ce que les développeurs SQL deviennent administrateurs, mais ils doivent prendre en compte les problèmes de production lors de l'écriture de leur code. S'ils ne le font pas pendant le développement initial, les DBA les feront simplement revenir en arrière et le faire plus tard - et les utilisateurs en souffriront entre-temps.

Il y a une raison pour laquelle nous disons que le réglage d'une base de données est à la fois un art et une science. C'est parce qu'il existe très peu de règles strictes qui s'appliquent à tous les niveaux. Les problèmes que vous avez résolus sur un système ne sont pas des problèmes sur un autre, et vice versa. Il n'y a pas de bonne réponse en ce qui concerne le réglage des requêtes, mais cela ne signifie pas que vous devriez abandonner.

Vous pouvez suivre quelques bons principes qui devraient donner des résultats dans une combinaison ou une autre. Je les ai encapsulés dans une liste de choses à faire et à ne pas faire en SQL qui sont souvent négligées ou difficiles à repérer. Ces techniques devraient vous donner un peu plus de perspicacité dans l'esprit de vos DBA, ainsi que la possibilité de commencer à penser aux processus d'une manière orientée vers la production.

1. Ne pas utiliser à la UPDATEplace deCASE

Ce problème est très courant, et bien qu'il ne soit pas difficile à repérer, de nombreux développeurs l'ignorent souvent car l'utilisation UPDATEa un flux naturel qui semble logique.

Prenez ce scénario, par exemple: vous insérez des données dans une table temporaire et vous en avez besoin pour afficher une certaine valeur si une autre valeur existe. Peut-être que vous tirez du tableau des clients et que vous voulez que toute personne dont les commandes dépassent 100 000 USD soit étiquetée "Préféré". Ainsi, vous insérez les données dans le tableau et exécutez une UPDATEinstruction pour définir la colonne CustomerRank sur «Preferred» pour toute personne ayant plus de 100 000 $ de commandes. Le problème est que l' UPDATEinstruction est enregistrée, ce qui signifie qu'elle doit écrire deux fois pour chaque écriture dans la table. Le moyen de contourner cela, bien sûr, consiste à utiliser une CASEinstruction en ligne dans la requête SQL elle-même. Cela teste chaque ligne pour la condition de montant de la commande et définit le libellé «Préféré» avant qu'il ne soit écrit dans la table. L'augmentation des performances peut être stupéfiante.

2. Ne réutilisez pas aveuglément le code

Ce problème est également très courant. Il est très facile de copier le code de quelqu'un d'autre car vous savez qu'il extrait les données dont vous avez besoin. Le problème est que très souvent, il extrait beaucoup plus de données que ce dont vous avez besoin, et les développeurs prennent rarement la peine de les réduire, de sorte qu'ils se retrouvent avec un énorme surensemble de données. Cela se présente généralement sous la forme d'une jointure externe supplémentaire ou d'une condition supplémentaire dans la WHEREclause. Vous pouvez obtenir d'énormes gains de performances si vous adaptez le code réutilisé à vos besoins exacts.

3. Ne tirez que le nombre de colonnes dont vous avez besoin

Ce problème est similaire au numéro 2, mais il est spécifique aux colonnes. Il est trop facile de coder toutes vos requêtes avec SELECT *au lieu de lister les colonnes individuellement. Le problème encore est qu'il extrait plus de données que vous n'en avez besoin. J'ai vu cette erreur des dizaines et des dizaines de fois. Un développeur effectue une SELECT *requête sur une table avec 120 colonnes et des millions de lignes, mais finit par n'en utiliser que trois à cinq. À ce stade, vous traitez tellement plus de données que vous n'en avez besoin, c'est une merveille que la requête renvoie. Non seulement vous traitez plus de données que ce dont vous avez besoin, mais vous retirez également des ressources d'autres processus.

4. Ne plongez pas deux fois

En voici une autre que j'ai vue plus de fois que je n'aurais dû: Une procédure stockée est écrite pour extraire des données d'une table avec des centaines de millions de lignes. Le développeur a besoin de clients qui vivent en Californie et ont des revenus de plus de 40 000 $. Il interroge donc les clients qui vivent en Californie et met les résultats dans une table temporaire; puis il interroge les clients dont les revenus sont supérieurs à 40 000 $ et met ces résultats dans une autre table temporaire. Enfin, il rejoint les deux tables pour obtenir le produit final.

Vous plaisantez j'espère? Cela devrait être fait en une seule requête; au lieu de cela, vous doublez une table très grande. Ne soyez pas un crétin: n'interrogez qu'une seule fois les tables volumineuses chaque fois que cela est possible.

Un scénario légèrement différent se produit lorsqu'un sous-ensemble d'une grande table est requis par plusieurs étapes dans un processus, ce qui provoque l'interrogation de la grande table à chaque fois. Évitez cela en interrogeant le sous-ensemble et en le conservant ailleurs, puis en pointant les étapes suivantes vers votre ensemble de données plus petit.

6. Faire des données de pré-étape

C'est l'un de mes sujets préférés car c'est une vieille technique souvent négligée. Si vous avez un rapport ou une procédure (ou mieux encore, un ensemble d'entre eux) qui effectuera des jointures similaires à des tables volumineuses, il peut être avantageux pour vous de pré-préparer les données en joignant les tables à l'avance et en les conservant. dans une table. Désormais, les rapports peuvent s'exécuter sur cette table préétablie et éviter la jointure volumineuse.

Vous n'êtes pas toujours en mesure d'utiliser cette technique, mais lorsque vous le pouvez, vous constaterez que c'est un excellent moyen d'économiser les ressources du serveur.

Notez que de nombreux développeurs contournent ce problème de jointure en se concentrant sur la requête elle-même et en créant une vue uniquement autour de la jointure afin de ne pas avoir à taper les conditions de jointure encore et encore. Mais le problème avec cette approche est que la requête s'exécute toujours pour chaque rapport qui en a besoin. En pré-staging les données, vous exécutez la jointure une seule fois (par exemple, 10 minutes avant les rapports) et tout le monde évite la grande jointure. Je ne peux pas vous dire à quel point j'aime cette technique; dans la plupart des environnements, il existe des tables populaires qui sont jointes tout le temps, il n'y a donc aucune raison pour qu'elles ne puissent pas être préétablies.

7. Supprimez et mettez à jour par lots

Voici une autre technique simple qui est souvent négligée. La suppression ou la mise à jour de grandes quantités de données à partir d'énormes tables peut être un cauchemar si vous ne le faites pas correctement. Le problème est que ces deux instructions s'exécutent comme une seule transaction, et si vous devez les supprimer ou si quelque chose arrive au système pendant qu'elles fonctionnent, le système doit annuler la transaction entière. Cela peut prendre très longtemps. Ces opérations peuvent également bloquer d'autres transactions pendant leur durée, goulotant essentiellement le système.

La solution consiste à effectuer des suppressions ou des mises à jour par lots plus petits. Cela résout votre problème de plusieurs manières. Premièrement, si la transaction est supprimée pour une raison quelconque, elle n'a qu'un petit nombre de lignes à restaurer, de sorte que la base de données revient en ligne beaucoup plus rapidement. Deuxièmement, alors que les lots plus petits s'engagent sur le disque, d'autres peuvent se faufiler et faire du travail, de sorte que la concurrence est grandement améliorée.

Dans le même esprit, de nombreux développeurs pensent que ces opérations de suppression et de mise à jour doivent être effectuées le même jour. Ce n'est pas toujours vrai, surtout si vous archivez. Vous pouvez étendre cette opération aussi longtemps que vous en avez besoin, et les lots plus petits y contribuent. Si vous pouvez prendre plus de temps pour effectuer ces opérations intensives, passez du temps supplémentaire et n'interrompez pas votre système.

8. Utilisez des tables temporaires pour améliorer les performances du curseur

J'espère que nous savons tous à présent qu'il est préférable de rester à l'écart des curseurs si possible. Les curseurs ne souffrent pas seulement de problèmes de vitesse, ce qui en soi peut être un problème avec de nombreuses opérations, mais ils peuvent également amener votre opération à bloquer d'autres opérations pendant beaucoup plus longtemps que nécessaire. Cela réduit considérablement la concurrence dans votre système.

Cependant, vous ne pouvez pas toujours éviter d'utiliser des curseurs, et lorsque ces moments se produisent, vous pourrez peut-être vous éloigner des problèmes de performances induits par le curseur en effectuant les opérations de curseur sur une table temporaire à la place. Prenez, par exemple, un curseur qui parcourt une table et met à jour quelques colonnes en fonction de certains résultats de comparaison. Au lieu de faire la comparaison avec la table en direct, vous pourrez peut-être placer ces données dans une table temporaire et faire la comparaison avec cela à la place. Ensuite, vous avez une seule UPDATEinstruction contre la table en direct qui est beaucoup plus petite et ne maintient les verrous que pendant une courte période.

Le sniping de vos modifications de données comme celui-ci peut considérablement augmenter la concurrence. Je terminerai en disant que vous n'avez presque jamais besoin d'utiliser un curseur. Il existe presque toujours une solution basée sur des ensembles; vous devez apprendre à le voir.

9. Ne pas imbriquer les vues

Les vues peuvent être pratiques, mais vous devez être prudent lorsque vous les utilisez. Bien que les vues puissent aider à masquer les requêtes volumineuses des utilisateurs et à standardiser l'accès aux données, vous pouvez facilement vous retrouver dans une situation où vous avez des vues qui appellent des vues qui appellent des vues qui appellent des vues. Cela s'appelle des vues d'imbrication et peut entraîner de graves problèmes de performances, en particulier de deux manières:

  • Tout d'abord, vous aurez très probablement beaucoup plus de données que vous n'en avez besoin.
  • Deuxièmement, l'optimiseur de requêtes abandonnera et renverra un mauvais plan de requête.

Une fois, j'ai eu un client qui aimait les vues imbriquées. Le client avait une vue qu'il utilisait pour presque tout, car il avait deux jointures importantes. Le problème était que la vue renvoyait une colonne contenant des documents de 2 Mo. Certains des documents étaient encore plus volumineux. Le client poussait au moins 2 Mo supplémentaires sur le réseau pour chaque ligne de presque chaque requête exécutée. Naturellement, les performances des requêtes étaient épouvantables.

Et aucune des requêtes n'a utilisé cette colonne! Bien sûr, la colonne était enterrée à sept vues de profondeur, donc même la trouver était difficile. Lorsque j'ai supprimé la colonne de document de la vue, la durée de la plus grosse requête est passée de 2,5 heures à 10 minutes. Quand j'ai finalement démêlé les vues imbriquées, qui avaient plusieurs jointures et colonnes inutiles, et écrit une requête simple, le temps pour cette même requête est tombé à quelques sous-secondes.