MS SQL Server
  1. MS SQL Server
  3. La clause WHERE ne filter pas dans un ordre logique
Intereting Posts
Comment returnner la sortie de la procédure stockée dans une variable dans le server sql SQL INNER QUERY renvoie plusieurs valeurs dans une requête UPDATE Comment puis-je faire pivoter les attributes d'une colonne XML dans T-SQL Meilleures pratiques pour gérer datatables cryptées dans MSSQL transférer des données d'Excel vers SQL Server Comment returnner Resulset avec un rayon donné Requête efficace pour find des loggings en double Comment puis-je sélectionner des loggings distincts par un nom? Utilisation de SQL Server Native Client avec Pyodbc sous Linux Interroger toutes les dates inférieures à la date donnée (mois et année) Comment déterminer si la valeur par défaut a été fournie à la procédure stockée? Newbie SQL Pivot requête Comment détecter ce triggersur a été créé avec ExecuteNonQuery Construction de requêtes: Count, Case, Datediff et Group By SQL Server comptant les connections à distance

La clause WHERE ne filter pas dans un ordre logique

J'écris une procédure stockée de search générale pour searchr dans une table basée sur beaucoup de filters que l'user peut sélectionner dans l'interface user (en utilisant MS-SQL 2008).

Voici la version simplifiée: 

CREATE PROCEDURE SearchAll @FirstName NVARCHAR(MAX) = NULL, @LastName NVARCHAR(MAX) = NULL, @Age INT = NULL AS SELECT * FROM persons WHERE (@FirstName IS NULL OR FirstName = @firstname) AND (@LastName IS NULL OR LastName = @LastName) AND (@Age IS NULL OR Age = @Age)

Il semble que si je passe NULL à @Age, il n'y aura pas de coût de performance. Mais, quand je teste avec une énorme quantité de données, j'ai une grande performance perdue!

Voici les requêtes qui sont les mêmes sur le plan logique mais TRÈS différentes en pratique: 

 DECLARE @FirstName NVARCHAR(MAX) = NULL DECLARE @Age INT = 23 ------------First slow------------ SELECT * FROM persons WHERE (@FirstName IS NULL OR FirstName = @firstname) AND (@Age IS NULL OR Age = @Age) ------------Very fast------------ SELECT * FROM persons WHERE Age = @Age

Est-ce que manquer un point?

Je sais que le moteur SQL trouve la meilleure correspondance pour les index, et … (avant d'exécuter la requête), mais il est évident que: @FirstName IS NULL et il n'y a pas besoin d'parsingr quoi que ce soit.

J'ai également testé la fonction ISNULL dans la requête (le même résultat).

Les requêtes contenant cette construction de @variable is null or @variable = column sont un désastre de performance. C'est parce que les plans SQL sont créés afin qu'ils fonctionnent pour n'importe quelle valeur de la variable. Pour une longue discussion sur le sujet, les problèmes et les solutions possibles, voir Conditions de search dynamics dans T-SQL

Le problème, comme cela a déjà été mentionné, est que le plan de requête sera construit pour fonctionner avec n'importe quelle valeur des variables. Vous pouvez contourner ce problème en créant la requête avec uniquement le paremètre requirejs, comme suit: 

 CREATE PROCEDURE SearchAll @FirstName NVARCHAR(MAX) = NULL, @LastName NVARCHAR(MAX) = NULL, @Age INT = NULL AS BEGIN DECLARE @sql NVARCHAR(MAX), @has_where BIT SELECT @has_where = 0, @sql = 'SELECT * FROM persons ' IF @FirstName IS NOT NULL SELECT @sql = @sql + 'WHERE FirstName = ''' + @FirstName + '''', @has_where = 1 IF @LastName IS NOT NULL SELECT @sql = @sql + CASE WHEN @has_where = 0 THEN 'WHERE ' ELSE 'AND ' END + 'LastName = ''' + @LastName + '''', @has_where = 1 IF @Age IS NOT NULL SELECT @sql = @sql + CASE WHEN @has_where = 0 THEN 'WHERE ' ELSE 'AND ' END + 'Age = ' + CAST(@Age AS VARCHAR), @has_where = 1 EXEC sp_executesql @sql END