Résolution des problèmes de performances des requêtes : assemblage de constantes et évaluation d'expressions pendant l'estimation de cardinalité

Expressions pouvant être assemblées

SQL Server utilise l'assemblage de constantes avec les types d'expressions suivants :

• Expressions arithmétiques telles que 1+1, 5/3*2 ne contenant que des constantes.

• Expressions logiques telles que 1=1 et 1>2 AND 3>4 ne contenant que des constantes.

• Fonctions intégrées considérées comme pouvant être assemblées par SQL Server, y compris CAST et CONVERT. En général, une fonction intrinsèque peut être assemblée si elle est fonction de ses données d'entrée uniquement, à l'exclusion de toute information contextuelle (options SET, paramètres de langue, options de base de données et clés de chiffrement). Les fonctions non déterministes ne peuvent pas être assemblées. Les fonctions intégrées déterministes peuvent être assemblées, à quelques exceptions près.

Les objets volumineux constituent une exception. Si le type de résultat du processus d'assemblage est un type d'objet volumineux (text, image, nvarchar(max), varchar(max) ou varbinary(max)), SQL Server n'assemble pas l'expression.

Expressions ne pouvant pas être assemblées

Aucun des autres types d'expressions ne peut être assemblé. C'est notamment le cas des types d'expressions suivants :

• Expressions non constantes, par exemple une expression dont le résultat dépend de la valeur d'une colonne.

• Expressions dont les résultats dépendent d'une variable locale ou d'un paramètre, par exemple @x.

• Fonctions non déterministes.

• Fonctions définies par l'utilisateur (Transact-SQL et CLR).

• Expressions dont les résultats dépendent de paramètres de langue.

• Expressions dont les résultats dépendent d'options SET.

• Expressions dont les résultats dépendent d'options de configuration du serveur.

Exemples d'expressions constantes pouvant ou ne pouvant pas être assemblées

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Évaluation d'expressions au moment de la compilation pour l'estimation de cardinalité

Certaines expressions qui ne sont pas assemblées, mais dont les arguments (qu'il s'agisse de paramètres ou de constantes) sont connus au moment de la compilation, sont évaluées pendant l'optimisation par l'estimateur de la taille de l'ensemble de résultats (cardinalité) inclus dans l'optimiseur.

Par exemple, les fonctions intégrées et opérateurs spéciaux suivants sont évalués au moment de la compilation si leurs données d'entrée sont connues : UPPER, LOWER, RTRIM, DATEPART( YY seulement ), GetDate, CAST et CONVERT.

Les opérateurs suivants sont également évalués au moment de la compilation si toutes leurs données d'entrée sont connues :

• Opérateurs arithmétiques : +, -, *, /, - unaire,

• Opérateurs logiques : AND, OR, NOT

• Opérateurs de comparaison : <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

L'optimiseur n'évalue pas d'autres fonctions ou opérateurs pendant l'estimation de cardinalité.

Exemples d'évaluation d'expression au moment de la compilation

USE AdventureWorks
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1

USE AdventureWorks
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END

Méthodes conseillées : Utilisation de l'assemblage de constantes et de l'évaluation d'expressions au moment de la compilation en vue de générer des plans de requête optimaux

Pour être sûr de générer des plans de requêtes optimaux, il est préférable de concevoir les requêtes, procédures stockées et lots de telle sorte que l'optimiseur de requête puisse estimer correctement la sélectivité des conditions en fonction de statistiques relatives à la distribution de vos données. Dans le cas contraire, l'optimiseur doit utiliser une estimation de sélectivité par défaut (30 % dans l'exemple précédent).

Pour garantir que l'estimation de cardinalité de l'optimiseur fournira des estimations correctes, vous devez commencer par vérifier que les options SET de la base de données AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS ont la valeur ON (configuration par défaut) ou qu'il existe des statistiques créées manuellement pour toutes les colonnes référencées dans une condition de requête. Ensuite, lorsque vous créez les conditions dans vos requêtes, tenez si possible compte des recommandations suivantes :

• Évitez d'inclure des variables locales dans les requêtes. Utilisez plutôt des paramètres, des littéraux ou des expressions.

• Limitez l'emploi d'opérateurs et de fonctions incorporés dans une requête qui contient un paramètre, conformément à la liste fournie dans la section Évaluation d'expressions au moment de la compilation pour l'estimation de cardinalité.

• Vérifiez que les expressions constituées de constantes uniquement qui sont incluses dans la condition d'une requête peuvent être soit assemblées, soit évaluées au moment de la compilation.

• Si vous devez utiliser une variable locale pour évaluer une expression destinée à être incluse dans une requête, envisagez de l'évaluer dans une étendue différente de celle de la requête. Voici quelques méthodes utiles dans ce cas :

  • Passez la valeur de la variable à une procédure stockée contenant la requête à évaluer, puis faites en sorte que cette requête utilise le paramètre de la procédure à la place d'une variable locale.

  • Créez une chaîne contenant une requête basée en partie sur la valeur de la variable locale, puis exécutez cette chaîne via un code SQL dynamique (EXEC ou sp_executesql).

  • Paramétrez la requête et exécutez-la à l'aide de sp_executesql, puis passez la valeur de la variable en tant que paramètre à la requête.

Voir aussi

Concepts

Autres ressources