Procédures stockées - bonnes pratiques

Version imprimable

21/12/2016, 10h45
patic

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.
21/12/2016, 13h38
StringBuilder

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/
21/12/2016, 15h02
patic

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.
21/12/2016, 21h09
StringBuilder

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.
22/12/2016, 09h58
patic

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
26/12/2016, 18h33
SQLpro

Citation:

Envoyé par patic

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 !

Citation:

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...

Citation:

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 !

Citation:

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.

Citation:

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 +
26/12/2016, 19h02
SQLpro
Citation:

Envoyé par patic

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:

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...

Citation:

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 !

Citation:

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 !

Citation:

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

Citation:

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 +
01/01/2017, 14h46
patic

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.