Conseils pour accélérer les requêtes SQL afin d'empêcher le code de la base de données de verrouiller la base de données

Le monde des bases de données étant encore relativement immature, les développeurs SQL sur toutes les plateformes ont du mal, commettant encore et encore les mêmes erreurs. Bien entendu, les fournisseurs de bases de données font des progrès et continuent de se débattre avec des problèmes plus importants.

Que les développeurs SQL écrivent du code sur SQL Server, Oracle, DB2, Sybase, MySQL ou toute autre plate-forme de base de données relationnelle, la concurrence, la gestion des ressources, la gestion de l'espace et la vitesse d'exécution les tourmentent toujours.

Un aspect du problème est qu’il n’existe pas de solution miracle ; je peux signaler au moins une exception à presque toutes les meilleures pratiques.

Nous disons que le réglage d'une base de données est à la fois un art et une science, ce qui est logique car il existe peu de règles strictes qui s'appliquent à tous les niveaux. Un problème que vous résolvez sur un système n’est pas un problème sur un autre, et vice versa.

Quand il s'agit de régler les requêtes, il n'y a pas de bonne réponse, mais cela ne veut pas dire que vous devez abandonner. Vous pouvez suivre certains des principes suivants et, espérons-le, obtenir d’excellents résultats.

N'utilisez pas UPDATE à la place de CASE

Ce problème est très courant, mais difficile à détecter. De nombreux développeurs ignorent souvent ce problème car l'utilisation de UPDATE est naturelle et semble logique.

Prenons ce scénario comme exemple : vous insérez des données dans une table temporaire, et si une autre valeur existe, vous en avez besoin pour afficher une certaine valeur.

Peut-être extrayez-vous des enregistrements de la table Clients et souhaitez-vous marquer les clients avec des commandes supérieures à 100 000 $ comme "Préférés".

Vous insérez donc des données dans la table, exécutez une instruction UPDATE et définissez la colonne CustomerRank sur « Préféré » pour tout client dont le montant de la commande est supérieur à 100 000 $.

Le problème est que l'instruction UPDATE est enregistrée, ce qui signifie qu'à chaque fois qu'elle est écrite dans la table, elle est écrite deux fois.

Solution : utilisez une instruction CASE en ligne dans la requête SQL, qui vérifie la condition du montant de la commande de chaque ligne et définit la balise "Préféré" avant de l'écrire dans la table. Cela améliore étonnamment les performances de traitement.

Ne réutilisez pas aveuglément le code

Ce problème est également très courant, il nous est facile de copier du code écrit par d'autres car vous savez qu'il obtiendra les données dont vous avez besoin.

Le problème est que cela nécessite souvent trop de données dont vous n'avez pas besoin, et que les développeurs les rationalisent rarement, vous vous retrouvez donc avec beaucoup de données.

Cela se manifeste généralement par une jointure externe supplémentaire ou une condition supplémentaire dans la clause WHERE. Si vous rationalisez le code réutilisé selon vos besoins précis, vous pouvez améliorer considérablement les performances.

Extraire autant de colonnes que nécessaire

Cette question est similaire à la question 2, mais elle est propre aux colonnes. Il est facile de coder toutes les requêtes à l'aide de SELECT* au lieu de lister les colonnes une par une.

Le même problème est qu'il extrait trop de données dont vous n'avez pas besoin. J'ai vu cette erreur d'innombrables fois. Un développeur effectue une requête SELECT* sur une table de 120 colonnes comportant des millions de lignes, mais n'utilise que trois ou cinq colonnes.

Vous traitez donc beaucoup plus de données que ce dont vous avez réellement besoin, et c'est un miracle que la requête renvoie des résultats. Non seulement vous traitez trop de données dont vous n'avez pas besoin, mais vous enlevez également des ressources à d'autres processus.

N'interrogez pas deux fois (double-dip)

C'est une autre erreur que je vois beaucoup de gens faire : écrire une procédure stockée pour extraire des données d'une table contenant des centaines de millions de lignes .

Le développeur souhaitait extraire des informations sur les clients qui vivaient en Californie et gagnaient plus de 40 000 $ par an. Il interroge donc les clients qui vivent en Californie et place les résultats de la requête dans une table temporaire.

Interrogez ensuite les clients dont le revenu annuel est supérieur à 40 000 $ et placez ces résultats dans un autre tableau temporaire. Finalement il rejoint les deux tableaux pour obtenir le résultat final.

Vous plaisantez ? Cela doit être fait avec une seule requête, au lieu de cela, vous interrogez deux fois une très grande table. Ne soyez pas stupide : essayez d'interroger une grande table une seule fois et vous constaterez que la procédure stockée s'exécute beaucoup plus rapidement.

Un scénario légèrement différent est celui où plusieurs étapes d'un processus nécessitent un sous-ensemble d'une grande table, ce qui entraîne l'interrogation de la grande table à chaque fois.

Pour éviter ce problème, interrogez simplement ce sous-ensemble, conservez-le ailleurs, puis dirigez les étapes suivantes vers cet ensemble de données plus petit.

Savoir quand utiliser des tables temporaires

Ce problème est un peu plus compliqué à résoudre, mais l'effet est significatif. Les tables temporaires peuvent être utilisées dans de nombreuses situations, par exemple pour éviter qu'une grande table soit interrogée deux fois. Les tables temporaires peuvent également être utilisées pour réduire considérablement la puissance de traitement requise pour joindre de grandes tables.

Si vous devez joindre une table à une grande table et qu'il y a des conditions sur la grande table, extrayez simplement la partie requise des données de la grande table dans une table temporaire, puis joignez-la à la table temporaire, ce qui peut améliorer les performances des requêtes.

Ceci est également utile s'il existe plusieurs requêtes dans la procédure stockée qui doivent effectuer des jointures similaires à la même table.

Données pré-stage

C'est l'un de mes sujets préférés à aborder car c'est une méthode ancienne qui est souvent négligée.

Si vous disposez d'un rapport ou d'une procédure stockée (ou d'un groupe de) qui effectue des opérations de jointure similaires sur de grandes tables, préparez les données en joignant les tables à l'avance et en les conservant dans une table. d'une grande aide pour vous.

Les rapports peuvent désormais être exécutés sur cette table préalable, évitant ainsi les jointures volumineuses. Vous ne pourrez pas toujours utiliser cette méthode, mais une fois que vous l'aurez fait, vous constaterez que c'est un excellent moyen d'économiser les ressources du serveur.

Remarque : de nombreux développeurs contournent ce problème de jointure en se concentrant sur la requête elle-même, en créant une vue en lecture seule basée sur la jointure, afin de ne pas avoir à saisir les critères de jointure encore et encore.

Mais le problème avec cette approche est que vous devez toujours exécuter la requête pour chaque rapport qui le nécessite. Si vous préparez les données, vous ne devez exécuter la connexion qu'une seule fois (disons 10 minutes avant le rapport) et d'autres peuvent éviter la connexion volumineuse.

Vous ne savez pas à quel point j'aime cette astuce. Dans la plupart des environnements, certaines tables couramment utilisées sont toujours connectées, il n'y a donc aucune raison pour qu'elles ne puissent pas être pré-organisées en premier.

Suppression et mise à jour par lots

Il s'agit d'une autre astuce souvent négligée : supprimer ou mettre à jour de grandes quantités de données d'une grande table peut être un cauchemar si vous ne le faites pas correctement.

Le problème est que les deux instructions sont exécutées comme une seule transaction. Si vous devez y mettre fin ou si le système rencontre un problème lors de leur exécution, le système doit annuler l'intégralité de la transaction, ce qui prend beaucoup de temps.

Ces opérations bloqueront également d'autres transactions pendant la durée, ce qui crée en fait un goulot d'étranglement au système. La solution consiste à supprimer ou à mettre à jour par petits lots.

Cela résout le problème de plusieurs manières :

Quelle que soit la raison pour laquelle la transaction est terminée, il n'y a qu'un petit nombre de lignes qui doivent être annulées, donc la base de données revient en ligne beaucoup plus rapide.

Lorsque des transactions en petits lots sont validées sur le disque, d'autres transactions peuvent intervenir pour gérer certains travaux, améliorant ainsi considérablement la simultanéité.

De même, de nombreux développeurs se sont obstinés à croire que ces opérations de suppression et de mise à jour doivent être effectuées le même jour. Ce n'est pas toujours le cas, surtout si vous archivez.

Si vous avez besoin de prolonger l'opération, vous pouvez le faire. De petits lots vous aident ; si vous prenez plus de temps pour effectuer ces opérations intensives, ne ralentissez pas le système.

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

Il est préférable d'éviter les curseurs si possible. Les curseurs ont non seulement des problèmes de vitesse, qui sont eux-mêmes un gros problème pour de nombreuses opérations, mais ils peuvent également amener vos opérations à bloquer d'autres opérations pendant une longue période, ce qui réduit considérablement la simultanéité du système.

Cependant, l'utilisation de curseurs ne peut pas toujours être évitée. Lorsque l'utilisation de curseurs ne peut être évitée, vous pouvez à la place effectuer des opérations de curseur sur les tables temporaires pour vous débarrasser des problèmes de performances causés par les curseurs.

A titre d'exemple, pensez à consulter un tableau et à mettre à jour les curseurs de plusieurs colonnes en fonction de certains résultats de comparaison. Vous pourrez peut-être placer ces données dans une table temporaire et les comparer à la table temporaire au lieu de la table active.

Vous pouvez ensuite exécuter une seule instruction UPDATE sur une table active beaucoup plus petite et verrouillée plus courtement.

Effectuer de telles modifications de données peut grandement améliorer la simultanéité. Je terminerai en disant que vous n'avez pas du tout besoin d'utiliser des curseurs, il existe toujours une solution basée sur la collection.

Utiliser des fonctions table

C'est l'une de mes techniques préférées de tous les temps car c'est le genre de secret que seuls les experts connaissent.

Lors de l'utilisation d'une fonction scalaire dans la liste SELECT d'une requête, la fonction est appelée pour chaque ligne du jeu de résultats, ce qui peut réduire considérablement les performances des requêtes volumineuses.

Cependant, vous pouvez convertir la fonction scalaire en fonction table, puis utiliser CROSS APPLY dans la requête, ce qui peut considérablement améliorer les performances. Cette merveilleuse astuce peut améliorer considérablement les performances.

N'effectuez pas d'opérations volumineuses sur plusieurs tables dans le même lot

Cela peut sembler évident, mais ce n'est pas le cas. Je vais utiliser un autre exemple frappant car il illustre mieux le point.

J'ai un système avec beaucoup de blocages et de nombreuses opérations sont bloquées. Il a été constaté qu'une routine de suppression exécutée plusieurs fois par jour supprimait les données de 14 tables dans le cadre de transactions explicites. Traiter les 14 tables en une seule transaction signifie verrouiller chaque table jusqu'à ce que toutes les suppressions soient terminées.

La solution est de décomposer la suppression de chaque table en transactions distinctes afin que chaque transaction de suppression ne verrouille qu'une seule table.

Cela libère d'autres tables, facilitant le blocage et permettant à d'autres opérations de continuer à s'exécuter. Vous devez toujours diviser les transactions volumineuses comme celle-ci en transactions plus petites distinctes pour éviter tout blocage.

N'utilisez pas de déclencheurs

Celui-ci est essentiellement le même que le précédent, mais il mérite d'être mentionné. Le problème avec les déclencheurs : tout ce que vous voulez que le déclencheur fasse, il le fera dans la même transaction que l'opération d'origine.

Si vous écrivez un déclencheur qui insère des données dans une autre table lors de la mise à jour d'une ligne de la table Commandes, les deux tables seront verrouillées jusqu'à ce que le déclencheur soit terminé.

Si vous devez insérer des données dans une autre table après la mise à jour, placez la mise à jour et insérez-la dans une procédure stockée et exécutez-la dans des transactions distinctes.

Si vous devez revenir en arrière, il est facile de revenir en arrière sans avoir à verrouiller les deux tables en même temps. Comme toujours, veillez à ce que les transactions soient courtes et ne verrouillez pas plusieurs ressources à la fois.

Ne faites pas de cluster sur les GUID

Après toutes ces années, je n'arrive pas à croire que nous soyons toujours aux prises avec ce problème. Mais je rencontre toujours des GUID en cluster au moins deux fois par an.

Un GUID (Globally Unique Identifier) ​​​​​​est un numéro de 16 octets généré aléatoirement. Le tri des données de votre tableau en fonction de cette colonne entraîne une fragmentation du tableau beaucoup plus rapide que l'utilisation d'une valeur en constante augmentation telle que DATE ou IDENTITY.

Il y a quelques années, j'ai fait un benchmark dans lequel j'ai inséré un tas de données dans une table avec un GUID clusterisé et les mêmes données dans une autre table avec une colonne IDENTITY.

La table GUID était si fragmentée qu'après seulement 15 minutes, les performances ont chuté de plusieurs milliers de points de pourcentage.

Après 5 heures, les performances de la table IDENTITY n'ont baissé que de quelques points de pourcentage, et cela ne s'applique pas uniquement aux GUID, cela s'applique à n'importe quelle colonne volatile.

Si vous souhaitez simplement vérifier si les données existent, ne comptez pas les lignes

Cette situation est très courante. Vous devez vérifier si les données existent dans le tableau en fonction du résultat. de ce contrôle, vous devez effectuer certaines opérations.

Je vois souvent des gens exécuter SELECT COUNT(*) FROMdbo.T1 pour vérifier si les données existent :

1. SET @CT=(SELECT COUNT(*) FROM

2. dbo.T1);

3. If@CT>0

4. BEGIN

6. FIN

Ceci est complètement inutile si vous souhaitez vérifier si les données existe, faites simplement ceci :

1. Si EXISTE (SELECT 1 FROM dbo.T1)

2. BEGIN

4. FIN

Ne comptez pas tout dans le tableau, récupérez simplement la première ligne que vous trouvez . SQL Server est suffisamment intelligent pour utiliser EXISTS correctement, et le deuxième morceau de code renvoie les résultats très rapidement.

Plus le tableau est grand, plus l'écart sur cet aspect est évident. Faites ce qu'il faut avant que vos données ne deviennent trop volumineuses. Il n'est jamais trop tôt pour optimiser votre base de données.

En fait, je viens d'exécuter cet exemple sur l'une de mes bases de données de production, sur une table de 270 millions de lignes.

La première requête a pris 15 secondes et contenait 456 197 lectures logiques. La deuxième requête a renvoyé des résultats en moins d'une seconde et ne contenait que 5 lectures logiques.

Cependant, si vous avez vraiment besoin de compter les lignes d'une table et que la table est grande, une autre façon est d'extraire des tables système,

SELECT rows fromsysindexes vous obtiendra toutes les lignes indexées nombre.

Et puisque l'index clusterisé représente les données elles-mêmes, ajoutez simplement WHERE indid = 1 pour obtenir les lignes de la table, puis incluez simplement le nom de la table.

Donc, la requête finale est :

1.SELECT les lignes des sysindex où object_name(id)='T1'et indexid =1

Dans ma version 2.7 Dans une table de 100 millions de lignes, les résultats sont renvoyés en moins d'une seconde et il n'y a que 6 lectures logiques. Les performances sont désormais différentes.

Ne pas effectuer de recherche inversée

Prenons comme exemple la requête simple SELECT * FROMCustomers WHERE RegionID Vous ne pouvez pas utiliser d'index avec cette requête car il s'agit d'une recherche inversée qui nécessite une comparaison ligne par ligne à l'aide d'une analyse de table. Si vous devez effectuer une telle tâche, vous constaterez peut-être que les performances sont bien meilleures si vous réécrivez la requête pour utiliser un index.

La requête est facilement réécrite, comme ceci :

1.SELECT * FROM Customers WHERE RegionID

Cette requête utilisera un index, donc si votre ensemble de données est volumineux, ses performances seront bien meilleures que la version d'analyse de table.

Bien sûr, rien n'est aussi simple et peut-être que les performances sont pires, alors essayez-le avant de l'utiliser. Cela fonctionne à 100%, même si de nombreux facteurs sont impliqués.

Finalement, j'ai réalisé que cette requête enfreignait la règle numéro 4 : ne pas interroger deux fois, mais cela montre aussi qu'il n'y a pas de règles strictes. Bien que nous interrogeions deux fois ici, nous le faisons pour éviter des analyses de table coûteuses.

Vous ne pourrez pas utiliser tous ces conseils tout le temps, mais si vous les gardez à l’esprit, vous les utiliserez un jour pour résoudre de gros problèmes.

La chose la plus importante à retenir est de ne pas prendre ce que je dis comme un dogme. Essayez-le dans votre propre environnement. Les mêmes solutions ne fonctionneront pas dans tous les cas, mais c'est ce que j'utilise tout le temps pour résoudre les problèmes de performances médiocres et elles fonctionnent toujours.