Sql dynamique dans les fonctions

[mello]

Bonjour,

Je travaille actuellement sur une version 5 de mysql et j'ai vu qu'il n'est pas possible de créer des fonctions stockées dynamiques.

J'ai besoin de créer une fonction dans laquelle je passe en paramètre le nom d'une table pour l'utiliser dans une requête (SELECT). Cela n'est pas possible en mysql 5

J'aimerais savoir si cela devient possible dans une version plus récente de mysql (même une version payante).
Quelqu'un peut-il me renseigner ?

[ericd69]

salut,

en fait c'est possible en utilisant une requête préparée dans ta procédure ou fonction stockée
tu stockes la concaténation de la chaine correspondant à ta requête et du nom de la table dans une variable globale et tu exécutes ça, par exemple:

Code sql :

1
2
3
4
SET @truc=concat('select * from ',nom,' where id=1');
prepare exe FROM @truc;
execute exe;
deallocate prepare exe;

[mello]

salut et merci pour ta réponse.

Le problème est bien là : avec ma version 5 de mysql, le code que tu m'a donné fonctionne bien pour les procédures mais pas pour les fonctions !

j'ai ce message d'erreur :

Dynamic SQL is not allowed in stored function or trigger

[ericd69]

je crois pas qu'il y ait une version de mysql qui le fasse dans les fonctions...

fait une procédure stockée si tu peux... vu ce que tu lui demandes de faire c'est ce que tu dois faire de toute façon

[mello]

Merci pour ta réponse.

En fait je t'explique un peu plus mon problème :
Dans l'application sur laquelle je travaille, on a une table assez grosse qui fait près de 3 millions d'enregistrements. Et chaque ligne contient une quarantaine champs de tout type.

Cette table étant au cœur de notre application, nous avons décidé de la scinder en plusieurs tables (une par compte) afin d'optimiser les performances des recherches.

On a donc toujours la table globale qui contient tous les enregistrements. Les insert et update sont faits sur cette table globale.
On a mis un trigger sur cette table afin que les modifications soient automatiquement répercutées sur la table du compte concerné.

Cela nous a permis de beaucoup gagner en performances lors des requêtes select. Le problème, c'est que les fonctions que nous utilisons pour certaines recherches continuent à aller taper dans la table globale.
C'est pour cela que je cherche à savoir si une version de mysql permet de faire des fonctions dynamiques.

Je ne perdspas espoir qu'un jour mysql puisse gérer ça ^^

[ericd69]

Les triggers sont plus limités que les procédures stockées.

Moi, je te conseille de faire une API de procédures stockées une fois pour toute...

Comme ça, côté application, une fois que l'adaptation est faite, elle n'a plus du tout à être refaite et tu as dé-corrélé les changements entre l'application et la bd.

En plus, tu te facilites la maintenance ensuite.

Il faut toujours scinder les grosses bd... même un gros sgbdr comme oracle n'est pas si performant avec 3 millions de tuples... mysql est plutôt pensé pour 50000 à 200000 tuples selon les traitements (pour rester dans des temps de traitements supportables de quelques secondes).

[Yanika_bzh]

Envoyé par mello

Merci pour ta réponse.

En fait je t'explique un peu plus mon problème :
Dans l'application sur laquelle je travaille, on a une table assez grosse qui fait près de 3 millions d'enregistrements. Et chaque ligne contient une quarantaine champs de tout type.

Cette table étant au cœur de notre application, nous avons décidé de la scinder en plusieurs tables (une par compte) afin d'optimiser les performances des recherches.

On a donc toujours la table globale qui contient tous les enregistrements. Les insert et update sont faits sur cette table globale.
On a mis un trigger sur cette table afin que les modifications soient automatiquement répercutées sur la table du compte concerné.

Cela nous a permis de beaucoup gagner en performances lors des requêtes select. Le problème, c'est que les fonctions que nous utilisons pour certaines recherches continuent à aller taper dans la table globale.
C'est pour cela que je cherche à savoir si une version de mysql permet de faire des fonctions dynamiques.

Je ne perdspas espoir qu'un jour mysql puisse gérer ça ^^

Je ne suis pas persuadé que votre solution soit la bonne...
Vous scindez une table contenant plusieurs comptes en plusieurs tables contenant un compte, c'est ca ?
Et cela dans un but d’améliorer les performances de recherche ?
Si c’était pour réorganiser votre modèle de données, je ne dis pas, mais pour un problème de performances, je ne crois pas.
En effet, cela consiste à reculer la prochaine échéance de dégradation de vos performances, car lorsqu'une table aura elle aussi atteint les 3 millions de lignes, vous en serez au même point.
Cela implique aussi une modification de l'architecture de votre base à chaque ajout de compte (dépendance architecture et fonctionnelle !!)

Si vos perfs sont mauvaises, peut-être est-ce dû à une mauvaise gestion des indexes, des requêtes peu optimisées qui font des full scan sur votre table (elles feront aussi des full scan sur vos nouvelles tables et vous le ressentirez quand votre volumétrie grandira)...
Vous allez donc vous trainer un double fonctionnement, du provisoire qui, comme l'on voit souvent, risque de durer sans pour autant résoudre le fond de votre problème.

Je vous conseillerais (mais cela n'engage que moi) de vérifier votre modèle de données, vérifier les contraintes relationnelles posées, les indexes déclarés, les requêtes peu performantes par des plans d’exécution, avant de vous lancer dans votre belle aventure

Bon Courage

[mydb_server]

Je ne sais pas en quelle version de MySQL tu es, mais à partir de la 5.1, MySQL a introduit une nouvelle version qui s'appelle "le Partitionnement"
Je pense que c'est une piste sérieuse à étudier malgré le grand nombre de limitations de cette option.

Rachid

[ericd69]

oui ils ont fait un partitionnement horizontal par compte... sans le dire ou le savoir formellement...

vu qu'il n'y a pas d'index globaux tu dois donc passer par la constitution d'une api de procédures stockées qui vont pallier ça et cacher la structure de la bd à la partie serveur...

comme ça :

• tu limites la quantité de données échangées entre mysql et php (par exemple)
• tu sépares la maintenance de ton application en 2, modifier la structure de la bd n'oblige pas forcément à tout repasser au crible côté applicatif (php)
• tu as une maintenance simplifiée : une procédure permet beaucoup plus de choses qu'un trigger

[Yanika_bzh]

Envoyé par ericd69

Il faut toujours scinder les grosses bd... même un gros sgbdr comme oracle n'est pas si performant avec 3 millions de tuples... .

[ericd69]

Y a rien de choquant à dire ça

Tu crois que les grosses tables de bases de données professionnelles ne sont pas partitionnées (sncf, google, etc...)?

Le sgbdr n'est qu'un outil qui sert à permettre à autre chose de se servir des données, on l'utilise rarement seul...

Certains processus ne sont pas contingentés en termes de temps, mais la plupart du temps c'est le cas...
Pour internet, 10s est acceptable pour un script php, pas 30 dans la plupart des configurations (sinon c'est le navigateur qui décide qu'il n'y a plus rien à attendre aussi).

Une grosse table = d'énormes fichiers à brasser, même oracle est rattrapé par les limites de débit de DD ou de la RAM ou des processeurs.

Par exemple, un tuple d'une arité de 40, la moitié en int(4) car des identifiants divers et l'autre en varchar(255) en utf8. Chaque tuple nécessite de bufferiser 20*4+20*255*4=20480 octets si tous les champs sont en "not null"...20Ko par tuple donc. Si on considère 3 millions de tuples, on a donc potentiellement 60Go.

Là on ne parle même pas de la taille des fichiers d'index à bufferiser en plus, etc... Je suis prêt à écouter en combien de temps tu lis et tu bufferises un fichier de cette taille, même si tu es en sata 3 avec un serveur ayant 12Go de mémoire... sans compter le tri des données, la taille de la génération des résultats...

D'où un partitionnement pour éventuellement paralléliser ou réduire les temps de parcours... Le principe : diviser pour mieux régner.


MySQL ne gére pas encore très bien le partitionnement, contrairement à sql server, postgres ou oracle, il n'y a, en effet, pas d'index globaux

je pense que pour faciliter le traitement, il serait bon de maintenir une table qui liste tous les comptes et si le nom de table n'est pas le même que la table associé.

Comme ça, dans l'api, les procédures qui servent d'accesseurs ou de créateur prennent en paramètre le nom du compte indépendamment de l'implémentation de la bd. Pour la suppression ou la création d'un compte idem... du coup, côté applicatif, on est bien indépendant des évolutions de structure de la bd.

[Yanika_bzh]

Vous confondez allégrement le partitionnement (physique) et le modèle de données !!!

Autant le partitionnement est une chose courante, mais elle ne se joue qu'au niveau de votre moteur SGBD. Mais en aucun cas, cela ne scinde une table au niveau de la modélisation de données. C'est la gestion des accès aux données sur disque qui change.

Consultez la doc ici

Vous verrez que malgré le partionnement des tables, le modèle de données reste intact, et que vous n'aurez pas (et ne devrez jamais) passer par du SQL dynamique pour aller chercher dans telle ou telle table vos valeurs en fonctions de critères en amont.

[ericd69]

Non non, je vous rassure.

Je comprends juste son choix pragmatique de faire un partitionnement horizontal sur le critère du compte, il aurait pu le faire par date, etc...

On n'a pas le modèle de données, donc le réviser est...

En plus, scinder une grosse table concrètement revient à faire plusieurs tables... à voir comment les organiser ensuite...

Dans l'étude d'un cas concret, le modèle de données doit tenir compte de l'implémentation réelle et des contraintes du projet...maintenant, là, on parle de la révision d'un modèle pour l'adapter le plus simplement à l'existant... Il faut aussi tenir compte de la perte de temps opérationnelle pour passer d'un modèle à l'autre sans perte, avec le temps d'étude, les vérifications, etc...

Je pense que son choix se justifie donc sans plus d'information sur son modèle en tant qu'approche pragmatique, c'est juste ce que je voulais vous faire comprendre

Mais je conçois que vous ne soyez pas d'accord avec son approche et son traitement cher yanika

[Yanika_bzh]

Je pense qu'un de nous deux n'a pas compris l'approche de l'intervenant...

Mais lorsque je vois le mot "scinder", puis "sql dynamique", puis de "trigger", puis "une table par compte" ... il n'y a aucun doute qu'il ne parlait pas de partitionnement mais bien de modification de son modèle de données ...

Son choix est peut-être pragmatique, il n'en demeure pas moins inadapté.

Si les requêtes ne sont pas optimisées, les indexes inadéquats, vous aurez beau partitionner tout ce que vous voulez, vous ne ferez que reculer l’échéance d'une nouvelle dégradation. L'optimisation physique doit être couplée à l'optimisation de développement.

Il faut diagnostiquer avant de traiter, dans tous les domaines...

Attendons le point de vue de l’intéressé.

[ericd69]

Oui, mais comme je disais, il ne donne pas son modèle de données...

On ne sait pas ce qu'il a fait.

Néanmoins, il arrive un moment où le partitionnement ici horizontal sur le critère des comptes est certainement devenu impératif...

Après, il est clair qu'on peut toujours discuter de critère de partitionnement...

Par contre, je maintiens que 3 millions de tuples pour le pauvre mysql, c'est des temps de traitement infames garantis...

Mais comme je l'ai dit aussi, on n'a pas le détails de ce qui a été fait comme étude (évolution de serveur, passer à un autre sgbd, revoir le modèle...)

J'avoue que je suis plus pour intégrer au départ l'évolution de la charge que de pleurer ensuite à réviser le modèle

[Yanika_bzh]

Je n'ai pas d'experience de volumétrie de cette grandeur sur MySQL.
Cependant j'en ai eu sur Sybase (12.5) et Oracle (meme en 7.3) avec des volumétries beaucoup plus importantes sans partionnement et les temps de réponses étaient quasi instantanés (lorsque la requête est bien pensée, les indexes bien placés et bien sur des volumétrie de retour restreintes).

[ericd69]

ça dépend de la taille de tes tuples bien sûr aussi

Mais quand je passais ma licence il y a quelques années on en parlait avec nos prof de BD et j'avoue que c'est pas intéressant

En plus je te rappelle que ça dépend aussi des réglages du serveur (buffer pour les jointures, etc...)

Mais y a aussi des trucs qui ne sont pas non plus négligeables sur les gros volumes... par exemple l'implémentation des indexes (type d'arbre utilisé, réglage sur les indexes textes), et là ça commence à jouer sérieusement ou le fait d'ouvrir des fichiers supplémentaires (pour les types text ou blob par exemple)

Bref je suis d'accord avec toi y a plein de trucs sur quoi jouer avant de toucher au modèle... mais faut différencier les sgbd et leur éventuelles performances selon, déjà, les fonctions systèmes qu'ils hackent (réservation mémoire, accès fichier, ordonnanceur...)

On peut enrober les choses de tout le formalisme qu'on veut le partitionnement horizontal revient bien, concrètement, à scinder ta table selon un ou plusieurs critères...

[mello]

Bonjour et désolé pour le temps que j'ai mis pour répondre,

Tout d'abord je vais ôter d'un doute : on n'a pas fait de partitionnement de base. On a bien modifié le modèle de données (création d'une table par compte utilisateur + Ajout de trgiger sur la table globale pour répercuter les modifications sur les tables des comptes).

Ensuite, je ne vois pas exactement à quoi correspond une "API de procédures stockées". En cherchant un peu, j'ai cru comprendre qu'il s'agissait de créer des dll en C ou en C++ et des les intégrer à MySQL. Le problème, c'est que personne dans notre équipe ne maitrise bien ces langages et encore moins pour faire ce genre de chose hélas.

Je ne peux pas vous donner exactement la structure de la table dont il est question. Il s'agit d'une table de gestion de planning. Cette table contient tout un tas d'informations nécessaires au bon fonctionnement de l'application mais les filtres de recherche s'effectuent essentiellement sur les champs "client", "salarié" et "date". Ces champs sont bien sûr indexés.

J'espère que ces données complémentaires vous permettrons de mieux visualiser mon problème.

[ericd69]

Une api c'est, pour faire simple, un ensemble de fonctions qui gèrent un ensemble de fonctionnalités données...

Dans ton cas:

• nouveau compte
• supprimer compte
• recherche diverse
• ...

Tout ceci constitue une api formée des diverses procédures stockées pour faire ça.

Toi, tu parles des UDF, qui sont une api de fonctions compilées sous forme d'une bibliothèque liée (".dll" sous windows ou ".so" sous linux) dont on se sert pour étendre les fonctionnalités du sgbd.

Ça peut aussi faire le job en partie, mais pour ce que tu as à faire, ce sont juste des procédures stockées qui sont nécessaires, codées en sql procédural.

Ce n'est pas très dur à faire, à comprendre et à vous former

En plus, tu minimises les échanges entre bd et application et, en soi, tu peux aussi augmenter la sécurité au passage.

[mello]

On utilise bien des procédures pour ce genre de chose ...

Le problème avec les procédures, c'est qu'on ne peux pas retourner de valeur. C'est pour cela qu'on utilise des fonctions. Dans certaines requêtes, on utilise même plusieurs fonctions pour aller récupérer des informations concernant le planning. Je ne vois pas comment remplacer cela par des procédures stockées.