Performances des procédures stockées exécutant une requête recomposée EXEC "SELECT"

[ValM_ENI]

Développeurs, Développeuses SQL Server,

J'ai besoin des lumières des Pros SQL Server concernant les performances d'une procédure stockée qui exécuterait une requête par un EXEC "SELECT ..." (requête recomposée dans une chaîne de texte) par rapport a une procédure qui exécuterait la même requête mais recomposée sans passer par une chaîne de texte.

Est ce une méthode à bannir ? les performances peuvent elles être plombées par ce type de développement ? (si oui pourquoi)

Quant est il de la compilation ou recompilation de ce type de procédure ? de la possibilité de la part du moteur SQL Server de réutiliser le plan d'exécution en cache ? etc ...

Merci de vos réponses avisées

[hmira]

La différence, en terme de performance, entre le SQL Dynamique et les procédures stockées est négligeable (généralement inférieur à 1%). Donc, le choix entre le SQL Dynamique (EXEC ou sp_executesql) et les procédures stockées, est rarement (pour ne pas dire jamais) guidé par le critère de performance.
Ceci dit, il est fortement recommandé d'utiliser sp_executesql en lieu et place de EXEC .. En effet, sp_executesql permet l'utilisation des paramètres et de ce fait, une meilleur réutilisation du plan d’exécution de la requête. sp_executesql est également fortement recommandée pour mieux se prémunir contre l'Injection SQL.

Le choix entre le SQL Dynamique et les procédures stockée se fait par rapport à d'autres aspects aussi importants que sont par exemple :
- "couplage faible" en faveur des procédures stockées : Mettre le code d'accès aux données dans une procédure stockée vous permet de modifier le comportement sans avoir à recompiler l'application
- Problème d'Injection SQL en défaveur du SQL Dymanique
- D'autre problème de sécurité en défaveur du SQL Dynamique, mais en faveur des procédures stockées. Vous pouvez par exemple autoriser un utilisateur à exécuter une procédures manipulant des tables sans pour autant autoriser l'utilisateur à accéder directement aux dites tables (emprunt d'identité - impersonate).
- Souplesse en faveur du SQL dynamique
- etc..

A+

[ValM_ENI]

Merci d'avoir pris de le tps de me répondre.

Mon problème n'est pas réellement l'utilisation de requête dynamique en dehors de procédure stockée. Actuellement j'ai plutôt choisi de développer des procédures stockées extrêmement flexibles et pour ce faire la requête est recomposée grâce à des variables qui sont renseignés suivant les paramètres passés à la procédure, ex : EXEC @SELECT + @WHERE + @ORDERBY.

Il y a bien d'autres techniques qui permettent de créer des requêtes dynamiques en utilisant le CASE...WHEN...THEN...ELSE...END dans la clause WHERE seulement cette technique limite la flexibilité de mes procédures.

En terme de performance je n'ai pas remarqué de différence entre les 2 méthodes pour une même requête générée au final.

Je me pose tout de même une question en cas de charge importante du serveur ou de gros volume à traiter est ce que ce genre de développement ne peut pas poser problème ?

Je ne vois pas pourquoi ce serait le cas mais je préfère demander conseil à des spécialistes expérimentés.

[hmira]

Ci-dessous quelques remarques:
- Il faut absolument bannir l'utilisation, dans la clause where, des expressions comme CASE...WHEN...THEN...ELSE...END. En effet, on abouti fatalement à des expression NON S-ARG (NON Searchable ARGguments), et une forte dégradation des performance. Il ne s'agit donc pas uniquement d'une question de flexibilité. Il faut toujours dans ce genre de situation privilégier le SQL Dynamique.

- Pour revenir à ta question. En cas de charge importante du serveur ou de gros volume à traiter, la requête finale qui sera analysée et exécutée par le moteur de base de donnée sera la même, et si problème de performance il y a, il ne faudra pas l'imputer à l'utilisation du SQL dynamique, il faudra analyser la requête finale transmise au serveur, analyser le plan d'exécution, indexes etc.., et ce, indépendamment de la manière dont la requête a été exprimée initialement (SQL static ou SQL dynamique).

- Il faudra aussi, évidement limiter le nombre d'enregistrement retournés par la procédure ; retourner des centaines de milliers voire même des millions d'enregistrements à l'application ou à l'utilisateur final, n'a généralement aucun sens, et constitue une totale ineptie ! Il faudra avoir recours au technique de pagination.

A+

[SQLpro]

Deux remarques :

Envoyé par hmira

La différence, en terme de performance, entre le SQL Dynamique et les procédures stockées est négligeable (généralement inférieur à 1%). Donc, le choix entre le SQL Dynamique (EXEC ou sp_executesql) et les procédures stockées, est rarement (pour ne pas dire jamais) guidé par le critère de performance.

Le SQL dynamique est même meilleur lorsque ce sont des petites requêtes peu complexes.
En revanche pour des requêtes très complexes du fait de la moindre optimisation du plna dans le cache cela peut être pénalisant.

Envoyé par hmira

- Il faut absolument bannir l'utilisation, dans la clause where, des expressions comme CASE...WHEN...THEN...ELSE...END. En effet, on abouti fatalement à des expression NON S-ARG (NON Searchable ARGguments), et une forte dégradation des performance.

Là c'est faux, le CASE est optimisé depuis longtemps sur SQL Server !!!

A +

[ValM_ENI]

Envoyé par SQLpro

Deux remarques :
Le SQL dynamique est même meilleur lorsque ce sont des petites requêtes peu complexes.
En revanche pour des requêtes très complexes du fait de la moindre optimisation du plna dans le cache cela peut être pénalisant.

Là ça m'intéresse , pour quelles raison le plna d'une requête dynamique complexe est moins optimisé dans le cache ?
Si a chaque appel de la procédure dans le code de l'application on récupère les données qui servent à générer les jointures, la clause where et la clause order by, n'est il pas possible de parfaitement optimiser les index et les statistiques en conséquence et du coup optimiser l'utilisation du cache SQL ?

[hmira]

Salut SQLPro, et Merci pour tes précisions.

En fait j'ai voulu parler des cas d'utilisation, que j'ai rencontrés plusieurs fois, et où à vouloir obstinément rendre une procédure la plus générique possible, gérant tous les cas et combinaisons possibles des paramètres en entrée, le développeur a recours à l’utilisation du CASE portant sur des expressions tordues

Code :

CASE (@param1 <> '' AND (Champ1 = 'A') OR (@param2 <> '' AND champ2 = 'B') ..   THEN  …  END

au lieu d'utiliser le SQL dynamique pour faire en sorte que la requête ne traite que le cas concerné en fonction des paramètres effectifs reçus en entrée. J'ai vu des requêtes de ce genre mettant plus 4 minutes pour s’exécuter alors qu'en les remplaçant par le SQL dynamique en obtient le même résultat en quelque millisecondes.

En fait j'ai voulu attirer l'attention sur le problème de performance des procédures générique lorsqu'on n'utilise pas le SQL dynamique.

A+

[SQLpro]

Envoyé par ValM_ENI

Là ça m'intéresse , pour quelles raison le plan d'une requête dynamique complexe est moins optimisé dans le cache ?

Parce que le cache de procédure (contenant les requêtes SQL) est sensible à la casse et tout un tas d'autres choses comme l'ordre des colonnes dans toutes les listes (claude SELECT, GROUP B Y, ORDER BY).
Donc les requêtes suivantes :

Code :

1
2
SELECT A, b
FROM   T

et

Code :

1
2
SELECT b, A
FROM   T

et enfin

Code :

1
2
SELECT a, b
FROM   T

Conduirons à 3 plans de requêtes différents.
Alors qu'avec une requête statique paramétrée, ce phénomène est rare.

A +

[ValM_ENI]

Je ne pensais pas que la position des colonnes dans la clause SELECT avait une quelconque importance.

Du coup on peut tomber dans des travers du style :
J'ai une table de 10 colonnes, l'application a besoin sur certaines pages d'avoir un retour d'infos des colonnes 1 2 3 6 7 sur d'autres 3 4 6 10 et sur d'autres seulement 2, 8. Et donc le mieux dans ce cas serait une procédure qui retourne toutes les colonnes (tjrs dans le même ordre) plutôt qu'une procédure qui propose le choix des colonnes à retourner ?

je vais devoir me relancer sur des tests cette semaine je crois

[SQLpro]

Non, car plus il y a de volume et moins rapide sera la requête.

A +

[ValM_ENI]

Merci à vous 2 pour toutes ces précisions.

Ça me conforte dans l'idée qu'il vaut mieux passer du temps sur la conception de la base de données et proposer dans la plupart des cas des procédures génériques en analysant la façon dont elles sont appelées plutôt que de créer une multitude de procédures très spécifiques qu'il faut maintenir à chaque évolution/modification/correction des applications.

Bon dimanche

[SQLpro]

ça n'est pas si simple car le générique à ses propres limites !

A +