T-Clause SQL where Cas de Déclaration d'Optimisation (en option paramètres de procédure stockée)

J'ai été aux prises avec celui-ci pendant un certain temps maintenant. J'ai une procédure stockée qui prend 3 paramètres qui sont utilisés pour filtrer. Si une valeur spécifique est passé, je veux filtre. Si -1 est passé, de me donner tous.

Je l'ai essayé les deux façons suivantes:

Première façon:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
parm1 = CASE WHEN @PARM1= -1 THEN parm1  ELSE @PARM1 END  
AND parm2 = CASE WHEN @PARM2 = -1 THEN parm2  ELSE @PARM2 END  
AND parm3 = CASE WHEN @PARM3 = -1 THEN parm3  ELSE @PARM3 END

Deuxième Façon:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
(@PARM1 = -1 OR parm1 = @PARM1)  
AND (@PARM2 = -1 OR parm2 = @PARM2)  
AND (@PARM3 = -1 OR parm3 = @PARM3)

J'ai lu quelque part que la deuxième façon de court-circuit et de ne jamais eval la deuxième partie, si vrai. Mon DBA dit il oblige à une analyse de la table. Je n'ai pas vérifié, mais il semble plus lent sur certains cas.

De la table principale, que ce point de vue sélectionne à partir de quelque part autour de 1,5 millions de disques, et le point de vue produit d'adhérer à environ 15 autres tables de recueillir un tas d'autres informations.

Ces deux méthodes sont lents...une prise de moi à partir de l'instant de n'importe où à partir de 2-40 secondes, ce qui dans ma situation est totalement inacceptable.

Est-il une meilleure manière qui n'implique pas de rupture vers le bas dans chaque cas séparé de spécifique vs -1 ?

Toute aide est appréciée. Merci.

OriginalL'auteur IronicMuffin | 2009-10-08

6

J'ai lu quelque part que la deuxième façon de court-circuit et de ne jamais eval la deuxième partie, si vrai. Mon DBA dit il oblige à une analyse de la table.

Vous lisez mal; il pas de court-circuit. Votre DBA est juste; il ne sera pas bien jouer avec l'optimiseur de requête et sans doute une analyse de la table.

La première option est à peu près aussi bon qu'il obtient. Vos options pour améliorer les choses sont dynamiques sql ou une longue procédure stockée avec chaque combinaison possible de filtrer les colonnes de sorte que vous obtenez indépendants les plans de requête. Vous pouvez également essayer d'utiliser le "with RECOMPILE" option, mais je ne pense pas que cela va vous aider.

Merde. Me battre pour elle. +1 🙂
La première option ne seront pas nécessairement les mêmes résultats que la 2e. Si vous avez les lignes de votre tableau avec des valeurs NULLES, elles ne seront PAS retournés par "l'option 1 de la requête". Par exemple.... Select * From Table Where NullableColumn = NullableColumn
Tous les trois parms ne sont PAS NULLES, ce qui n'est pas un problème dans ce cas. On dirait que je pourrait être coincé avec l'option 1.
Merci à tous pour les réponses rapides et de très bons conseils.

OriginalL'auteur Joel Coehoorn
5

si vous exécutez SQL Server 2005 ou ci-dessus, vous pouvez utiliser Fi à faire plusieurs version de la requête avec le bon OÙ un index peut être utilisé. Chaque plan de requête sera placé dans le cache de requêtes.

aussi, voici un article très complet sur ce sujet:

Dynamique des Conditions de Recherche en T-SQL par Erland Sommarskog

il couvre l'ensemble des questions et des méthodes d'essayer d'écrire des requêtes avec plusieurs option de recherche des conditions de

voici la table des matières:
```
 Introduction 
L'Étude De Cas: À La Recherche Des Commandes 
Le Northgale Base De Données 
SQL dynamique 
Introduction 
Sp_executesql 
À l'aide de la CLR 
Utiliser EXEC() 
Lorsque La Mise En Cache N'Est Pas Vraiment Ce Que Vous Voulez 
SQL statique 
Introduction 
x = @x @x EST NULLE 
À l'aide des instructions if 
Umachandar du Sac à malices 
L'Aide De Tables Temporaires 
x = @x ET @x n'EST PAS NUL 
La Manipulation Complexe Des Conditions 
Les Solutions hybrides – en Utilisant à la fois Statique et Dynamique SQL 
À L'Aide De Vues 
À L'Aide De Fonctions Inline Table 
Conclusion 
Commentaires et Remerciements 
Révision De L'Histoire
```
+1 pour la liaison rien par M. Sommarskog
Signet...merci. Nous utilisons un mélange de 2000/2005 serveurs, malheureusement il est 2000.
l'article recense de nombreux moyens pour traiter la variable de conditions de recherche, l'IFs est juste un

OriginalL'auteur KM.
2

Si vous passez une valeur null lorsque vous voulez tout, alors vous pouvez rédiger votre clause where comme
```
   Where colName = IsNull(@Paramater, ColName)  
```
C'est essentiellement la même que celle de votre première méthode... il fonctionnera tant que la colonne elle-même n'est pas nullable... les valeurs Null DANS la colonne plaisante un peu.

La seule approche pour accélérer est d'ajouter un index sur la colonne filtrée dans la clause where. Est-il déjà un? Si non, qui se traduira par une amélioration spectaculaire.

Pensez-vous que cela va l'accélérer? Il semble que c'est fondamentalement la même opération, mais la vérification de la valeur null au lieu de -1. Est IsNull plus efficace qu'un CAS?
comme votre nom) Non, vous avez tout à fait raison, c'est équivalent à l'an premier de la méthode...

OriginalL'auteur Charles Bretana
0

Pas d'autre moyen que je peux penser, puis de le faire:

OÙ

(MyCase EST NULL OU MyCase = @MyCaseParameter)
ET ....

Le second est plus simple et lisible par les autres développeurs, si vous me demandez.

Il est aussi lent que la melasse. Ne l'utilisez pas.
avez-vous quelque chose? Non pas que je doute de vous, je suis juste à la recherche de certaines données d'une façon ou de l'autre.
J'ai deux indicateurs que la deuxième méthode peut forcer une analyse de la table, et qui n'est pas une bonne chose, avec 1,5 million d'enregistrements.
Cette notion de lent que la mélasse est complètement faux. Êtes-vous en déclarant que dynamiquement la construction d'une clause where à l'intérieur de votre client app est le meilleur? Je prie de différer...il est propre code et sa DBA doit être demandé.

OriginalL'auteur JonH
0

SQL 2008 et plus tard faire quelques améliorations à l'optimisation des choses comme (MyCase IS NULL OR MyCase = @MyCaseParameter) AND ....

Si vous pouvez mettre à niveau, et si vous ajoutez un OPTION (RECOMPILE) pour obtenir de bons perf pour tous les param combinaisons (c'est une situation où il n'y a pas un seul plan qui est bon pour tous les param combinaisons de), vous pouvez constater que cela fonctionne bien.

http://blogs.msdn.com/b/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx

OriginalL'auteur bartd

Vous devez vous connecter pour publier un commentaire.