Acquérir de l’expérience dans l’optimisation des requêtes SQL peut être très difficile et compliqué pour les développeurs ou administrateurs de bases de données. Dans cet article, nous travaillerons sur une étude de cas et apprendrons comment améliorer les performances d’une requête étape par étape. En comprenant l’approche pratique des problèmes de performance des requêtes, vous serez en mesure d’améliorer les performances de vos propres requêtes SQL.

Prérequis

Dans cet article, nous utiliserons la base de données d’exemple Adventureworks2017. De plus, nous utiliserons le script Create Enlarged AdventureWorks Tables pour obtenir une version agrandie des tables SalesOrder et SalesOrderDetail. Cela est nécessaire car la taille de la base de données d’origine n’est pas suffisante pour effectuer des tests de performance. Assurez-vous d’installer la base de données Adventureworks2017 et d’exécuter le script d’agrandissement des tables avant de poursuivre l’étude de cas.

Étude de cas : Optimisation des requêtes SQL sans créer un nouvel index

Imaginez que vous êtes employé en tant qu’administrateur de base de données à temps plein dans une entreprise qui utilise encore SQL Server 2017. L’équipe de développement logiciel s’est plainte des performances d’une requête spécifique. Votre objectif est d’améliorer les performances de la requête sans créer un nouvel index sur les tables, mais vous pouvez réécrire la requête.

Jetons un coup d’œil à la requête problématique :

SELECT p.ProductID, p.ProductNumber, p.Name, s.CarrierTrackingNumber, h.AccountNumber, h.CreditCardApprovalCode, dbo.[ufnGetStock](p.ProductID) AS Stock, CONCAT(SUBSTRING(CarrierTrackingNumber, 1, 4), SUBSTRING(p.Class, 1, 4))
FROM Sales.SalesOrderDetailEnlarged s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeaderEnlarged h ON h.SalesOrderID = s.SalesOrderID
WHERE s.OrderQty > 2 AND LEN(CreditCardApprovalCode) > 10
ORDER BY CONCAT(SUBSTRING(CarrierTrackingNumber, 1, 4), SUBSTRING(p.Class, 1, 4)), ProductID DESC

La première étape de l’optimisation des requêtes SQL : Identifier les problèmes

La première étape de l’optimisation d’une requête consiste à identifier les problèmes. Activez le plan d’exécution réel dans SSMS et exécutez la requête problématique. Le plan d’exécution réel fournit des statistiques précises et des informations sur la requête. Dans ce cas, la requête a pris 142 secondes pour s’exécuter.

Après avoir analysé le plan d’exécution, nous pouvons identifier plusieurs problèmes :

• La fonction définie par l’utilisateur (UDF) utilisée dans la requête peut causer un problème de performance.
• La requête ne génère pas de plan d’exécution parallèle en raison de la présence de fonctions scalaires.
• La requête rencontre un problème de débordement TempDB.

Améliorer les performances de la fonction scalaire dans la requête

Les fonctions scalaires peuvent être un tueur de performance pour les requêtes. Dans ce cas, la fonction scalaire “ufnGetStock” est invoquée pour chaque ligne de l’ensemble de résultats, ce qui crée un goulot d’étranglement de performance. Pour améliorer les performances, nous pouvons transformer la fonction scalaire en une sous-requête en utilisant l’opérateur CROSS APPLY.

SELECT p.ProductID, p.ProductNumber, p.Name, s.CarrierTrackingNumber, h.AccountNumber, h.CreditCardApprovalCode, Warehouse.Stock AS Stock
FROM Sales.SalesOrderDetailEnlarged s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeaderEnlarged h ON h.SalesOrderID = s.SalesOrderID
CROSS APPLY (
    SELECT ISNULL(SUM(Production.[Quantity]), 0) AS Stock
    FROM [Production].[ProductInventory] Production
    WHERE Production.[ProductID] = p.ProductID AND Production.[LocationID] = '6'
) AS Warehouse
WHERE s.OrderQty > 2 AND LEN(CreditCardApprovalCode) > 10
ORDER BY CONCAT(SUBSTRING(CarrierTrackingNumber, 1, 4), SUBSTRING(p.Class, 1, 4)), p.ProductID DESC

En transformant la fonction scalaire en une sous-requête, nous nous assurons que la requête peut s’exécuter en parallèle et sera plus rapide que la version originale.

Faire preuve de créativité pour l’optimisation des requêtes SQL

Pour résoudre le problème de débordement TempDB, nous pouvons créer une table temporaire et y insérer toutes les lignes. De plus, nous pouvons ajouter une colonne calculée au lieu d’utiliser la fonction LEN dans la clause WHERE. Cela permettra une utilisation plus flexible de la table temporaire.

IF OBJECT_ID(N'tempdb..#PerfmonTable') IS NOT NULL
BEGIN
    DROP TABLE #PerfmonTable
END

CREATE TABLE #PerfmonTable (
    [ProductID] [int] NOT NULL,
    [ProductNumber] [nvarchar](25) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [AccountNumber] [nvarchar](15) NULL,
    [CreditCardApprovalCode] [varchar](15) NULL,
    [Stock] [int] NOT NULL,
    [SortParameter] [nvarchar](6) NOT NULL,
    SmallApp AS ISNULL(LEN(CreditCardApprovalCode), 0) PERSISTED
)

INSERT INTO #PerfmonTable WITH (TABLOCK)
SELECT p.ProductID, p.ProductNumber, p.Name, s.CarrierTrackingNumber, h.AccountNumber, h.CreditCardApprovalCode, Warehouse.Stock, CONCAT(SUBSTRING(CarrierTrackingNumber, 1, 4), SUBSTRING(Class, 1, 4)) AS SortParameter
FROM Sales.SalesOrderDetailEnlarged s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeaderEnlarged h ON h.SalesOrderID = s.SalesOrderID
CROSS APPLY (
    SELECT ISNULL(SUM(Production.[Quantity]), 0) AS Stock
    FROM [Production].[ProductInventory] Production
    WHERE Production.[ProductID] = p.ProductID AND Production.[LocationID] = '6'
) AS Warehouse
WHERE s.OrderQty > 2

DELETE FROM #PerfmonTable WHERE SmallApp <= 10

En utilisant une table temporaire et en ajoutant une colonne calculée, nous pouvons éliminer le problème de débordement TempDB. Le temps d’exécution de la requête est considérablement réduit.

Utiliser des index pour améliorer les performances de tri

Lors de la conception d’un index efficace pour les requêtes incluant la clause ORDER BY, nous pouvons éliminer la nécessité d’une opération de tri dans le plan d’exécution. En créant un index non clusterisé qui satisfait les exigences de l’opération de tri, nous pouvons améliorer les performances.

CREATE NONCLUSTERED INDEX IX_Sort ON #PerfmonTable (SortParameter, ProductID DESC) INCLUDE ([ProductNumber], [Name], [CarrierTrackingNumber], [AccountNumber], [CreditCardApprovalCode], Stock)

En créant l’index ci-dessus, nous pouvons éliminer l’opérateur de tri dans le plan d’exécution. Le temps d’exécution de la requête est encore réduit.

Conclusion

Dans cet article, nous avons appris des techniques pratiques pour l’optimisation des requêtes SQL. En abordant le problème de la fonction scalaire, en utilisant des plans d’exécution parallèles et en surmontant les problèmes de débordement TempDB, nous avons pu améliorer considérablement les performances de la requête dans l’étude de