Requête en cours d'exécution plus longue en ajoutant des conditions WHERE inutilisées

SQL Server ne devrait-il pas réaliser au moment de l'exécution que ces conditions ne seront jamais satisfaites et les ignorer complètement?

Non, absolument pas. Il y a deux facteurs en jeu ici.

1. SQL Server ne garantit pas le court-circuit de l'opérateur boolean. Voir Court-circuit sur l' opérateur boolean SQL Server pour un exemple montrant clairement comment l'optimization de la requête peut inverser l'ordre de l'évaluation de l'expression booleanne. Alors qu'à première vue, cela ressemble à un bug de l'esprit de programmation C, comme à l'impératif, c'est la bonne chose à faire pour le monde SQL orienté set déclaratif.

2. OU est l'ennemi de SQL SARGability. Les instructions SQL sont complétées dans un plan d'exécution, puis le plan est exécuté. Le plan est réutilisé entre les invocations (est mis en cache). En tant que tel, le compilateur SQL doit générer un seul plan qui s'adapte à tous les cas OR distincts (@ AuthorType = 1 AND @ AuthorType = 2 AND @ AuthorType = 3). Quand il s'agit de générer le plan de requête est exactement comme si @AuthorType aurait toutes les valeurs à la fois, dans un sens. Le résultat est presque toujours le pire plan possible, celui qui ne peut bénéficier à aucun index parce que les différentes twigs d'OR se contredisent, ainsi il finit par balayer toute la table et vérifier les rangées une par une.

La meilleure chose à faire dans votre cas, et dans tout autre cas impliquant un OR boolean, est de déplacer le @AuthorType en dehors de la requête:

 IF (@AuthorType = 1) SELECT ... FROM ... WHERE ... ELSE IF (@AuthorType = 2) SELECT ... FROM ... WHERE ... ELSE ... 

Parce que chaque twig est clairement séparée dans sa propre déclaration, SQL peut créer le path d'access approprié pour chaque cas individuel.

La meilleure chose à faire est d'utiliser UNION ALL, comme chadhoc l'a déjà suggéré, et c'est la bonne approche dans les vues ou autres endroits où une seule instruction est requirejse (aucune IF n'est permise).

Cela doit être dû au fait qu'il est difficile pour l'optimiseur de gérer la logique de type "OU" avec les problèmes liés au reniflage des parameters . Essayez de changer votre requête ci-dessus pour une approche UNION comme mentionné dans le post ici . c'est-à-dire que vous vous refindez avec plusieurs instructions réunies avec un seul @AuthorType = x ET, permettant à l'optimiseur d'exclure des parties où la logique ET ne correspond pas au @AuthorType donné, et de chercher dans les index appropriés à leur tour. ressemblerait à ceci:

 SELECT * FROM TBooks WHERE (--...SOME CONDITIONS) AND @AuthorType = 1 AND --...DIFFERENT CONDITIONS) union all SELECT * FROM TBooks WHERE (--...SOME CONDITIONS) AND @AuthorType = 2 AND --...DIFFERENT CONDITIONS) union all ... 

Je devrais combattre l'envie de réduire le dédoublement … mais l'homme, ça ne me semble vraiment pas juste.

Est-ce que cela "se sentirait" mieux?

 SELECT ... beaucoup de colonnes et de choses compliquées ...
 DE 
 (
     CHOISIR MyPK
     De TBooks
     OÙ 
     (--... QUELQUES CONDITIONS) 
     AND @AuthorType = 1 ET - ... DIFFERENTES CONDITIONS) 
     union tout 
     CHOISIR MyPK
     De TBooks
     OÙ 
     (--... QUELQUES CONDITIONS) 
     AND @AuthorType = 2 ET - ... DIFFERENTES CONDITIONS) 
     union tout 
     ... 
 ) AS B1
 JOIN TBooks AS B2
     ON B2.MyPK = B1.MyPK
 JOIN ... autres tables ...

La pseudo-table B1 est juste la clause WHERE pour get les PKs. Cela est ensuite rejoint à la table d'origine (et tous les autres qui sont requirejs) pour get la "présentation". Cela évite de dupliquer les colonnes de présentation dans chaque UNION ALL

Vous pouvez aller plus loin et insert d'abord les PK dans une table temporaire, puis les joindre aux autres tables pour l'aspect présentation.

Nous faisons cela pour les très grandes tables où l'user a beaucoup de choix sur ce qu'il faut searchr.

 DECLARE @MyTempTable TABLE
 (
     MyPK int NON NUL,
     CLÉ PRIMAIRE
     (
         MyPK
     )
 )

 IF @LastName N'EST PAS NUL
 COMMENCER
    INSERT INTO @MyTempTable
    (
         MyPK
    )
    CHOISIR MyPK
    De MyNamesTable
    WHERE LastName = @LastName - Disons que nous avons un index efficace pour cette
 FIN
 AUTRE
 SI @Country N'EST PAS NUL
 COMMENCER
    INSERT INTO @MyTempTable
    (
         MyPK
    )
    CHOISIR MyPK
    De MyNamesTable
    WHERE Country = @Country - Vous avez un index sur celui-ci aussi
 FIN

 ... etc

 SELECT ... colonnes de présentation
 FROM @MyTempTable AS T
     REJOIGNEZ MyNamesTable AS N
         ON N.MyPK = T.MyPK - une jointure PK, V. efficace
     JOIN ... autres tables ...
         SUR ....
 WHERE (@LastName IS NULL OU Lastname @LastName)
       AND (@Country IS NULL OU Country @ Country)

Notez que tous les tests sont répétés [techniquement vous n'avez pas besoin du @Lastname un :)], y compris ceux qui ne sont pas (disons) pas dans les filters d'origine pour créer @MyTempTable.

La création de @MyTempTable est conçue pour tirer le meilleur parti des parameters disponibles. Peut-être que si @LastName ET @Country sont disponibles, il est beaucoup plus efficace de remplir la table que l'un d'entre eux, donc nous créons un cas pour ce scénario.

Problèmes d'échelle? Passez en revue les requêtes en cours et ajoutez des cas pour ceux qui peuvent être améliorés.