Réglage des performances de la base de données SQL pour les développeurs

Le réglage des performances SQL peut être une tâche extrêmement difficile, en particulier lorsque vous travaillez avec des données à grande échelle où même le changement le plus mineur peut avoir un impact considérable (positif ou négatif) sur les performances.

Dans les moyennes et grandes entreprises, la plupart des réglages de performances SQL seront gérés par un administrateur de base de données (DBA). Mais croyez-moi, il existe de nombreux développeurs qui doivent effectuer des tâches de type DBA. De plus, dans de nombreuses entreprises que j'ai vues qui ont des DBA, ils ont souvent du mal à bien travailler avec les développeurs - les postes nécessitent simplement différents modes de résolution de problèmes, ce qui peut entraîner des désaccords entre collègues.

En plus de cela, la structure de l'entreprise peut également jouer un rôle. Supposons que l'équipe DBA soit placée au 10e étage avec toutes leurs bases de données, tandis que les développeurs se trouvent au 15e étage, ou même dans un bâtiment différent sous une structure de rapport complètement séparée - il est certainement difficile de travailler ensemble sans heurts dans ces conditions.

Dans cet article, j'aimerais accomplir deux choses :

1. Fournissez aux développeurs des techniques de réglage des performances SQL côté développeur.
2. Expliquez comment les développeurs et les DBA peuvent travailler ensemble efficacement.

Réglage des performances SQL (dans la base de code) : index

Si vous êtes un nouveau venu dans les bases de données et que vous vous demandez même « Qu'est-ce que le réglage des performances SQL ? », vous devez savoir que l'indexation est un moyen efficace de régler votre base de données SQL qui est souvent négligée lors du développement. En termes simples, un index est une structure de données qui améliore la vitesse des opérations de récupération de données sur une table de base de données en fournissant des recherches aléatoires rapides et un accès efficace aux enregistrements ordonnés. Cela signifie qu'une fois que vous avez créé un index, vous pouvez sélectionner ou trier vos lignes plus rapidement qu'auparavant.

Les index sont également utilisés pour définir une clé primaire ou un index unique qui garantira qu'aucune autre colonne n'a les mêmes valeurs. Bien sûr, l'indexation des bases de données est un sujet vaste et intéressant auquel je ne peux pas rendre justice avec cette brève description (mais voici une description plus détaillée).

Si vous débutez avec les index, je vous recommande d'utiliser ce diagramme lors de la structuration de vos requêtes :

Fondamentalement, l'objectif est d'indexer les principales colonnes de recherche et de classement.

Notez que si vos tables sont constamment martelées par INSERT , UPDATE et DELETE , vous devez être prudent lors de l'indexation - vous pourriez finir par diminuer les performances car tous les index doivent être modifiés après ces opérations.

De plus, les administrateurs de base de données suppriment souvent leurs index SQL avant d'effectuer des insertions par lots de plus d'un million de lignes pour accélérer le processus d'insertion. Une fois le lot inséré, ils recréent ensuite les index. N'oubliez pas, cependant, que la suppression d'index affectera toutes les requêtes exécutées dans cette table ; cette approche n'est donc recommandée que lorsque vous travaillez avec une seule grande insertion.

Réglage SQL : Plans d'exécution dans SQL Server

Au fait : l'outil Execution Plan de SQL Server peut être utile pour créer des index.

Sa fonction principale est d'afficher graphiquement les méthodes de récupération de données choisies par l'optimiseur de requête SQL Server. Si vous ne les avez jamais vus auparavant, il y a une procédure pas à pas détaillée.

Pour récupérer le plan d'exécution (dans SQL Server Management Studio), cliquez simplement sur "Inclure le plan d'exécution réel" (CTRL + M) avant d'exécuter votre requête.

Ensuite, un troisième onglet nommé "Plan d'exécution" apparaîtra. Vous pouvez voir un index manquant détecté. Pour le créer, faites un clic droit dans le plan d'exécution et choisissez "Détails de l'index manquant…". C'est aussi simple que ça!

( Cliquez pour zoomer )

SQL Tuning : évitez les boucles de codage

Imaginez un scénario dans lequel 1000 requêtes martèlent votre base de données en séquence. Quelque chose comme:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Vous devriez éviter de telles boucles dans votre code. Par exemple, nous pourrions transformer l'extrait ci-dessus en utilisant une INSERT ou UPDATE unique avec plusieurs lignes et valeurs :

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Assurez-vous que votre clause WHERE évite de mettre à jour la valeur stockée si elle correspond à la valeur existante. Une telle optimisation triviale peut augmenter considérablement les performances des requêtes SQL en ne mettant à jour que des centaines de lignes au lieu de milliers. Par exemple:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Réglage SQL : évitez les sous-requêtes SQL corrélées

Une sous-requête corrélée est une requête qui utilise les valeurs de la requête parent. Ce type de requête SQL a tendance à s'exécuter ligne par ligne, une fois pour chaque ligne renvoyée par la requête externe, et diminue ainsi les performances de la requête SQL. Les nouveaux développeurs SQL sont souvent surpris à structurer leurs requêtes de cette manière, car c'est généralement la voie la plus facile.

Voici un exemple de sous-requête corrélée :

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

En particulier, le problème est que la requête interne ( SELECT CompanyName… ) est exécutée pour chaque ligne renvoyée par la requête externe ( SELECT c.Name… ). Mais pourquoi passer en revue la Company encore et encore pour chaque ligne traitée par la requête externe ?

Une technique de réglage des performances SQL plus efficace consisterait à refactoriser la sous-requête corrélée en tant que jointure :

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

Dans ce cas, nous parcourons la table Company une seule fois, au début, et la REJOIGNONS avec la table Customer . Dès lors, nous pouvons sélectionner les valeurs dont nous avons besoin ( co.CompanyName ) plus efficacement.

SQL Tuning : sélectionner avec parcimonie

L'un de mes conseils d'optimisation SQL préférés est d'éviter SELECT * ! Au lieu de cela, vous devez inclure individuellement les colonnes spécifiques dont vous avez besoin. Encore une fois, cela semble simple, mais je vois cette erreur partout. Considérez une table avec des centaines de colonnes et des millions de lignes : si votre application n'a vraiment besoin que de quelques colonnes, il est inutile d'interroger toutes les données. C'est un énorme gaspillage de ressources. ( Pour plus de problèmes, voir ici. )

Par exemple:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Si vous avez vraiment besoin de chaque colonne, répertoriez explicitement chaque colonne. Ce n'est pas tant une règle, mais plutôt un moyen d'éviter de futures erreurs système et un réglage supplémentaire des performances SQL. Par exemple, si vous utilisez un INSERT... SELECT... et que la table source a changé via l'ajout d'une nouvelle colonne, vous pouvez rencontrer des problèmes, même si cette colonne n'est pas nécessaire à la table de destination, par exemple:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Pour éviter ce genre d'erreur de SQL Server, vous devez déclarer chaque colonne individuellement :

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Notez cependant qu'il existe certaines situations où l'utilisation de SELECT * peut être appropriée. Par exemple, avec les tables temporaires, ce qui nous amène à notre sujet suivant.

SQL Tuning : l'utilisation judicieuse des tables temporaires (#Temp)

Les tables temporaires augmentent généralement la complexité d'une requête. Si votre code peut être écrit de manière simple et directe, je vous suggère d'éviter les tables temporaires.

Mais si vous avez une procédure stockée avec une manipulation de données qui ne peut pas être gérée avec une seule requête, vous pouvez utiliser des tables temporaires comme intermédiaires pour vous aider à générer un résultat final.

Lorsque vous devez joindre une grande table et qu'il existe des conditions sur ladite table, vous pouvez augmenter les performances de la base de données en transférant vos données dans une table temporaire, puis en effectuant une jointure sur celle- ci. Votre table temporaire aura moins de lignes que la (grande) table d'origine, donc la jointure se terminera plus rapidement !

La décision n'est pas toujours simple, mais cet exemple vous donnera une idée des situations dans lesquelles vous pourriez vouloir utiliser des tables temporaires :

Imaginez une table de clients avec des millions d'enregistrements. Vous devez faire une jointure sur une région spécifique. Vous pouvez y parvenir en utilisant une SELECT INTO , puis en vous joignant à la table temporaire :

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Remarque : certains développeurs SQL évitent également d'utiliser SELECT INTO pour créer des tables temporaires, indiquant que cette commande verrouille la base de données tempdb, interdisant aux autres utilisateurs de créer des tables temporaires. Heureusement, cela est corrigé dans 7.0 et versions ultérieures .)

Comme alternative aux tables temporaires, vous pouvez envisager d'utiliser une sous-requête en tant que table :

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Mais attendez! Il y a un problème avec cette seconde requête. Comme décrit ci-dessus, nous ne devrions inclure que les colonnes dont nous avons besoin dans notre sous-requête (c'est-à-dire, ne pas utiliser SELECT * ). En tenant compte de cela :

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Tous ces extraits SQL renverront les mêmes données. Mais avec les tables temporaires, nous pourrions, par exemple, créer un index dans la table temporaire pour améliorer les performances. Il y a une bonne discussion ici sur les différences entre les tables temporaires et les sous-requêtes.

Enfin, lorsque vous avez terminé avec votre table temporaire, supprimez-la pour effacer les ressources tempdb, plutôt que d'attendre qu'elle soit automatiquement supprimée (comme ce sera le cas lorsque votre connexion à la base de données sera terminée) :

 DROP TABLE #temp

Réglage SQL : "Mon enregistrement existe-t-il ?"

Cette technique d'optimisation SQL concerne l'utilisation de EXISTS() . Si vous voulez vérifier si un enregistrement existe, utilisez EXISTS() au lieu de COUNT() . Alors que COUNT() parcourt la table entière, en comptant toutes les entrées correspondant à votre condition, EXISTS() se fermera dès qu'il verra le résultat dont il a besoin. Cela vous donnera de meilleures performances et un code plus clair.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Réglage des performances SQL avec SQL Server 2016

Comme les administrateurs de bases de données travaillant avec SQL Server 2016 le savent probablement, la version a marqué un changement important dans la gestion des valeurs par défaut et de la compatibilité. En tant que version majeure, elle est bien sûr livrée avec de nouvelles optimisations de requêtes, mais le contrôle de leur utilisation est désormais rationalisé via sys.databases.compatibility_level .

Réglage des performances SQL (au bureau)

Les administrateurs de bases de données SQL (DBA) et les développeurs s'affrontent souvent sur des problèmes liés aux données et non liés aux données. Tirés de mon expérience, voici quelques conseils (pour les deux parties) sur la façon de s'entendre et de travailler ensemble efficacement.

Tweeter

Optimisation de la base de données pour les développeurs :

1. Si votre application cesse de fonctionner soudainement, il se peut qu'il ne s'agisse pas d'un problème de base de données. Par exemple, vous avez peut-être un problème de réseau. Enquêtez un peu avant d'accuser un DBA !

2. Même si vous êtes un ninja modélisateur de données SQL, demandez à un DBA de vous aider avec votre diagramme relationnel. Ils ont beaucoup à partager et à offrir.

3. Les DBA n'aiment pas les changements rapides. C'est naturel : ils doivent analyser la base de données dans son ensemble et examiner l'impact de tout changement sous tous les angles. Un simple changement dans une colonne peut prendre une semaine pour être mis en œuvre, mais c'est parce qu'une erreur peut se matérialiser par des pertes énormes pour l'entreprise. Sois patient!

4. Ne demandez pas aux administrateurs de base de données SQL d'apporter des modifications aux données dans un environnement de production. Si vous souhaitez accéder à la base de données de production, vous devez être responsable de tous vos propres changements.

Optimisation de la base de données pour les administrateurs de base de données SQL Server :

1. Si vous n'aimez pas que les gens vous posent des questions sur la base de données, donnez-leur un panneau d'état en temps réel. Les développeurs se méfient toujours du statut d'une base de données, et un tel panneau pourrait faire gagner du temps et de l'énergie à tout le monde.

2. Aider les développeurs dans un environnement de test/assurance qualité. Facilitez la simulation d'un serveur de production avec des tests simples sur des données réelles. Ce sera un gain de temps important pour les autres ainsi que pour vous-même.

3. Les développeurs passent toute la journée sur des systèmes avec une logique métier fréquemment modifiée. Essayez de comprendre que ce monde est plus flexible et soyez capable d'enfreindre certaines règles à un moment critique.

4. Les bases de données SQL évoluent. Le jour viendra où vous devrez migrer vos données vers une nouvelle version. Les développeurs comptent sur de nouvelles fonctionnalités importantes à chaque nouvelle version. Au lieu de refuser d'accepter leurs changements, planifiez à l'avance et soyez prêt pour la migration.