IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Décisions SGBD Discussion :

Procédures stockées - bonnes pratiques


Sujet :

Décisions SGBD

  1. #1
    Membre averti

    Profil pro
    En reconversion
    Inscrit en
    Novembre 2007
    Messages
    180
    Détails du profil
    Informations personnelles :
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : En reconversion

    Informations forums :
    Inscription : Novembre 2007
    Messages : 180
    Points : 351
    Points
    351
    Par défaut Procédures stockées - bonnes pratiques
    Bonjour,

    Il me semble que les procédures stockées sont rarement utilisées dans le fonctionnement d'applications métiers ou même pour des sites web d'ailleurs. Je me demande bien pourquoi ?

    Est ce par souci de simplification, pour permettre un changement de sgdb plus facilement ou tout simplement à cause de la généralisation des ORM ...

    Je crois pourtant avoir compris que c'était un moyen d'optimisation, et puis une sécurité bien plus grande en terme d'accès.
    Pour ces raisons là, et comme j'aime bien "jouer" avec les bases de données, j'ai plutôt envie pour mon développement (application métiers) d'oublier l'utilisation d'ORM et de gérer moi même cette couche en utilisant des procédures stockées et une gestion fine des droits dans la base, en plus de l'application.

    Autre questionnement, mais en rapport, pensez vous que lorsqu'on peut gérer une partie du développement directement dans la base (enfin dans la procédure stockée), il est préférable de faire ainsi.
    Par exemple, la pagination d'un tableau d'une page HTML. Vous iriez plutôt faire une requête pour chaque page avec des bornes pour les lignes à retourner, ou bien vous iriez plutôt retourner tout et gérer la pagination par exemple en javascript avec un fichier json directement du côté client ?

    Le développement est en python du coté serveur avec une base PostgreSQL. En dehors de l'argument du temps passé, qu'en pensez vous ?

    Merci par avance pour vos avis sur la question.

  2. #2
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Bonjour,

    Sans savoir vous donner la source, il est dit par un grand homme que lorsqu'on accède à une base de données depuis une application, on ne doit jamais accéder directement aux tables, ni en lecture, ni en modification.

    De ce fait, il en découle un certain nombre d'éléments :
    - pour la lecture des données, on doit impérativement passer par des procédures, fonctions ou vues
    - pour la modification des données, on doit impérativement passer par des procédures ou vues + triggers

    Vous avez donc tout compris en décidant de vous lancer dans le développement de la couche "accès aux données" directement en procédures stockées plutôt qu'avec des couches contre-performantes et instables (gestion hasardeuse des transactions, etc.) côté applicatif.

    Vous y gagnerez :
    - en lisibilité du code de l'application métier (dont le but n'est pas de dégueuler du SQL généré automatiquement par un framework contre-performant, mais de mettre en forme l'interface graphique et les règles métiers "applicatives")
    - en stabilité : vous pouvez travailler de manière ensembliste dans une procédure stockée, gérer plus finement les transactions, etc.
    - en performance : avec les couches merdiques actuelles, on charge généralement 90% d'information inutile, et on déclenche 2000 requêtes dans une boucle plutôt que de faire une simple jointure. Tous ces échanges et exécutions multiples de requêtes prennent beaucoup de temps, et sont évitées par l'utilisation d'objets directement dans la base de données

    Attention lorsque vous désirerez faire du CRUD : n'hésitez pas à encapsuler vos procédures stockées dans des vues avec triggers. Ceci simplifiera encore plus votre code.

    Autre immense avantage de cette méthode : si un jour votre modèle des données change, l'impact sera absolument minime pour votre application.
    http://www.developpez.net/forums/blo...-base-donnees/
    On ne jouit bien que de ce qu’on partage.

  3. #3
    Membre averti

    Profil pro
    En reconversion
    Inscrit en
    Novembre 2007
    Messages
    180
    Détails du profil
    Informations personnelles :
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : En reconversion

    Informations forums :
    Inscription : Novembre 2007
    Messages : 180
    Points : 351
    Points
    351
    Par défaut
    Merci pour votre avis, parfois il est bien de s'assurer qu'on ne croit pas à des sottises, même quand beaucoup de personnes le disent (ce n'est pas toujours le plus grand nombre qui a raison).

    Par contre, j'ai toujours fait en sorte de ne pas utiliser les triggers, a priori ils alourdissent les traitements. Ensuite tout dépend de leur utilisation. Je les ai vu par exemple utilisé pour ajouter des lignes dans une table de logs (personnellement j'aurai fait cela dans le code de l'application). C'est bien quand on joue sur une ligne ou deux, mais quand on commence à attaquer des dizaines, voir des centaines de milliers de lignes, cela ne va plus du tout. Bon d'accord cela concernait des requêtes exceptionnelles. Il faut y réfléchir à l'avance quand même. Imaginons un fonction d'archivage par exemple. Sous certaines bases, je crois qu'il est possible de désactiver les triggers, tout comme les contraintes d'intégrité, mais cela reste délicat à utiliser, il faut verrouiller les tables concernés.

    J'avoue que ne pas savoir ce que fait un framework derrière le code m'énerve un peu. J'aime bien comprendre. On sort du thème SGBD, mais par exemple j'ai regardé le framework python Django, on peut carrément créer le modèle de données à partir du code grace à l'ORM. Ce n'est pas dans mes principes. Je travaille sur le modèle, puis je crée la base et enfin le code de l'application et son front, et non pas dans l'autre sens.
    Cela vient surement du fait que j'ai dû faire du sql 95% de mon temps et du code 5% depuis toujours.

  4. #4
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Attention, il y a trigger et trigger, et l'utilisation qu'on en fait, mais aussi surtout la manière dont ils sont écrits.

    Je ne connais pas beaucoup PostgreSQL, donc je ne sais pas trop comment ça marche dans votre cas.

    Mais pour avoir une expérience avec des triggers avec Oracle et SQL Server, on voit qu'il existe deux écoles :
    - Le "for each row", ou utilisation d'un curseur
    - L'ensembliste (on traite l'ensemble des lignes affectées par le trigger en une seule fois)

    Avec Oracle, j'ai toujours fais du "for each row", et c'est une catastrophe.
    Avec SQL Server, on est par défaut avec de l'ensembliste, et là, ça va bien plus vite.

    En effet, dans un cas on va parcourir ligne à ligne chaque élément mis à jour, puis exécuter des requêtes pour chaque ligne.
    => Si on veut par exemple recalculer le total des commandes dont on met à jour en masse 20 000 lignes, on va se taper 20 000 fois le recalcul du total des commandes correspondant à chaque ligne mise à jour.

    Dans l'autre cas, on va simplement exécuter une unique requête qui recalcule une et une seule fois chaque entête de commande dont au moins une ligne a été mise à jour dans la transaction.
    => Ça change tout...

    De la même manière, beaucoup de triggers sont souvent "mamouth", à savoir qu'ils font beaucoup trop de choses : un trigger à pour vocation de garantir l'intégrité des données.
    => Il ne doit donc pas se substituer à d'autres types de contraintes (unique, check, foreign key, etc.)
    => Il ne doit pas contenir de code "métier" (mise en forme, calculs, etc.) Par exemple, le coup du calcul des entête de commande ne doit pas se faire par trigger. On leur préfèrera une vue ou une colonne calculée.

    Au final, pour illustrer les triggers de mon exemple, ils ne servent qu'à répartir les données d'un CRUD effectué sur une table dans les tables utilisées par la vue.
    => En effet, quand une vue fait une jointure entre deux tables, on ne peut plus faire de CRUD dessus sans la présence de trigger.
    On ne jouit bien que de ce qu’on partage.

  5. #5
    Membre averti

    Profil pro
    En reconversion
    Inscrit en
    Novembre 2007
    Messages
    180
    Détails du profil
    Informations personnelles :
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : En reconversion

    Informations forums :
    Inscription : Novembre 2007
    Messages : 180
    Points : 351
    Points
    351
    Par défaut
    Bonjour,

    Je ne savais pas que SQL Server pouvait traiter les triggers en bloc.

    Je n'ai jamais implémenté moi même les triggers donc je ne saurai dire comment c'était fait, mais par contre je les ai déjà subi lors de requête pour nettoyer les bases notamment, et là c'était problèmatique (sous SQL Server 2008 R2).

    Je ne connais pas encore PostgreSQL, ayant toujours travaillé sur MS SQL et à peine sur Mysql, je verrai bien à l'usage, j'ai assez confiance en ce SGBD.

    Merci

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 448
    Points
    52 448
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par patic Voir le message
    Bonjour,

    Il me semble que les procédures stockées sont rarement utilisées dans le fonctionnement d'applications métiers ou même pour des sites web d'ailleurs. Je me demande bien pourquoi ?
    D’où sortez vous cette affirmation ? Il est vrai que pour des petits sites perso réalisé par des embryons d'outil comme Joomla ou Prestashop la notion de procédure stockée est quasiment inconnue parce qu'à l'origine ces brimborions ont été développé pour MySQmerde qui ne les supportait pas !
    Mais sur tous les gros sites web professionnel comme CDISCOUNT, FNAC.COM, VentesPrivées... tout est fait sous forme de procédures stockées !

    Est ce par souci de simplification, pour permettre un changement de sgdb plus facilement ou tout simplement à cause de la généralisation des ORM ...
    Changer de SGBDR esr toujours une aventure complexe car aucun n'a un niveau de SQL équivalent et donc la plupart des requêtes SQL doivent être récrites...

    Je crois pourtant avoir compris que c'était un moyen d'optimisation, et puis une sécurité bien plus grande en terme d'accès.
    Ho que oui !
    Pour ces raisons là, et comme j'aime bien "jouer" avec les bases de données, j'ai plutôt envie pour mon développement (application métiers) d'oublier l'utilisation d'ORM et de gérer moi même cette couche en utilisant des procédures stockées et une gestion fine des droits dans la base, en plus de l'application.

    Autre questionnement, mais en rapport, pensez vous que lorsqu'on peut gérer une partie du développement directement dans la base (enfin dans la procédure stockée), il est préférable de faire ainsi.
    Par exemple, la pagination d'un tableau d'une page HTML. Vous iriez plutôt faire une requête pour chaque page avec des bornes pour les lignes à retourner, ou bien vous iriez plutôt retourner tout et gérer la pagination par exemple en javascript avec un fichier json directement du côté client ?
    Ni l'un ni l'autre. Le mieux est de créer une table pseudo temporaire afin de cacher l'int"égralité des résultats à paginer côté serveur en ne stockant que les clefs, les pages, les id et les sessions concernées.

    Le développement est en python du coté serveur avec une base PostgreSQL. En dehors de l'argument du temps passé, qu'en pensez vous ?

    Merci par avance pour vos avis sur la question.

    C'est plutôt hyper efficace et réellement plus rapide une fois que l'on s'est formé et que le respecte certaines règles, notamment nommage et généricité.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 448
    Points
    52 448
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par patic Voir le message
    Par contre, j'ai toujours fait en sorte de ne pas utiliser les triggers, a priori ils alourdissent les traitements.
    La question n'est pas d'utiliser ou pas les déclencheurs, mais simplment de les utiliser à bon escient. En effet s'ils ont existé il y a une raison et heureusement que les éditeurs les ont inventés car s'ils avaient suivi la norme SQL cela aurait sans doute été pire !
    En effet, l'invention du trigger par Sybase en 1986 reposait sur la complexité de créer des contraintes multitables. Le relationnel induisait trois types de contraintes : les contraintes de domaines qui valident les données atomiques, les contraintes de tables (clef, primaire, unicité, clef étrangères, validation) et les assertions capable de vérifier des ensembles de données venant de plusieurs tables. Pour ces dernières (ordre SQL CREATE ASSERTION ...) le but est de vérifier pour chaque table et chaque mise à jour les données respectent les règles. Un petit exemple peut être donné dans le cas suivant :
    Supposons que nous avons deux tables d'entreprises, une de prospects et l'autre de clients, toutes deux possédant une colonne avec le n° de SIRET. Nous devons faire en sorte qu'une entreprise soit présente dans l'une ou l'autre, mais jamais dans les deux. Comment faire ? L'assertion suivante permet de garantir cette règle :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    CREATE ASSERTION A_SIRET
    AS
    CHECK NOT EXISTS(SELECT *
                     FROM   PROPSPECT AS P
                            JOIN CLIENT AS C
                                 ON P.SIRET = C.SIRET);
    Une telle assertion sera donc vérifiée lors des commandes suivantes :
    • INSERT sur CLIENT
    • INSERT sur PROSPECT
    • UPDATE sur CLIENT
    • UPDATE sur PROSPECT
    • DELETE sur CLIENT
    • DELETE sur PROSPECT


    Or à l'évidence, nous n'avons pas besoin de la vérifier sur DELETE ! Un trigger sera donc plus efficace puisqu'on peut lui dire de ne pas la vérifier sur DELETE.

    D'autre part s'il y a 10 000 prospects et 100 000 clients, la jointure dans l'opérateur EXISTS va produire une correspondance potentielle de 10 000 x 100 000 = > 1 000 000 000 !

    Encore une fois le déclencheur sera beaucoup plus fin car :
    • lors de l'insertion on limitera la vérification aux nouvelles valeurs seulement;
    • lors de la modification et uniquement si la colonne SIRET a été modifiée, on limitera la vérification aux nouvelles valeurs seulement !

    Que d'économie réalisé par le trigger !!!

    Et il n'est bien entendu pas possible de faire autrement...

    Ensuite tout dépend de leur utilisation. Je les ai vu par exemple utilisé pour ajouter des lignes dans une table de logs (personnellement j'aurai fait cela dans le code de l'application).
    Cela n'est pas toujours judicieux. Dans le cas applicatif d'une UPDATE vous ne pouvez pas savoir quelles lignes ont été impactées. Le recours au pseudo tables (new et old dans la norme ou inserted et deleted dans SQL Server) permet de savoir avec précision quelle valeurs étaient avant et après. Cela peut être plus que fondamental, quand par exemple dans un hôpital on envoie 1000 fois la dose de radiation d'une bombe au cobalt par erreur sur un patient atteint du cancer et qu'on le tue !

    C'est bien quand on joue sur une ligne ou deux, mais quand on commence à attaquer des dizaines, voir des centaines de milliers de lignes, cela ne va plus du tout. Bon d'accord cela concernait des requêtes exceptionnelles. Il faut y réfléchir à l'avance quand même. Imaginons un fonction d'archivage par exemple.
    Il y a des outils dans les bons SGBDR pour ça, par exemple sous SQL Server : Change Tacking, Change Data Capture, Database Audit et les Temporal Tables. Tout cet attirail est moins pénalisant que des triggers !

    Sous certaines bases, je crois qu'il est possible de désactiver les triggers, tout comme les contraintes d'intégrité, mais cela reste délicat à utiliser, il faut verrouiller les tables concernés.
    Ou mais ça reste intéressant pour des traitements en mode "batch". Et dans certaines cas c'est automatique, par exemple dans les imports par BCP.exe ou BULK INSERT dans SQL Server. Attention toutefois à bien revérifier les contraintes après coup, car sinon elles ne sont pas "trusted" ce qui peut conduire à des plans de requêtes moins performant

    J'avoue que ne pas savoir ce que fait un framework derrière le code m'énerve un peu. J'aime bien comprendre. On sort du thème SGBD, mais par exemple j'ai regardé le framework python Django, on peut carrément créer le modèle de données à partir du code grace à l'ORM. Ce n'est pas dans mes principes. Je travaille sur le modèle, puis je crée la base et enfin le code de l'application et son front, et non pas dans l'autre sens.
    Cela vient surement du fait que j'ai dû faire du sql 95% de mon temps et du code 5% depuis toujours.
    Le modèle fait 80% des performances. Le respect des formes normale, des types de données et des principes de modélisation est plus qu'important. il est primordial.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Membre averti

    Profil pro
    En reconversion
    Inscrit en
    Novembre 2007
    Messages
    180
    Détails du profil
    Informations personnelles :
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : En reconversion

    Informations forums :
    Inscription : Novembre 2007
    Messages : 180
    Points : 351
    Points
    351
    Par défaut
    Merci pour votre avis, notamment l'idée de table temporaire pour la pagination, je creuserai l'idée.

    Je retiens donc sans aucun doute que l'utilisation de procédures stockées est préférable et que le report de fonctions (souvent des contraintes) dans la base de données est généralement plus performant.

    Merci encore à vous deux, je clos le post.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. passage d'un nom de table dans une procédure stockée
    Par thierry V dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 26/07/2010, 17h48
  2. [Procédures stockées] Bonnes pratiques de gestion des erreurs
    Par jbrasselet dans le forum Développement
    Réponses: 4
    Dernier message: 04/02/2009, 01h14
  3. [Pervasive SQL ] procédure stockée
    Par magellan dans le forum Autres SGBD
    Réponses: 2
    Dernier message: 25/10/2002, 14h17
  4. Explication procédure stockée
    Par underworld dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 09/09/2002, 11h51
  5. [Comparatif] Procédures stockées, triggers, etc.
    Par MCZz dans le forum Décisions SGBD
    Réponses: 3
    Dernier message: 28/08/2002, 13h27

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo