NOTE qu'il s'agit d'un regard général sur les procédures stockées qui ne sont pas régies par un SGBD. Certains SGBD (et même, différentes versions d'un même SGBD !) peuvent fonctionner contrairement à ce qui est indiqué, vous devrez donc vérifier avec votre SGBD cible cible avant de supposer que tout ceci est toujours valable.
J'ai été DBA Sybase ASE, MySQL et SQL Server de façon intermittente pendant près de dix ans (parallèlement au développement d'applications en C, PHP, PL/SQL, C#.NET et Ruby). Je n'ai donc aucune raison particulière de m'opposer à cette guerre (parfois) sainte.
Les avantages historiques des procs stockés en termes de performances proviennent généralement des éléments suivants (sans ordre particulier) :
- SQL pré-paramétré
- Plan d'exécution de la requête pré-généré
- Réduction de la latence du réseau
- Avantages potentiels du cache
SQL pré-paramétré -- avantages similaires à ceux du code compilé par rapport au code interprété, sauf à un niveau très micro.
C'est toujours un avantage ? Ce n'est pas du tout perceptible sur un processeur moderne, mais si vous envoyez une seule instruction SQL qui est TRES grande onze cent milliards de fois par seconde, la surcharge d'analyse peut s'accumuler.
Plan d'exécution de la requête pré-généré . Si vous avez de nombreux JOIN, les permutations peuvent devenir ingérables (les optimiseurs modernes ont des limites et des seuils pour des raisons de performance). Il n'est pas rare que des requêtes SQL très compliquées aient des latences distinctes et mesurables (j'ai vu une requête compliquée prendre plus de 10 secondes juste pour générer un plan, avant que nous ne modifiions le SGBD) en raison de l'optimiseur qui essaie de trouver le "meilleur" plan d'exécution. Les procédures stockées stockent généralement ces informations en mémoire, ce qui permet d'éviter cette surcharge.
C'est toujours un avantage ? La plupart des SGBD (les dernières éditions) mettent en cache les plans de requête pour les instructions SQL INDIVIDUELLES, ce qui réduit considérablement la différence de performance entre les procs stockés et le SQL ad hoc. Il existe quelques réserves et cas où ce n'est pas le cas, vous devrez donc effectuer des tests sur votre SGBD cible.
En outre, de plus en plus de SGBD vous permettent de fournir des plans de chemin d'accès à l'optimiseur (plans de requête abstraits) afin de réduire de manière significative le temps d'optimisation (pour le SQL ad hoc et les procédures stockées ! !).
AVERTISSEMENT Les plans de requête en cache ne sont pas une panacée en matière de performances. Il arrive que le plan de requête généré soit sous-optimal. Par exemple, si vous envoyez SELECT * FROM table WHERE id BETWEEN 1 AND 99999999 le SGBD peut sélectionner un un balayage complet de la table au lieu d'un d'index parce que vous saisissez chaque ligne de la table (c'est ce que disent les statistiques). Si c'est la version en cache vous pouvez obtenir de mauvaises performances lorsque vous envoyez ensuite SELECT * FROM table WHERE id BETWEEN 1 AND 2 . Le raisonnement derrière cela est en dehors du cadre de cet article, mais pour une lecture plus approfondie, voir : http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx et http://msdn.microsoft.com/en-us/library/ms181055.aspx et http://www.simple-talk.com/sql/performance/execution-plan-basics/
"En résumé, ils ont déterminé que fournir autre chose que les valeurs communes lors d'une compile ou recompile était effectuée, l'optimiseur l'optimiseur compile et met en cache le plan de requête pour cette valeur particulière particulière. Cependant, lorsque ce plan de requête était réutilisé pour des exécutions ultérieures de de la même requête pour les valeurs communes ('M', 'R', ou 'T'), les performances n'étaient pas performance sous-optimale. Cette problème de performance sous-optimale jusqu'à ce que la requête soit recompilée. À ce moment-là, en fonction de la valeur du paramètre @P1 fournie, la requête pouvait ou non avoir un problème de problème de performance".
Réduction de la latence du réseau A) Si vous exécutez le même SQL encore et encore - et que ce SQL représente plusieurs Ko de code - remplacer ce code par un simple "exec foobar" peut vraiment faire grimper la facture. B) Les procs stockés peuvent être utilisés pour déplacer le code procédural dans le SGBD. Cela évite de transférer de grandes quantités de données vers le client pour qu'il ne renvoie qu'un filet d'informations (ou rien du tout !). C'est un peu comme faire un JOIN dans le SGBD plutôt que dans votre code (le WTF préféré de tout le monde !).
C'est toujours un avantage ? A) Les réseaux Ethernet modernes de 1Gb (et 10Gb et plus !) rendent cet aspect négligeable. B) Cela dépend de la saturation de votre réseau - pourquoi envoyer plusieurs mégaoctets de données dans les deux sens sans raison valable ?
Avantages potentiels du cache L'exécution de transformations de données côté serveur peut potentiellement être plus rapide si la mémoire du SGBD est suffisante et si les données dont vous avez besoin se trouvent dans la mémoire du serveur.
C'est toujours un avantage ? À moins que votre application ne dispose d'un accès en mémoire partagée aux données du SGBD, les procs stockés auront toujours l'avantage.
Bien sûr, aucune discussion sur l'optimisation des procédures stockées ne serait complète sans une discussion sur le SQL paramétré et ad hoc.
SQL paramétré/préparé
Sorte de croisement entre les procédures stockées et le SQL ad hoc, il s'agit d'instructions SQL intégrées dans un langage hôte qui utilise des "paramètres" pour les valeurs des requêtes, par exemple :
SELECT .. FROM yourtable WHERE foo = ? AND bar = ?
Elles fournissent une version plus généralisée d'une requête que les optimiseurs modernes peuvent utiliser pour mettre en cache (et réutiliser) le plan d'exécution de la requête, ce qui se traduit par une grande partie des avantages des procédures stockées en termes de performances.
Ad Hoc SQL Il suffit d'ouvrir une fenêtre de console vers votre SGBD et de taper une instruction SQL. Dans le passé, ces requêtes étaient les moins performantes (en moyenne), car le SGBD n'avait aucun moyen de pré-optimiser les requêtes comme dans la méthode des procs paramétrés/stockés.
C'est toujours un désavantage ? Pas nécessairement. La plupart des SGBD ont la capacité d'"abstraire" le SQL ad hoc en versions paramétrées - ce qui annule plus ou moins la différence entre les deux. Certains le font implicitement ou doivent être activés par un paramètre de commande (SQL server : http://msdn.microsoft.com/en-us/library/ms175037.aspx , Oracle : http://www.praetoriate.com/oracle_tips_cursor_sharing.htm ).
Les leçons apprises ? La loi de Moore continue de progresser et les optimiseurs de SGBD, à chaque version, deviennent plus sophistiqués. Bien sûr, vous pouvez placer chaque petite instruction SQL dans une procédure stockée, mais sachez que les programmeurs qui travaillent sur les optimiseurs sont très intelligents et cherchent continuellement des moyens d'améliorer les performances. À terme (si ce n'est pas déjà le cas), les performances du SQL ad hoc deviendront indiscernables (en moyenne !) de celles des procédures stockées. massif L'utilisation d'une procédure stockée ** uniquement pour des "raisons de performance "** me semble être une optimisation prématurée.
Quoi qu'il en soit, je pense que si vous évitez les cas limites et que vous avez un SQL assez classique, vous ne remarquerez pas de différence entre les procédures ad hoc et les procédures stockées.
