La partie la plus intéressante de mon travail concerne le réglage et l'optimisation des performances dans T-SQL. Le cœur du réglage des performances sur un serveur SQL est une indexation correcte et utilisable sur les tables via l’utilisation de requêtes Sargable.
Parfois, le développeur de bases de données principal au travail me dit d’ajouter un index à une colonne xyz car il est utilisé dans plusieurs clauses Where dans plusieurs requêtes SQL. C'est à ce moment-là que je dois me calmer. Si l'ajout d'un index sur la colonne xyz pouvait résoudre tous les problèmes de performances, des milliers de livres n'auraient pas été publiés sur l'optimisation des performances T-SQL, et j'aurais été à la recherche de l'anaconda dans la forêt amazonienne.
Sargable Queries (Search Argumentable)
En termes simples, les requêtes Sargable sont celles qui peuvent utiliser des index créés sur elles pour accélérer les recherches et l'exécution d'une requête.
Une recherche plus rapide signifie qu’un index efficace recherche un grand nombre de lignes et évite des analyses d’index coûteuses.
Recherche d'index - les requêtes peuvent utiliser efficacement les index et localiser les lignes avec moins d'effort de la part de l'optimiseur de requêtes.
Analyse d'index - scanner le tableau entier pour localiser les lignes afin de répondre aux critères de recherche
Qu'est-ce qui rend une requête non modifiable (impossible d'utiliser efficacement les index créés)?
1. utilisation de fonctions dans les conditions de la clause Where (car une fonction est évaluée par rapport à chaque ligne, ce qui oblige l'optimiseur de requête à ne pas utiliser l'index)
2. en utilisant LIKE '% Proposition%' dans les requêtes de recherche générique
3. effectuer un calcul arithmétique sur une colonne d'index dans une clause Where
Créons et peuplons une table avec 0,1 million de lignes pour voir comment effectuer des requêtes Sargable.
Ce script prendrait du temps pour créer des exemples de données basés sur votre configuration matérielle (3-5 minutes).
- Créer une table avec la clé primaire CREATE TABLE EmployeeTest (id INT IDENTITY (1, 1) PRIMARY KEY, Salary INT, DateOfBirth DATETIME, EmployeeName VARCHAR (80)); GO - Insérer des lignes avec des valeurs aléatoires DECLARE @row INT; DECLARE @string VARCHAR (80), @length INT, @code INT; SET @row = 0; PENDANT @row <100000 BEGIN SET @row = @row + 1; IF @row = 10000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 20000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 30000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 40000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 50000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 60000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 70000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 80000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 90000 PRINT 'Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); IF @row = 100000 PRINT 'Done, Lignes insérées:' + CONVERT (VARCHAR (20), @ rangée); - Construisez la chaîne aléatoire SET @length = ROUND (80 * RAND (), 0); SET @string = "; WHILE @length> 0 BEGIN SET @length = @length - 1; SET @code = ROUND (32 * RAND (), 0) - 6; SI @code compris entre 1 et 26 SET @string = @ chaîne + CHAR (ASCII ('a') + @ code-1); ELSE SET @string = @string + "; END - Prêt pour l'enregistrement SET NOCOUNT ON; INSERT INTO EmployeeTest VALUES (ROUND (2000000 * RAND () + 10000,0)), CONVERT (DATETIME, ROUND (60000 * RAND () - 30000, 9)), @string) END OK Créons un index non clusterisé sur chaque colonne.
CREATE NONCLUSTERED INDEX [NCI_EmployeeTest_Salary] ON [dbo]. [EmployeeTest] ([Salary] ASC) GO CREER UN INDEX NONCLUSTERED [NCI_EmployeeTest_DateOfBirth] ON [dbo]. [EmployeeTest] ([DateOfBirth] ASC) GO CREATE INDEX NONCLUSTERED [NCI_EmployeeTest_EmployeeName] ON [dbo]. [EmployeeTest] ([EmployeeName] ASC) GO
Voyons quelques exemples de requêtes pour voir la différence entre les requêtes sargable et non-sargable.
1. Filtrer les résultats en fonction des noms d’employés commençant par A
SET STATISTICS IO ON - Requête non modifiable en raison de la fonction utilisée dans la clause Where SELECT EmployeeName FROM EmployeeTest WHERE (NomEmploi, 1) = 'A'; - Requête sargable SELECT EmployeeName FROM EmployeeTest WHERE EmployeeName LIKE 'A%'; SET STATISTICS IO OFF
Les statistiques ci-dessous montrent que la première requête non discutable a nécessité 680 lectures logiques, alors que la requête paramétrable avec une recherche avec caractère générique n'a effectué que 25 lectures logiques.
(3115 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 680, lectures physiques 1, lectures anticipées 688, lectures logiques lob 0, lectures physiques lob 0, lectures lob anticipées 0. (3115 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 25, lecture physique 0, lecture à l'avance 0, lecture logique du lob 0, lecture physique du lob 0, lecture lob à l'avance 0.Les plans d’exécution ci-dessous montrent que la première requête Non-Sargable prend Coût 97%, alors que la requête Sargable prend 3% avec Index Seek.
2. Filtrer les résultats pour une année spécifique
SET STATISTICS IO ON - Requête non modifiable en raison de la fonction utilisée dans la clause Where. SELECT DateOfBirth DE EmployeeTest WHERE YEAR (DateOfBirth) = '1952'; - Requête sélectionnable SELECT DateOfBirth DE EmployeeTest WHERE DateOfBirth> = '19520101' ET DateOfBirth <'19530101'; SET STATISTICS IO OFF
Les statistiques ci-dessous montrent que la première requête non-Sargable a nécessité 226 lectures logiques, alors que la requête Sargable n'a effectué que quatre lectures logiques.
(628 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 226, lecture physique 0, lecture à l'avance 0, lecture logique du lob 0, lecture physique du lob 0, lecture lob à l'avance 0.(628 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 4, lecture physique 0, lecture à l'avance 0, lecture logique du lob 0, lecture physique du lob 0, lecture lob à l'avance 0.Les plans d’exécution ci-dessous montrent que la première requête Non-Sargable prend 98% de coût par lot avec Index Scan, alors que la requête Sargable prend 2% avec Index Seek.
3. Calculs sur une colonne d'index dans une clause Where
SET STATISTICS IO ON - Requête non modifiable en raison du calcul effectué sur la colonne d'indexation --in où Clause SELECT Salaire FROM EmployeeTest WHERE Salaire / 2 = 50147; --Sargable Query SELECT Salaire DE EmployeeTest WHERE Salaire = (50147 * 2); SET STATISTICS IO OFF
Les statistiques ci-dessous montrent que la première requête non compressible a nécessité 178 lectures logiques, alors que la requête compressable n'a effectué que deux lectures logiques.
(3 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 178, lecture physique 0, lecture anticipée 0, lecture logique lob 0, lecture physique lob 0, lecture anticipée lob 0. (3 ligne (s) concernée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 2, lecture physique 0, lecture à l'avance 0, lecture logique du lob 0, lecture physique du lob 0, lecture lob à l'avance 0.Les plans d’exécution ci-dessous montrent la première requête non sargable. 99% de coût par lot avec Index Scan, alors que la requête Sargable prend 1% avec Index Seek.
4. Utilisation de la fonction ISNULL dans une clause Where
SET STATISTICS IO ON - Requête non modifiable en raison de la fonction ISNULL sur la colonne d'index --in où Clause sélectionnez EmployeeName FROM EmployeeTest où ISNULL (EmployeeName, 'Vru') = 'Vru'; --Sargable Query sélectionnez EmployeeName FROM EmployeeTest où EmployeeName = 'Vru' OU EmployeeName IS NULL; SET STATISTICS IO OFF
Les statistiques ci-dessous montrent que la première requête non compressible a nécessité 680 lectures logiques, alors que la requête paramétrable n'a effectué que six lectures logiques.
(1 ligne (s) affectée (s)) Tableau 'EmployeeTest'. Nombre de numérisations 1, lectures logiques 680, lecture physique 0, lecture anticipée 0, lecture logique lob 0, lecture physique lob 0, lecture anticipée lob 0. (1 ligne (s) concernée (s)) Tableau 'EmployeeTest'. Nombre de balayage 2, lectures logiques 6, lecture physique 0, lecture à l'avance 0, lecture logique du lob 0, lecture physique du lob 0, lecture lob à l'avance 0.Les plans d’exécution ci-dessous montrent la première requête non sargable. 99% de coût par lot avec Index Scan, alors que la requête Sargable prend 1% avec Index Seek.
