Performances d'insertion dans une procédure

Bonjour,

je suis en pleine rédaction de ma première vraie procédure PL/SQL, dont le but est d'insérer dans une table tierce, à partir d'une table lignes de commandes, des données de quantité commandée, expédiée, reste à expédier... calculs effectués à partir d'une autre table.
Donc je récupère les lignes de commandes concernées via un curseur, et dans l'exécution de ce curseur, je calcule les positions citées ci dessus, que je stocke dans des variables, tant que le curseur ramène des lignes.

Ma question est de savoir quelle est la solution performante entre effectuer un insert des valeurs A, B, C, D à chaque passage dans la boucle, ou bien gérer un tableau de type RECORD alimenté à chaque passage, puis ne faire qu'une insertion à la fin du traitement du curseur?

J'ai effectué des recherches dans la FAQ et sur le forums PL/SQL, je n'ai pas trouvé d'éléments pertinents. Etant novice dans la création PL/SQL, je crains que certains termes que j'utilise soient inappropriés, aussi n'hésitez pas à pointer mes erreurs sémantiques :aie:

je vous remercie par avance de l'aide que vous pourrez m'apporter.

Cordialement,

Salut,

Effectivement faire des traitements de masse c'est beaucoup plus performant que les insert ligne à ligne cf tuto

merci skuatamad,

je pense effectivement que c'est le genre de comparaison que je n'arrivais pas à trouver. Il me reste à comprendre le fonctionnement ;)

Merci en tous cas de ton aide,

Cordialement

Il faut toujours garder à l'esprit que l'accès au disque dur est au moins 100 fois plus coûteux que l'accès à la mémoire vive, donc l'accès à une table est au moins 100 fois plus coûteux que l'accès à une variable mémoire.
D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

Citation:

---

Envoyé par dgi77

Il faut toujours garder à l'esprit que l'accès au disque dur est au moins 100 fois plus coûteux que l'accès à la mémoire vive, donc l'accès à une table est au moins 100 fois plus coûteux que l'accès à une variable mémoire.
D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

---

Désolé, mais ce n'est pas une histoire d'accès au disque!
Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

Citation:

---

Envoyé par mnitu

Désolé, mais ce n'est pas une histoire d'accès au disque!
Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

---

Bonjour,

effectivement, la lecture ne me pose pas vraiment de problème, il s'agit en fait de l'insertion de données dans une autre table (données provenant de la lecture d'une table) qui me fait poser des questions: je vois deux façons de faire mon algo, soit en insérant ligne à ligne à chaque fetch, soit en gérant un tableau de records, qui fait l'insertion une fois toutes les lignes traitées et le fetch terminé.

Je vais donc a priori me tourner vers FORALL, avec tout de même une petite précision (pour être sûr): le fait de gérer le commit après mon forall effectuera bien un seul accès disque ?

d'avance merci

Cordialement

Citation:

---

Envoyé par dgi77

D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

---

Bonjour dgi77,

j'ai imprimé, lu et testé le cours de SheikYerbouti concernant l'utilisation des collections et de FORALL. A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter. Cela signifie-t-il que ecla ne fonctionne pas avec une collection dont le schéma ne reprend que certaines colonnes de la table ?

Cordialement,

Citation:

---

Envoyé par f-demu01

Bonjour,

effectivement, la lecture ne me pose pas vraiment de problème, il s'agit en fait de l'insertion de données dans une autre table (données provenant de la lecture d'une table) qui me fait poser des questions: je vois deux façons de faire mon algo, soit en insérant ligne à ligne à chaque fetch, soit en gérant un tableau de records, qui fait l'insertion une fois toutes les lignes traitées et le fetch terminé.

Je vais donc a priori me tourner vers FORALL, avec tout de même une petite précision (pour être sûr): le fait de gérer le commit après mon forall effectuera bien un seul accès disque ?

d'avance merci

Cordialement

---

Quand c'est possible la solution la plus performante est

Code:

---

1 2 3

Insert Into Table Select ... From T1, T2 etc.

---

Parfois cette solution peut être mise en pratique en utilisant les fonctions pipelined.

Citation:

---

...A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter...

---

effectivement:

Citation:

---

A partir d'oracle9i, les copies d'informations par blocs peuvent être effectuées directement dans les collections d'enregistrements

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL> Declare 2 TYPE TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ; 3 tabrec TYP_TAB_TEST ; 4 CURSOR C_test is select A, B From TEST ; 5 begin 6 -- chargement de la collection depuis la table -- 7 Select A, B BULK COLLECT into tabrec From TEST ; 8 9 -- insertion de lignes à partir de la collection -- 10 Forall i in tabrec.first..tabrec.last 11 insert into TEST values tabrec(i) ; 12 13 -- mise à jour des données de la collection -- 14 For i in tabrec.first..tabrec.last Loop 15 tabrec(i).B := tabrec(i).B * 2 ; 16 End loop ; 17 18 -- utilisation du curseur -- 19 Open C_test ; 20 Fetch C_test BULK COLLECT Into tabrec ; 21 Close C_test ; 22 23 End ; 24 /   Procédure PL/SQL terminée avec succès.

---

---

Citation:

---

Envoyé par mnitu

Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

---

Est-ce que j'ai dit que chaque fetch implique un accès disque ???
Toujours est-il, par contre, qu'il implique un accès disque tous les N fetch, ce qui justifie de passer par des variables mémoires. Je donnais une tendance pour que f-demu01 ait une idée du fonctionnement. Maitenant si tu as la science suffisante pour nous donner la valeur du N en fonction des différents paramètres à prendre en compte, libre à toi de disserter...
Au passage, pourrais-tu aussi nous expliquer l'intérêt du BULK COLLECT si le FETCH n'est pas gourmand en accès disque.
Pour f-demu01, le BULK COLLECT est le pendant du FORALL, mais quand tu "balances" les données dans les variables.

Citation:

---

Envoyé par mnitu

Désolé, mais ce n'est pas une histoire d'accès au disque!

---

Donc je suis désolé, mais je maintiens que c'est une histoire d'accès au disque!

Citation:

---

Envoyé par mnitu

Quand c'est possible la solution la plus performante est

Code :

INSERT INTO TABLE
SELECT ... FROM T1, T2 etc.
Parfois cette solution peut être mise en pratique en utilisant les fonctions pipelined.

---

Certes, mais si tu lis le post initial de f-demu01, tout laisse à penser que son algo est fonctionnellement trop compliqué pour être résolu en ensembliste.

Citation:

---

Envoyé par f-demu01

Bonjour dgi77,

j'ai imprimé, lu et testé le cours de SheikYerbouti concernant l'utilisation des collections et de FORALL. A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter. Cela signifie-t-il que ecla ne fonctionne pas avec une collection dont le schéma ne reprend que certaines colonnes de la table ?

Cordialement,

---

Je n'ai pas testé, mais je suppose que si les colonnes manquantes n'ont pas de contrainte NOT NULL, ça devrait passer

bonjour,

ne vous énervez pas, tout va bien. Je ne veux pas générer de tensions en demandant de l'aide :mrgreen::mrgreen:

je crois que je comprends tout ce que vous avez écrit, cela m'a permis en plus d'élargir mes connaissances (notamment BULK COLLECT: oui, je code en PL/SQL depuis vraiment peu de temps... mais j'aime ca !!). Reste maintenant à terminer l'écriture !

merci de l'aide et de l'intérêt que vous avez tous apporté à ma question

Cordialement,

Ne t'inquiète pas f-demu01...
J'ai simplement horreur que l'on me prête des discours que je n'ai pas tenus, surtout s'ils sont foireux ;)
Et puis je n'aime pas trop aussi qu'on casse l'explication de quelqu'un sans aucun argumentaire, ni qu'on balance des concepts techniques assez compliqués sans les étayer... ça fait un peu trop "je m'la raconte en lisant PC Expert", à mon goût :zekill:

ok, merci en tous cas.

je considère le problème comme résolu, en revanche, je pense ouvrir d'autres posts (pour d'autres questions, comme la gestion des RECORD avec le package DBMS_SQL...)

merci à tous,

Cordialement,

Citation:

---

Envoyé par dgi77

Est-ce que j'ai dit que chaque fetch implique un accès disque ???
Toujours est-il, par contre, qu'il implique un accès disque tous les N fetch, ce qui justifie de passer par des variables mémoires. ...

Donc je suis désolé, mais je maintiens que c'est une histoire d'accès au disque!
...

---

Bref, ce n'est pas parce que c'est toi qui le dit que cella signifie que t'as raison (de plus crier ça n'aide pas). Apprenez à soutenir tes affirmations par des tests ou démonstrations.
Voilà un article (On cursor for loops) écrit par Steven Feurstein dans Oracle Magazine qui explique bien ce qui se passe en réalité.

mnitu, le lien que tu donnes sur Oracle Magazine n'aboutit, chez moi, à aucun article en particulier :?

Citation:

---

Envoyé par SheikYerbouti

mnitu, le lien que tu donnes sur Oracle Magazine n'aboutit, chez moi, à aucun article en particulier :?

---

Ni chez moi :D. C'est pour ça que j'ai indiqué le titre. Donc suivez Contents, Section 50 Developper, article PL/SQL Practices - On cursor for loops.

Chez moi non plus, mais bon, je ne voulais pas insister...

Edit : je viens de voir ton post mnitu, fallait comprendre quand même...

Ben je viens de lire l'article, rien de nouveau sur la comète...
Si je comprends bien, il préconise de faire un curseur, dans la limite de 100 lignes ramenées... en le couplant avec un BULK COLLECT (tiens tiens... accès disque ?), et je ne vois aucune explication sur ce qu'il se passe derrière d'un point de vue purement technique (accès disque par exemple).
Et puis... 100 lignes : SUPER !!!
Il arrive qu'on ait à en traiter des millions dans certaines petites boîtes...

Mais tout ça ne traite que de la récupération des données.
Le problème qui était posé ici est qu'à chaque ligne récupérée, il y a un traitement pour générer des données calculées, puis insérer le résultat dans une autre table. Mais là, ni toi mnitu ni l'article de donne de solution, je reste sur ma faim. C'est donc à ce sujet que je préconisais de générer les données calculées en travaillant sur des variables mémoires de type RECORD (ou TABLE), après que les données sources y ait été ramenées par des curseurs agrémentés de BULK COLLECT, puis de les insérer par bloc en fin de traitement (et non une à une => je reviens aux accès disques) via des FORALL INSERT.

Pour ce qui est de la com, sache que si tu as eu l'impression que je criais, c'est que j'étais dépité par tant de mépris, alors que tes solutions ne m'ont toujours pas convaincues, et que j'ai dû me battre pour avoir comme seul argument un article généraliste en anglais sur Oracle Magazine...

Autre info, également est que le BULK COLLECT peut-être lui aussi "débité en tranches" via l'argument LIMIT, justement pour soulager les reins du rollback segment.

Citation:

---

Envoyé par dgi77

Ben je viens de lire l'article, rien de nouveau sur la comète...
Si je comprends bien, il préconise de faire un curseur, dans la limite de 100 lignes ramenées... en le couplant avec un BULK COLLECT (tiens tiens... accès disque ?), et je ne vois aucune explication sur ce qu'il se passe derrière d'un point de vue purement technique (accès disque par exemple).
Et puis... 100 lignes : SUPER !!!
Il arrive qu'on ait à en traiter des millions dans certaines petites boîtes...

---

Si, l'article il précise que c'est le changement de contexte PL/SQL SQL qui intervient et non pas l’accès au disque. Bref, écrit un petit test qui utilise bulk collect et un autre qui ne l’utilise pas, fais la trace sql et regarde dans le fichier de trace la statistique « disk = number of physical reads of buffers from disk ».

Citation:

---

Mais tout ça ne traite que de la récupération des données.
Le problème qui était posé ici est qu'à chaque ligne récupérée, il y a un traitement pour générer des données calculées, puis insérer le résultat dans une autre table. Mais là, ni toi mnitu ni l'article de donne de solution, je reste sur ma faim. C'est donc à ce sujet que je préconisais de générer les données calculées en travaillant sur des variables mémoires de type RECORD (ou TABLE), après que les données sources y ait été ramenées par des curseurs agrémentés de BULK COLLECT, puis de les insérer par bloc en fin de traitement (et non une à une => je reviens aux accès disques) via des FORALL INSERT.

---

Je pense que j'ai répondu à cette question. Le plus performante est de faire le traitement en pur SQL si c’est possible ; il arrive que certaines calculs peuvent être effectués en SQL. J’ai suggéré également qu’on pourrait employer le PL/SQL via les functions pipelined pour faire ces calculs. Ces fonctions peuvent utiliser le bulk collect à volonté. Mais sans savoir précisément les détails du traitement il est difficile de décider.

Citation:

---

Pour ce qui est de la com, sache que si tu as eu l'impression que je criais, c'est que j'étais dépité par tant de mépris, alors que tes solutions ne m'ont toujours pas convaincues, et que j'ai dû me battre pour avoir comme seul argument un article généraliste en anglais sur Oracle Magazine...

---

Désolé, mais je ne vois pas ou j’étais méprisant, peux tu me la montrer ?

Pour ce qui concerne les tests les voilà.

Pour ce qui concerne les articles voilà un autre et ensuite il y en a pas mal des références dans la doc Oracle et sur le site www.oracle/technology comme ailleurs.

A propos peux tu me montrer un exemple, ou article qui explique les différences par le nombre d’accès au disque.

Citation:

---

Envoyé par SheikYerbouti

Autre info, également est que le BULK COLLECT peut-être lui aussi "débité en tranches" via l'argument LIMIT, justement pour soulager les reins du rollback segment.

---

Je ne vois pas bien ce que tu veut dire par "soulager les reins du rollback segment". Peux tu expliquer ?

Le boulot est divisé par "paquets" commités séparément.

Citation:

---

Envoyé par SheikYerbouti

Le boulot est divisé par "paquets" commités séparément.

---

Pour moi utiliser le Bulk Collect n'est pas de tout synonyme de faire le commit par lot. Je pourrais assez bien l’employer pour accélérer le traitement et faire un seul commit à la fin.
Comme j’ai le dit récemment dans un autre post le problème des « commits par paquets » pose le problème de comment relancer le traitement proprement en cas de plantage !

Certe, mais il y a des moments ou le découpage devient incontournable car l'extension du rollback n'est pas toujours sans limite.
Etablire une procédure de reprise n'a rien de compliqué. De plus le traitment via les collections permet de récupérer les exceptions sans plomber le traitement.

Citation:

---

Envoyé par SheikYerbouti

Certe, mais il y a des moments ou le découpage devient incontournable car l'extension du rollback n'est pas toujours sans limite.
Etablire une procédure de reprise n'a rien de compliqué. De plus le traitment via les collections permet de récupérer les exceptions sans plomber le traitement.

---

Les disques sont bon marche aujourd'hui. Bon, c’est un débat où il est difficile à convaincre.
Ce qui est important est qu’il faut se rappeler que le commit par lot est plus complexe à gérer. Si il est vrai que « établir une procédure de reprise n’est pas compliqué » il est aussi vrai qu’il est facile d’établir une procédure de reprise erronée.
Et en fin quand la instance plante au milieu du traitement l’habilité de gérer les exceptions ne sert plus, mais le traitement doit toujours être relancé.

Chacun choisira la solution qui lui va le mieux...