Quels sont les avantages et les inconvénients de conserver SQL dans Procs stocké par rapport au Code [fermé]

Ceci est discuté sur quelques autres fils ici actuellement. Je suis un partisan constant des procédures stockées, bien que de bons arguments pour Linq à Sql soient présentés.

L'intégration des requêtes dans votre code vous relie étroitement à votre modèle de données. Les procédures stockées sont une bonne forme de programmation contractuelle, ce qui signifie qu'un DBA a la liberté de modifier le modèle de données et le code dans la procédure, tant que le contrat représenté par le stocké les entrées et sorties de la procédure sont maintenues.

ajuster les bases de données de production peut être extrêmement difficile lorsque les requêtes sont enfouies dans le code et non dans un emplacement central, facile à gérer.

[Edit] Voici un autre en cours de discussion

à mon avis, vous ne pouvez pas voter pour ou pour cette question. Il dépend totalement de la conception de votre application.

je vote totalement contre L'utilisation de SPs dans un environnement à 3 niveaux, où vous avez un serveur d'application devant. Dans ce genre d'environnement de votre serveur d'application est là pour exécuter votre logique métier. Si vous utilisez en outre SPs vous commencez à distribuer votre mise en œuvre de la logique d'entreprise dans tout votre système et il deviendra très difficile à comprendre qui est responsable de quoi. Finalement, vous finirez avec un serveur d'application qui ne fera rien d'autre que ce qui suit:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

ainsi, à la fin, vous avez votre niveau intermédiaire fonctionnant sur ce très cool 4 cluster serveur chacun d'entre eux équipés de 16 CPU et il ne fera en fait rien du tout! Quel gaspillage!

si vous avez un client gui fat qui se connecte directement à votre base de données ou peut-être même plus d'applications, c'est une autre histoire. Dans cette situation SPs peut servir comme une sorte de pseudo niveau moyen qui découplent votre application du modèle de données et offre un accès contrôlable.

avantages pour le Code in:

• plus facile à entretenir - pas besoin d'exécuter un script SQL pour mettre à jour les requêtes
• Port Plus Facile à un autre DB-no procs à port

en fait, je pense que vous avez ça à l'envers. À mon humble avis, SQL dans le code de la douleur à maintenir car:

• vous finissez par vous répéter dans les blocs de code connexes
• SQL N'est pas pris en charge en tant que langue dans de nombreuses IDE, donc vous avez juste une série de chaînes Non-erreur vérifiées exécutant des tâches pour vous
• les changements d'un type de données, d'un nom de table ou d'une contrainte sont beaucoup plus fréquents que l'échange d'une base de données entière pour une nouvelle
• votre niveau de difficulté augmente avec la complexité de votre requête
• et tester une requête en ligne nécessite la construction du projet

pensez aux Procs stockés comme des méthodes que vous appelez de l'objet de base de données - ils sont beaucoup plus faciles à réutiliser, il n'y a qu'un seul endroit pour éditer et dans le cas où vous changez les fournisseurs de DB, les changements se produisent dans vos Procs stockés et pas dans votre code.

cela dit, les gains de performances stockées procs est minime, car Stu a dit avant moi, et vous ne pouvez pas mettre un point d'arrêt dans une procédure stockée (encore).

CON

je trouve que faire beaucoup de traitement à l'intérieur des procédures stockées ferait de votre serveur DB un point d'inflexibilité unique, quand il s'agit de mettre à l'échelle votre acte.

Cependant en faisant tout ce crunch dans votre programme par opposition au sql-server, pourrait vous permettre de mettre à l'échelle plus si vous avez plusieurs serveurs qui exécute votre code. Bien sûr, cela ne s'applique pas aux procs stockés qui fait le fetch ou la mise à jour normale mais à ceux qui effectuent plus de traitement comme la boucle sur les ensembles de données.

PROS

"1519150920 de Performance" pour ce que ça peut être intéressant (cela évite d'interroger le traitement par le pilote de base de données / plan loisirs, etc)
1. la manipulation de données n'est pas intégrée dans le code C/C++/C# ce qui signifie que j'ai moins de code de bas niveau à regarder. SQL est moins verbeux et plus facile à consulter quand il est listé séparément.
2. grâce à la séparation les gens sont en mesure de trouver et de réutiliser le code SQL beaucoup plus facile.
3. il est plus facile de changer les choses quand le schéma change - vous avez juste à donner la même sortie au code et il fonctionnera très bien
4. plus facile à transférer dans une base de données différente.
5. je peux lister les permissions individuelles sur mes procédures stockées et contrôler l'accès à ce niveau aussi.
6. I peut profiler mon code de requête/ persistance de données séparément de mon code de transformation de données.
7. je peux mettre en œuvre des conditions variables dans ma procédure stockée et il serait facile de personnaliser sur un site client.
8. il devient plus facile d'utiliser certains outils automatisés pour convertir mon schéma et mes déclarations ensemble plutôt que quand il est intégré à mon code où je devrais les traquer.
9. assurer des pratiques exemplaires en matière de données l'accès est plus facile quand vous avez tout votre code d'accès de données à l'intérieur d'un dossier simple - je peux vérifier pour les requêtes qui accèdent à la table non performant ou ce qui utilise un niveau plus élevé de sérialisation ou select *'s dans le code etc.
10. il devient plus facile de trouver des modifications de schéma / modifications de la logique de manipulation de données lorsque tout cela est répertorié dans un fichier.
11. il devient plus facile de faire la recherche et de remplacer les modifications sur SQL quand ils sont au même endroit par exemple changer / ajouter des instructions d'isolement de transaction pour tous les procs stockés.
12. moi et le gars de DBA trouver que d'avoir un fichier SQL séparé est plus facile / commode quand le DBA doit examiner mes trucs SQL.
13. enfin, vous n'avez pas à vous soucier des attaques par injection SQL car un membre paresseux de votre équipe n'a pas utilisé de requêtes paramétrées lors de l'utilisation de sqls intégrés.

L'avantage de performance pour les procédures stockées est souvent négligeable.

plus d'avantages pour les procédures stockées:

• empêcher la rétro-ingénierie (si créé avec le cryptage, bien sûr)
• meilleure centralisation de l'accès aux bases de données
• possibilité de changer le modèle de données de manière transparente (sans devoir déployer de nouveaux clients); particulièrement pratique si plusieurs programmes accèdent au même modèle de données

je tombe sur le code côté. Nous construisons une couche d'accès aux données qui est utilisée par toutes les applications (web et client), donc elle est sèche de ce point de vue. Cela simplifie le déploiement de la base de données parce que nous devons juste nous assurer que les schémas de table sont corrects. Cela simplifie la maintenance du code car nous n'avons pas à consulter le code source et la base de données.

Je n'ai pas beaucoup de problème avec l'accouplement serré avec le modèle de données parce que je ne vois pas où il est possible de vraiment briser ce couplage. Une application et ses données sont intrinsèquement liés.

procédures stockées.

si une erreur glisse ou si la logique change un peu, vous n'avez pas à recompiler le projet. De plus, il permet l'accès à partir de différentes sources, pas seulement le seul endroit où vous avez codé la requête dans votre projet.

Je ne pense pas qu'il soit plus difficile de maintenir des procédures stockées, vous ne devriez pas les coder directement dans la base de données, mais dans les fichiers séparés d'abord, alors vous pouvez juste les exécuter sur N'importe quel DB que vous devez configurer.

avantages pour les procédures stockées :

plus facile à coder.

moins couplé, donc plus facile à tester.

plus facilement accordé.

la Performance est généralement meilleure, du point de vue du trafic réseau - si vous avez un curseur, ou similaire, alors il n'y a pas de voyages multiples à la base de données

Vous pouvez protéger l'accès aux données plus facilement, supprimer l'accès direct aux tables, renforcer la sécurité par le procs - cela vous permet également de trouver relativement rapidement n'importe quel code qui met à jour une table.

S'il y a d'autres services impliqués (tels que les services de rapport), vous pouvez trouver plus facile de stocker toute votre logique dans une procédure stockée, plutôt que dans le code, et d'avoir à la dupliquer

inconvénients:

plus difficile à gérer pour le développeurs: contrôle de version des scripts: chacun dispose-t-il de sa propre base de données, le système de contrôle de version est-il intégré à la base de données et à L'IDE?

dans certaines circonstances, sql créé dynamiquement dans le code peut avoir de meilleures performances qu'un proc stocké. Si vous avez créé un proc stocké (disons sp_customersearch) qui devient extrêmement compliqué avec des dizaines de paramètres parce qu'il doit être très flexible, vous pouvez probablement générer une déclaration sql beaucoup plus simple en code à l'exécution.

on pourrait faire valoir que cela déplace simplement un certain traitement de SQL vers le serveur web, mais en général, ce serait une bonne chose.

l'autre grande chose à propos de cette technique est que si vous regardez dans le profileur SQL, vous pouvez voir la requête que vous avez générée et la déboguer beaucoup plus facilement que de voir un appel proc stocké avec 20 Paramètres entrer.

j'aime procs stocké, ne sais pas combien de fois j'ai pu faire un changement à une application en utilisant une procédure stockée qui n'a pas produit de temps d'arrêt à l'application.

grand fan de Transact SQL, le réglage de grandes requêtes se sont avérés très utiles pour moi. Je n'ai pas écrit de SQL inline depuis environ 6 ans!

vous listez 2 pro-points pour sprocs:

Performance-pas vraiment. Dans Sql 2000 ou plus, les optimisations du plan de requête sont assez bonnes, et mises en cache. Je suis sûr que L'Oracle etc faire des choses similaires. Je ne pense pas qu'il y ait de raison pour que sprocs joue.

sécurité? Pourquoi les sproc seraient-ils plus sûrs? À moins que vous n'ayez une base de données non sécurisée de toute façon, tout l'accès sera à partir de votre Ad ou via votre application. Toujours paramétrez toutes les requêtes-Ne jamais mettre en ligne quelque chose à partir de l'entrée de l'utilisateur et vous serez très bien.

C'est la meilleure pratique pour la performance de toute façon.

Linq est certainement la façon dont je vais sur un nouveau projet en ce moment. Voir ce poste similaire .

@Keith

sécurité? Pourquoi les sproc seraient-ils plus sûrs?

comme suggéré par Komradekatz, vous pouvez refuser l'accès aux tables (pour le combo nom d'utilisateur/mot de passe qui se connecte à la base de données) et n'autoriser L'accès SP que. De cette façon, si quelqu'un obtient le nom d'utilisateur et le mot de passe de votre base de données, il peut exécuter SP mais ne peut pas accéder aux tables ou à toute autre partie de la base de données.

(bien sûr exécution sprocs peut leur fournir toutes les données dont ils ont besoin, mais cela dépend des sproc disponibles. Leur donner accès aux tables leur donne accès à tout.)

Pensez-y de cette façon

vous avez 4 serveurs web et un tas d'Applications windows qui utilisent le même code SQL Maintenant vous réalisez qu'il y a un petit problème avec le code SQl donc, ne vous plutôt...... changer le proc en 1 lieu ou poussez le code pour tous les serveurs, la réinstallation de toutes les applications de bureau(clickonce peut aider) sur toutes les fenêtres des boîtes

je préfère stockées procs

il est également plus facile de faire des tests de performance contre un proc, le mettre dans l'analyseur de requêtes set statistics io/heure sur la set showplan_text et voilà

pas besoin de courir profiler pour voir exactement ce qui est appelé

just my 2 cents

je préfère les garder dans le code (en utilisant un ORM, pas en ligne ou ad-hoc) de sorte qu'ils sont couverts par le contrôle source sans avoir à gérer l'économie .les fichiers sql.

de plus, les procédures stockées ne sont pas intrinsèquement plus sûres. Vous pouvez écrire une mauvaise requête avec une procédure stockée tout aussi facilement en ligne. Les requêtes paramétrées en ligne peuvent être tout aussi sécurisées qu'un sproc.

utilisez votre code app comme ce qu'il fait le mieux: gérer la logique.

Utilisez votre base de données pour ce qu'elle fait le mieux: stocker des données.

vous pouvez déboguer les procédures stockées, mais vous trouverez plus facile de déboguer et de maintenir la logique dans le code. Habituellement, vous cesserez de recompiler votre code à chaque fois que vous changez le modèle de base de données.

procédures également stockées avec des paramètres de recherche optionnels sont très inneficient parce que vous devez spécifier à l'avance tous les paramètres possibles et les recherches complexes sont parfois impossibles parce que vous ne pouvez pas prédire combien de fois un paramètre va être répété dans la zone.

en matière de sécurité, les procédures stockées sont beaucoup plus sûres. Certains ont fait valoir que tout l'accès se fera de toute façon par le biais de la demande. Ce que beaucoup de gens oublient, c'est que la plupart des infractions à la sécurité proviennent de l'intérieur d'une entreprise. Pensez à combien de développeurs connaissent le nom d'utilisateur et le mot de passe" cachés " pour votre application?

en outre, comme MatthieuF a souligné, la performance peut être considérablement améliorée en raison de moins de voyages aller-retour entre l'application (si c'est sur un ordinateur de bureau ou serveur web) et le serveur de base de données.

d'après mon expérience, l'abstraction du modèle de données par le biais de procédures stockées améliore également considérablement la maintenabilité. Comme quelqu'un qui a dû maintenir de nombreuses bases de données dans le passé, c'est un tel soulagement quand il est confronté à un changement de modèle requis pour être en mesure de simplement changer une ou deux procédures stockées et avoir le changement être complètement transparent à toutes les applications externes. Souvent votre application n'est pas le seul à pointer vers une base de données - il y a d'autres applications, des solutions de rapports, etc. donc traquer tous ces points touchés peut être un tracas avec un accès libre aux tables.

je vais également mettre des vérifications dans la colonne plus pour mettre la programmation SQL dans les mains de ceux qui se spécialisent en elle, et pour SPs le rendant beaucoup plus facile à isoler et tester/optimiser le code.

le seul inconvénient que je vois est que beaucoup de langues ne permettent pas la passer des paramètres de table, donc passer un nombre inconnu de valeurs de données peut être ennuyeux, et certaines langues ne peuvent toujours pas gérer la récupération de plusieurs ensembles de résultats d'une seule procédure stockée (bien que cette dernière ne rend pas SPs plus mauvais que inline SQL à cet égard).

une des suggestions D'une session Microsoft TechEd sur la sécurité à laquelle j'ai assisté, pour faire tous les appels à travers des procs stockés et de refuser l'accès directement aux tables. Cette approche a été facturée comme offrant une sécurité supplémentaire. Je ne suis pas sûr que ça en vaille la peine juste pour la sécurité, mais si vous utilisez déjà des procs stockés, ça ne peut pas faire de mal.

Certainement plus facile à maintenir si vous le mettez dans une procédure stockée. S'il y a une logique difficile qui pourrait changer à l'avenir, c'est certainement une bonne idée de la mettre dans la base de données lorsque vous avez plusieurs clients connectés. Par exemple, je travaille actuellement sur une application qui a une interface web utilisateur final et une application de Bureau administratif, qui partagent une base de données (évidemment) et j'essaie de garder autant de logique sur la base de données que possible. Ceci est un exemple parfait du dry principle .

je suis fermement du côté des procs stockés en supposant que vous ne trichez pas et utilisez le SQL dynamique dans le proc stocké. Tout d'abord, l'utilisation de procs stocké permet au dba de définir les permissions au niveau de proc stocké et non au niveau de la table. Cela est essentiel non seulement pour combattre les attaques par injection SQL, mais aussi pour empêcher les initiés d'accéder directement à la base de données et de changer les choses. C'est une façon de prévenir la fraude. Pas de base de données contenant des informations personnelles (SSNs, numéros de carte de crédit, etc.) ou cela crée de toute façon des transactions financières devraient jamais être accessibles sauf par des procédures strored. Si vous utilisez une autre méthode, vous laissez votre base de données ouverte aux personnes de l'entreprise pour créer de fausses transactions financières ou voler des données qui peuvent être utilisées pour le vol d'identité.

procs stockés sont également beaucoup plus facile à maintenir et performance tune que SQL envoyé de l'application. Ils permettent également au dba un moyen de voir ce que l'impact d'un changement structurel de base de données aura sur la façon dont les données sont accessibles. Je n'ai jamais rencontré un bon dba qui permettrait un accès dynamique à la base de données.

nous utilisons des procédures stockées avec Oracle DB où je travaille maintenant. Nous utilisons aussi Subversion. Toutes les procédures stockées sont créées sous .pkb & .fichiers pks et sauvegardé dans Subversion. J'ai déjà fait de la SQL en ligne et ça fait mal! Je préfère de beaucoup la manière dont nous le faisons ici. Créer et tester de nouvelles procédures stockées est beaucoup plus facile que de le faire dans votre code.

Thérèse

Petites billes

un autre pro mineur pour les procédures stockées qui n'a pas été mentionné: quand il s'agit de trafic SQL, l'accès aux données basé sur sp génère beaucoup moins de trafic. Cela devient important lorsque vous surveillez le trafic pour l'analyse et le profilage - les journaux seront beaucoup plus petits et lisibles.

Je ne suis pas un grand fan des procédures stockées, mais je les utilise dans une condition:

quand la requête est assez énorme, il est préférable de la stocker dans la base de données comme une procédure stockée au lieu de l'Envoyer à partir du code. De cette façon, au lieu d'envoyer d'énormes quantités de caractères de chaîne du serveur d'application à la base de données, seule la commande "EXEC SPNAME" sera envoyée.

C'est exagéré quand le serveur de base de données et le serveur web ne sont pas sur le même réseau (par exemple, communication internet). Et même si ce n'est pas le cas, trop de stress signifie beaucoup de gaspillage de bande passante.

mais mec, ils sont si terribles à gérer. - Je les éviter autant que je le peux.

en SQL, procédures stockées, n'augmente pas les performances de la requête

bien évidemment en utilisant des procédures stockées a plusieurs avantages par rapport à la construction de code SQL.

1. votre implémentation de code et SQL deviennent indépendants l'un de l'autre.
Le Code
2. est plus facile à lire.
3. écrire une fois utiliser plusieurs fois.
4. modifier une fois
5. pas besoin de donner des détails internes au programmeur sur la base de données. etc, etc.

procédures stockées sont plus maintenable because:

• Vous n'avez pas à recompiler votre application en C# lorsque vous voulez modifier certains SQL
• vous finissez par réutiliser le code SQL.

la répétition de Code est la pire chose que vous pouvez faire quand vous essayez de construire une application maintenable!

que se passe-t-il lorsque vous trouvez une erreur de logique qui doit être corrigée en plusieurs endroits? Vous êtes plus susceptible d'oublier de changer le dernier endroit où vous copiez et collez votre code.

à mon avis, les gains de performance et de sécurité sont un plus. vous pouvez encore écrire des procédures stockées SQL non sécurisées/inefficaces.

Port Plus Facile à un autre DB-no procs à port

ce n'est pas très difficile d'écrire tous vos procédures stockées pour la création dans un autre DB. En fait - il est plus facile que l'exportation de vos tables parce qu'il n'y a pas de clés primaires/étrangères à se soucier.

@Terrapin-les sproc sont tout aussi vulnérables aux attaques par injection. Comme je l'ai dit:

paramétrez toujours toutes les requêtes - jamais en ligne quelque chose à partir de l'entrée de l'utilisateur et vous serez très bien.

qui va pour sprocs et dynamic Sql.

je ne suis pas sûr de ne pas recompiler votre application est un avantage. Je veux dire, vous avez exécuté vos tests unitaires contre ce code (à la fois l'application et la base de données) avant d'y retourner de toute façon.

@Guy-oui vous avez raison, sprocs ne vous permettent de contrôler les utilisateurs de l'application de sorte qu'ils ne peuvent effectuer le sproc, pas l'action sous-jacente.

ma question serait: si tous l'accès il par votre application, en utilisant des connexions et les utilisateurs avec des droits limités pour mettre à jour / insérer etc, est-ce que ce niveau supplémentaire ajoute la sécurité ou l'administration supplémentaire?

Mon avis est très bien le dernier. Si ils ont compromis votre application au point où ils peuvent ré-écrire ils ont beaucoup d'autres attaques qu'ils peuvent utiliser.

les injections Sql peuvent toujours être effectuées contre ces sproc s'ils dynamiquement code en ligne, donc la règle d'or s'applique toujours, toutes les entrées utilisateur doit toujours être paramétré.

quelque chose que je n'ai pas vu mentionné jusqu'à présent: les gens qui connaissent le mieux la base de données ne sont pas toujours les gens qui écrivent le code d'application. Les procédures stockées donnent aux gens de la base de données un moyen d'interface avec les programmeurs qui ne veulent pas vraiment apprendre beaucoup sur SQL. Les grandes bases de données, et surtout les anciennes, ne sont pas les choses les plus faciles à comprendre, donc les programmeurs préféreront peut-être une interface simple qui leur donne ce dont ils ont besoin: rejoignez les 17 tables pour y arriver.

cela dit, les langues utilisées pour écrire les procédures stockées (PL/SQL étant un exemple notoire) sont assez brutales. Ils n'offrent généralement aucune des subtilités que vous verriez dans l'impératif populaire d'aujourd'hui, OOP, ou les langues fonctionnelles. Pense à COBOL.

ainsi, s'en tenir aux procédures stockées qui ne font qu'abstraire les détails relationnels plutôt que ceux qui contiennent la logique d'affaires.

j'écris généralement un code OO. Je suppose que la plupart d'entre vous probablement ne, trop. Dans ce contexte, il me semble évident que toute la logique commerciale - y compris les requêtes SQL - fait partie des définitions de classe. Diviser la logique de telle sorte qu'une partie de celle-ci réside dans le modèle d'objet et une partie est dans la base de données n'est pas mieux que de mettre la logique des affaires dans l'interface utilisateur.

beaucoup a été dit dans les réponses précédentes sur les avantages de sécurité de procs stockés. Elles se répartissent en deux grandes catégories:

1) restreindre L'accès direct aux données. Cela est certainement important dans certains cas et, lorsque vous rencontrez un, puis stockées procs sont à peu près votre seule option. Dans mon expérience, ces cas sont l'exception plutôt que la règle, cependant.

2) injection SQL / requêtes paramétrées. Cette objection est une diversion. Inline SQL-même généré dynamiquement inline SQL-peut être tout aussi pleinement paramétrées comme toute procédure stockée et il peut être fait tout aussi facilement dans n'importe quelle langue moderne qui vaut son sel. Il n'y a aucun avantage de toute façon ici. ("Paresseux développeurs pourrait ne pas s'embêter avec l'aide des paramètres" n'est pas une objection valable. Si vous avez des développeurs dans votre équipe qui préfèrent simplement concaténer les données de l'utilisateur dans leur SQL au lieu d'utiliser des paramètres, vous essayez d'abord de les éduquer, puis vous les virez si cela ne fonctionne pas, tout comme vous le feriez avec les développeurs qui ont n'importe quel autre mauvais, demonstrably préjudiciable habitude.)

je suis un grand partisan du code sur les SPROC. La raison numéro un est de garder le code étroitement couplé, puis une seconde proche est la facilité de contrôle des sources sans beaucoup d'Utilités personnalisées pour le tirer dedans.

dans notre DAL si nous avons des instructions SQL très complexes, nous les incluons généralement en tant que fichiers de ressources et les mettons à jour au besoin (cela pourrait être aussi un assemblage séparé, et échangé par db, etc...).

cela garde notre code et notre appels sql stockés dans le même contrôle de version, sans "oublier" d'exécuter des applications externes pour la mise à jour.