Forum Programmation.SQL Procédure stockées

Posté par .
Tags : aucun
2
10
fév.
2010
C'est avec quelques années de retard que je découvre les procédures stockées et les trigger (bouu).
Je voulais avoir quelques retours d'expérience de ceux qui sont passé des requêtes 1 à une vers les procédure/fonction stockées.
J'ai pas encore pu mettre ça en pratique mais c'est mon prochain objectif, en tous cas dans la théorie ça à l'air bien.
Pouvoir "séparer" les requête sql du code j'aime beaucoup l'idée, si on change de langage pour travailler sur la même base de données ça doit faire gagner beaucoup de temps.
Aussi j'imagine que comme les procédures sont compilé il doit y avoir un bon gain de performance comparé aux traitement que je fait pour le moment en PHP.
Je compte utilisé ça avec php/MySQL quelqu'un sait ce que ça vaut sur avec MySQL, sinon je firebird m'a l'air sympa mais malheureusement les hébergeurs ne propose pas souvent ce SGDB.

Jrefomule vous penser quoi de procédure stockée ? vous les utilisez ? est-ce que vous les utilisez pour tous ?
  • # Adhérence au SGBD

    Posté par . Évalué à 1.

    C'est le seul inconvénient à mon avis
  • # Procédure stockées

    Posté par . Évalué à 2.

    [disclaimer]
    Je connais plus Oracle que MySQL/Postgres mais je suppose qu'on retrouve à peu près les même possibilités.
    [/disclaimer]

    Pouvoir "séparer" les requête sql du code j'aime beaucoup l'idée, si on change de langage pour travailler sur la même base de données ça doit faire gagner beaucoup de temps.

    Ca peut aider mais pas tant que ça. Les procédures servent surtout pour l'écriture, très peu pour la lecture et on effectue souvent plus de lecture/recherche que d'écriture. Ca évite surtout de dupliquer du code ou d'oublier de mettre à jour certaines informations.

    Aussi j'imagine que comme les procédures sont compilé il doit y avoir un bon gain de performance comparé aux traitement que je fait pour le moment en PHP.

    Tout dépend ce que tu fais. Encore une fois on fait plus de lecture que d'écriture. Si tu construis tes requêtes dans le code tu prends cher deux fois. Tu as déjà le coût de multiples concaténation de strings pour au final construire toujours la même string (à part les valeurs passées).
    Ensuite, une procédure stockée permet au RDBMS de mettre le plan d'exécution en cache et de n'avoir pas à refaire un hard parsing à chaque fois. Sur ce point, on peut obtenir des performances à peu près équivalentes en passant par des variables pour les requêtes récurrentes. Lorsqu'un RDBMS rencontre une requête (dans le cas d'Oracle en tous cas), il vérifie s'il n'a pas déjà rencontré la même (et donc s'il a le plan en cache). Or si on passe des requêtes qui ne diffèrent que par les valeurs, pour le système ce sont des requêtes différentes. Si on passe par des variables, il identifie qu'il s'agit de la même requête.

    vous les utilisez ? est-ce que vous les utilisez pour tous ?
    Pour tout, non. On peut difficilement "tout" faire uniquement avec. Je les utilise beaucoup pour des opérations type insert/update : Si le code passé est nul c'est une insertion, s'il est rempli c'est une mise à jour. Sinon ça sert pas mal pour supprimer une donnée et celles qui en dépendent. Ca évite d'écrire plusieurs delete et d'en oublier un (violation de contraintes, toussa). Idem pour les opérations d'insertion complexe (ajout d'un entrée et mise à jour dans une autre table).
    • [^] # Re: Procédure stockées

      Posté par . Évalué à 1.

      pourquoi ne pas les utiliser pour la lecture, ça fait un peux beaucoup à écrire pour un simple select * from `machin` where id = '1', mais sinon on garde les autres avantages non ?

      Je crois comprendre que c'est parce qu'il faut refaire un select sur le résultat de la procedure ?
  • # Intégrité de l'information

    Posté par . Évalué à 3.

    La principale utilité des triggers et des procédures stockées, c'est de faire de la validation de données pour garantir que ta base ne puisse pas se retrouver dans un état incohérent. Ce n'est plus à la partie applicative d'appliquer les contraintes d'intégrité mais ça relève directement du SGBD. Ça permet de faire des trucs un peu plus chiadés que les contraintes SQL de bases.
    ===> ça évite de se retrouver avec une base incohérente à cause d'un bogue dans le client ou d'un crétin qui a fait une requête foireuse.

    Les autres utilisations couvrent l'encapsulation de la logique métier donc moins de traitements à faire côté client, améliorer les performances en réduisant les échanges, néanmoins ça a un coût côté serveur. Mais également logguer, faire de la réplication, gérer plus finement les accès.


    Postgres a un support très complet des triggers et procédures stockés que l'on peut écrire en PL/pgSQL (un langage de programmation inspiré du PL/SQL d'Oracle) ou en Python, Perl, etc ...
    MySQL a un support assez récent et relativement pauvre comparé à Postgres, mais suffisant pour la plupart des cas d'utilisations.


    Pour de l'optimisation pure, tu as d'autres leviers à ta disposition à commencer par une bonne conception et l'implantation. Et ne jamais oublier que tout se paie avec un SGBD !
  • # Facilité

    Posté par (page perso) . Évalué à 2.

    Pour le projet sur lequel je travail, on utilise du C# (oui, je sais cestpaslibre). Et les procédures stoquées sont très utilisées.

    1) Ça permet de ne pas repasser les binaires à chaque changement d'une query. Même avec PHP, si on a du load balancing sur plusieurs serveurs, ça évite de devoir mettre à jour tous les serveurs. Seul un seul est mis à jour : le serveur SQL.
    2) Ça permet de tester la procédure sur les données sans devoir créer une page web ou un soft juste pour ça.
    3) Comme ça a été dit, le contrôle des données est meilleur ; ca évite aussi les SQL injections ; faut quand même faire un minimum attention aussi hein :)
    4) C'est certainement compilé dans le SGBD et donc plus performant ; pas besoin d'interpréter la query à chaque fois.
    5) Ça permet de faire de grosses transactions très proprement.
    6) Et si le gestionnaire que tu utilises est bien fait, tu retrouves plus facilement les procédures qui utilisent telle ou telle table et tous des trucs comme ça.
    7) Ça permet aussi, si nécessaire d'obliger les développeurs d'utiliser les stored procédures et ainsi avoir une sorte de séparation entre développeurs SQL et développeurs pages web.
    En gros, les stored procedure forment une API SQL.

    Voilà, en espérant t'avoir convaincu :)
    • [^] # Re: Facilité

      Posté par . Évalué à 1.

      Voilà, en espérant t'avoir convaincu :)

      pour sûr , bon je demandais que ça.
      Pas obligez de recompiler à chaque changement de requête j'y avais pas penser.
      La séparation ça me plait bien les développeurs c'est moi je navigue seul, c'est justement pour ça que je cherche des méthode pour éviter de laisser aller mon code à la dérive...

      Donc je pourrai aussi utiliser pour mes select. Bon me reste plus cas tester ça sérieusement et si ça me va à retravailler mon framework php.

      merci beaucoup les gars pour vos avis
      • [^] # Re: Facilité

        Posté par (page perso) . Évalué à 2.

        Les triggers/procédures stockées, il faut juste oublier sous MySQL !!!

        Pour faire simple, chez oracle ils ont un moteurs de compilation des triggers et les gardent en cache, chez PostgreSQL ça doit être de même (de mémoire).

        Chez MySQL, TOUTES les procédures stockées sont recompilées à CHAQUE connexion à la base de donnée !!!

        Donc en gros à chaque F5 de ta page tu ouvres une connexion à MySQL, tu recompiles automatiquement TOUTES les procédures stockées, puis tu fais tes requêtes et re-belote.

        Donc, si c'est pour avoir quelques < 10 procédures stockées pour un usage spécifique, oui, mais c'est une très mauvaise idée d'utiliser ça pour plus que ça.

        Les vues sous MySQL, même combat, il n'y a pas de vues consolidées donc il faut oublier pour les performances...
  • # Un peu passé de mode

    Posté par . Évalué à 2.

    Les procédures stockées sont un peu passées de mode. La tendance actuelle c'est de les éviter au maximum. Les problèmes sont nombreux, il est difficile de les monitorer, de les débugger, et d'en faire l'inventaire sur des grosses plates formes qui évoluent beaucoup.
    La plupart du temps, une procédure stockée va quand même embarquer un peu de logique métier, et comme on les oublie dans un coin (bah oui, c'est pas du code) ; lors d'évolutions c'est un peu la galère.
    Les outils pour les développer sont pas au top, la syntaxe du PL/SQL est plutôt vieille et pas orienté objet, et pour le non-initié c'est carrément insupportable à lire et à écrire.
    Quant à l'aspect performance, il faut le relativiser. Comme dit plus haut, une fois le plan d'exécution calculé (qui est une opération interne au sgbdr), il y n'y a plus de différence de performance. Si c'est la performance que tu cherches, il y a déjà de quoi faire en utilisant des prepared statement partout où tu le peux.
    'Chez moi', on les utilise lors de modification du schéma de base. Si les modifs de code devaient prendre 2 semaines, alors que ça peut se régler en 5 minutes d'écriture d'une procédure stockée, alors oui, la procédure stockée a sa place. Le reste du temps, je dirais que non.
    • [^] # Re: Un peu passé de mode

      Posté par . Évalué à 1.

      bah le mieux c'est que je test tous ça je me ferai une idée, je verrai bien si c'est si bien ou si pourrie que ça, aux moins avec vos commentaires j'ai le pour et le contre.

      Par contre pour moi je vois pas comment je pourrai oublier dans un coin comme si c'était pas du code.
      Il suffit de les mettre dans le programme pour dans la partie installation, je vois bien ça en PHP, faut ce créer un petit outils pour faire les MAJ près du reste de l'application voilà tous.
      J'oublie pas me table parce qu'elle sont pas directement dans mon code php quand même...

      Aussi pour la synthaxe moins je trouve ça plus propre que les 50.000 concatenation de chaines + les cast et échappement, au final on n'est presque plus capable de lire directement la requête à partir du code.

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.