Impact sur les performances des procédures stockées du serveur SQL

Question

Je veux écrire une procédure stockée qui peut être utilisée à des fins multiples. Quelqu'un peut-il m'aider à trouver la meilleure pratique pour écrire une procédure stockée dans SQL Server à partir de ces approches ? Et quels sont les impacts sur les performances de chacune d'entre elles ?

Approche 1 : paramètres @KEY, @VALUE

IF @KEY = 'ISACTIVE'
BEGIN 
    SELECT ID, STATECODE, STATENAME, ISACTIVE 
    FROM STATE 
    WHERE ISACTIVE = @VALUE
END
ELSE IF @KEY = 'STATECODE'
BEGIN
    SELECT ID, STATECODE, STATENAME, ISACTIVE 
    FROM STATE  
    WHERE STATECODE = @VALUE
END
ELSE
BEGIN
    SELECT ID, STATECODE, STATENAME, ISACTIVE 
    FROM STATE
END

Approche : 2 paramètres @ISACTIVE, @STATECODE

SELECT ID, STATECODE, STATENAME, ISACTIVE 
FROM STATE
WHERE ISACTIVE = ISNULL(@ISACTIVE, ISACTIVE) 
  AND STATECODE = ISNULL(@STATECODE,STATECODE)

Approche : 3

paramètres @KEY, @VALUE

DECLARE @SQLQRY NVARCHAR(500)
SET @SQLQRY = 'SELECT ID, STATECODE, STATENAME, ISACTIVE FROM STATE WHERE ' + @KEY +' = ' + @VALUE
EXEC @SQLQRY

Quelle est la meilleure approche en termes de performance et de réutilisation ?

Ou existe-t-il une autre solution pour y parvenir ?

Answer 1

J'opterais sans hésiter pour le Approche 2 qui, à mon avis, est à la fois maintenable et permanent.

Dans le cadre de la Approche 1 Vous dupliquez le code et ce n'est donc pas la meilleure approche du point de vue de la maintenance.

Dans le cadre de la Approche 3 Lorsque vous utilisez une requête SQL dynamique, ce n'est pas la meilleure façon de procéder car elle ne fournit pas de support intellisense et limite la capacité du serveur SQL à optimiser les performances.

Si vous rencontrez des problèmes de performance, envisagez d'introduire un index sur les colonnes "ISACTIVE" et "STATECODE". Analysez également le plan d'exécution de la requête et utilisez le profileur pour effectuer des recherches plus approfondies si nécessaire.

Answer 2

Je veux écrire une procédure stockée qui peut être utilisée à des fins multiples.

En tenant compte de ce désir (bien que je sois préoccupé par ce besoin), cela dépend .

• Approche 1 : Vous commencerez à répéter beaucoup de code au fur et à mesure que vos règles de gestion deviendront complexes. Lorsque vous vous rendez compte que vous avez plusieurs sélections répétées avec éventuellement plusieurs jointures ou EXISTS et peut-être qu'un autre programmeur qui doit modifier quelque chose oublie d'éditer l'une des requêtes. Les choses deviennent encore plus compliquées lorsque vous commencez à appliquer plusieurs filtres en même temps (comme SELECT où IsActive = 0 AND StateCode = 'AR' ). Cette approche permet de renvoyer plus facilement différents ensembles de résultats en fonction de la clé que vous souhaitez filtrer.
• Approche 2 : Vous pouvez empiler des filtres ensemble et vous n'aurez qu'à écrire votre SELECT une seule fois, mais vous perdrez en performance surtout si vous avez beaucoup de lignes et que les filtres s'appliquent à différentes tables. Un autre problème est que vous devrez peut-être fournir une valeur de remplacement pour les deux extrémités de la comparaison : si nous voulons sélectionner uniquement les enregistrements qui ont NULL sur StateCode Vous devrez alors utiliser un filtre du type ISNULL(StateCode, 'SomeImpossibleStateCode') = ISNULL(@StateCode, 'SomeImpossibleStateCode') ou comme (@StateCode IS NULL AND StateCode IS NULL) OR (@StateCode = StateCode) .
• Approche 3 : Comme il est dynamique, il est plus difficile à maintenir, les tables/colonnes dépendantes n'apparaîtront pas dans les vues telles que sysdepends ou autre et vous devrez peut-être prendre des mesures supplémentaires pour éviter l'injection SQL. Cependant, il permet d'éviter complètement les filtres conditionnels, ce qui rendra votre requête plus simple et plus performante. Vous pouvez même recevoir un tableau comme paramètre d'entrée qui inclut les filtres que vous voulez appliquer, l'opérateur logique ( AND o OR ) qui les relie et les priorités du filtre.

Conclusion :

• Si votre table n'est pas grande et que le nombre de clés à rechercher est faible, approche 2 serait la meilleure solution, bien qu'il faille tester les performances.

• Approche 3 semble le mieux à même de gérer tous les scénarios, y compris les performances.

• J'éviterais définitivement approche 1 car elle est très sujette aux erreurs de maintenance. Si vous avez l'impression que différentes clés doivent renvoyer des colonnes différentes ou appliquer des conditions de requête différentes, il est temps de diviser votre SP universel en plusieurs clés.

Answer 3

À lire absolument : Conditions de recherche dynamique en T-SQL par Erland Sommarskog. En l'état, l'approche 2 est mauvaise, mais elle peut devenir la meilleure.

Voici un bref résumé de l'article en question.

Approche 1 est bon du point de vue de la performance. L'optimiseur peut générer de bons plans et utiliser les index s'ils sont disponibles. Le problème ici est le nombre de variantes/permutations possibles de la requête. Si vous avez trois paramètres Key1, Key2, Key3, vous obtiendrez un IF imbriqué assez laid à trois niveaux de profondeur. Si vous ajoutez quelques paramètres supplémentaires, cela devient peu pratique du point de vue de la maintenance.

Approche 2 est une bonne chose du point de vue de la maintenance, mais en l'état, elle est mauvaise du point de vue de la performance. ISACTIVE = ISNULL(@ISACTIVE, ISACTIVE) sera évaluée même si @ISACTIVE est NULL et peut empêcher une utilisation efficace des index.

La requête doit être réécrite comme suit :

SELECT ID, STATECODE, STATENAME, ISACTIVE 
FROM STATE
WHERE
    (ISACTIVE = @ISACTIVE OR @ISACTIVE IS NULL) 
    AND (STATECODE = @STATECODE OR @STATECODE IS NULL)
OPTION(RECOMPILE);

OPTION(RECOMPILE) est très important. Il permet essentiellement à l'optimiseur d'intégrer les valeurs réelles des paramètres dans la requête, de simplifier les expressions logiques et de générer un plan aussi bon que dans l'approche 1, voire meilleur. Voir l'article pour plus de détails.

Approche 3 produirait de bons plans, comme dans l'approche 1, mais pas les meilleurs comme dans l'approche 2. La maintenance n'est pas mauvaise, bien que le SQL dynamique présente des difficultés. Si vous optez pour cette solution, vous devriez lire un autre article d'Erland La malédiction et les bénédictions du SQL dynamique .