[Oracle/PL-SQL] Passer un tableau en paramètre d'une procedure stockée

Recherche :

Mot : Pseudo : Filtrer
Page : 1 2 Page Suivante Page Précédente Bas de page
Auteur	Sujet : [Oracle/PL-SQL] Passer un tableau en paramètre d'une procedure stockée

mareek

Et de 3 \o/

Je dois insérer un grand nombre d'enregistrement (plusieurs dizaines de milliers) dans une table à la suite. Pour optimiser cette opération, j'ai pensé à utiliser une procedure stockée qui prendrait un tableau (ou une collection ou n'importe quoi d'approchant) en paramètres plutot que d'appeller 20000 fois la même procedure.
1)Est-ce possible ?
2)Si oui, Comment dois-je m'y prendre ?
3)1+2 via les composants ADO ?

D'avance merci

:hello:

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

Publicité

skeye

Ils viennent d'où tes enregistrements?

---------------
Can't buy what I want because it's free -

mareek

Et de 3 \o/

skeye a écrit :

Ils viennent d'où tes enregistrements?

~~de ton cul évidemment~~
Ils viennent de données entrées par l'utilisateur (enfin, pas saisies à la main, mais c'est tout comme).

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

nraynaud

lol

1) oui.
2) je sais plus
3) les ADOs c'est des trucs de pédophiles

---------------
trainoo.com, c'est fini

Ummon

skeye a écrit :

Ils viennent de données entrées par l'utilisateur (enfin, pas saisies à la main, mais c'est tout comme).

Tu veux dire qu'un utilisateur est capable de saisir plus d'un millier d'enregistrements à la suite avec ses propres pieds :??: :ange:

Message édité par Ummon le 02-07-2004 à 16:37:02

skeye

Ummon a écrit :

Tu veux dire qu'un utilisateur est capable de saisir plus d'un millier d'enregistrements à la suite avec ses propres pieds :??: :ange:

Je t'arrête tout de suite, j'ai jamais écrit ça!

---------------
Can't buy what I want because it's free -

jagstang

Pa Capona ಠ_ಠ

Ummon a écrit :

Tu veux dire qu'un utilisateur est capable de saisir plus d'un millier d'enregistrements à la suite avec ses propres pieds :??: :ange:

c'est peut-être des données entrées en 20 ans, qui sait ?

---------------
What if I were smiling and running into your arms? Would you see then what I see now?

mareek

Et de 3 \o/

Ummon a écrit :

Tu veux dire qu'un utilisateur est capable de saisir plus d'un millier d'enregistrements à la suite avec ses propres pieds :??: :ange:

JagStang a écrit :

c'est peut-être des données entrées en 20 ans, qui sait ?

L'origine des données n'est pas le problème

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

nraynaud a écrit :

1) oui.
2) je sais plus
3) les ADOs c'est des trucs de pédophiles

1) cool [:icon10]
2) dommage
3) Je n'ai pas souvenir de ces faits monsieur le juge :ange:

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

Harkonnen

Modérateur
Un modo pour les bannir tous

Je suppose que chaque entrée du tableau de ton programme VB contiendrait la donnée à insérer dans ta base, et que dans ta procédure stockée, tu prends chaque entrée de ce tableau, et tu l'INSERT dans ta base.

Le client Oracle de Microsoft ne permet pas de passer des tableaux en argument à des procédures stockées (ou alors j'ai jamais réussi à y arriver, mais dans ce cas je suis pas le seul)

La meilleure solution, est de mettre toutes les entrées de ton tableau dans une grande chaine de caractères, séparées par des virgules, puis de passer cette chaine en argument à une procédure stockée.
Puis, dans la procédure stockée, il faudra que tu parses cette chaine pour séparer les données, en utilisant la virgule comme séparateur. Puis réalise ton INSERT pour chaque donnée extraite.

Tu peux parser la chaine en utilisant les fonctions INSTR, SUBSTR, LTRIM et RTRIM de PL/SQL.

Bref, bon courage, c'est pas simple

---------------
J'ai un string dans l'array (Paris Hilton)

Publicité

mareek

Et de 3 \o/

merci, je vais voir ce que je peux faire avec ça.

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

dans mes rechrches sur google, je suis tombé sur cet article:
http://www.databasejournal.com/fea [...] hp/3318791

avec les méthodes qu'il donne, je pense avoir trouvé le moyens d'envoyer des tableaux de valeurs en paramètre d'une procedure stockée, je testerai ça lundi matin

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

Harkonnen

Modérateur
Un modo pour les bannir tous

ça a l'air sympa [:figti]
si tu y arrives, poste le code de ta procédure, ça m'intéresse

---------------
J'ai un string dans l'array (Paris Hilton)

HappyHarry

tu peux pas utiliser un type de données TABLE ?

mareek

Et de 3 \o/

HappyHarry a écrit :

tu peux pas utiliser un type de données TABLE ?

Vu que je veux insérer des valeurs qui ne sont pas dans la base, je ne pense pas, mais si tu as une méthode pour faire ça, je suis à l'écoute.

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

HappyHarry

ben tu peux passer un argument de types "table d'entier" en parametre d'une proc, ce qui correspond a un tableau [:spamafote]

mareek

Et de 3 \o/

Comment je fais ça avec les ADO ?

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

HappyHarry

ca c'est une autre histoire [:ddr555]

j'avais essayé en ASP un jour, j'avais pas réussi

Message édité par HappyHarry le 03-07-2004 à 13:45:59

mareek

Et de 3 \o/

Bonne nouvelle: J'ai réussi \o/
Mauvaise nouvelle: C'est 4 à 5 fois plus lent en utilisant des tableaux /o\

Si ça vous intéresse, je poste le code qui permet tout ça.

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

HappyHarry

t'as fait comment finalement ?

mareek

Et de 3 \o/

CREATE TABLE TABLETEST
(
IDTABLETEST NUMBER(6) NOT NULL,
NOM VARCHAR2(25)
);

CREATE TYPE TEST_TY AS OBJECT (V_ID NUMBER, NOM VARCHAR2(25));
CREATE TYPE TEST_ARRAY AS VARRAY(10000) OF TEST_TY;

CREATE OR REPLACE PROCEDURE TestInsertArray(ARRAY_INSERT IN TEST_ARRAY)
AS
BEGIN
For i in 1..ARRAY_INSERT.COUNT Loop
INSERT INTO TABLETEST(IDTABLETEST, NOM) VALUES(ARRAY_INSERT(i).V_ID, ARRAY_INSERT(i).NOM);
End Loop;
END TestInsertArray;
/

Code VB:

Code :

'Cette fonction renvoit le temps (en secondes) que met oracle à insérer les elements
'ATTENTION, la taille des "programmes" est limitée dans Oracle (chez moi ça plante quand lNbRequetes est >2519)
Public Function TestInsert(lNbRequetes As Long, ADOConnection as ADODB.Connection) As Single
Dim sngResult As Single
Dim sSqlRequest As String
Dim i As Long
sSqlRequest = "BEGIN" & vbCrLf & "TestInsertArray(TEST_ARRAY("
For i = 1 To lNbRequetes
sSqlRequest = sSqlRequest & "TEST_TY(" & CStr(i) & ",'DTC'),"
Next
sSqlRequest = Left(sSqlRequest, Len(sSqlRequest) - 1) & " ));" & vbCrLf & "END;"
sngResult = Timer
ADOConnection.BeginTrans
ADOConnection.Execute sSqlRequest, , adExecuteNoRecords
ADOConnection.CommitTrans
TestInsert= Timer - sngResult
End function

Requete envoyée à Oracle:

BEGIN
TestInsertArray(TEST_ARRAY(TEST_TY(1,'DTC'),TEST_TY(2,'DTC'),TEST_TY(3,'DTC'));
END;

Message édité par mareek le 05-07-2004 à 17:53:42

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

ça intéresse qqn si je poste les différences de perfs selon la méthode utilisée ou bien je laisse mourir ce topic tranquillement ?

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

jagstang

Pa Capona ಠ_ಠ

non vas-y envoie

---------------
What if I were smiling and running into your arms? Would you see then what I see now?

mareek

Et de 3 \o/

Pour 100000 insertions:

Sans rien : 980,531 s.
Blocs : 273,453 s.
Transaction : 387,311 s.
Blocs + Transaction : 270,156 s.
proc : 1881,984 s.
Blocs + proc : 138,030 s.
Transaction + proc : 1004,984 s.
Blocs + Transaction + proc : 40,640 s.
Blocs + proc + tableaux : 265,077 s.
Blocs + Transaction + proc + tableaux : 238,124 s.

lexique:
-sans rien: execute les requêtes d'insertion une par une.
-Blocs: execute toutes les requêtes d'insertion en une seule fois dans un bloc BEGIN ... END;
-Transaction: exetute les requêtes dans une transaction.
-Proc: utilise une procedure stockee pour faire l'insert (un insert par procedure)
-proc + tableaux: utilise une procedure stockee qui prend un tableau en paramètre (tous les insert d'un seul coup).

Message édité par mareek le 06-07-2004 à 10:06:13

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

jagstang

Pa Capona ಠ_ಠ

résultats impressionnants... surtout en tre 1004 et 40 s...

---------------
What if I were smiling and running into your arms? Would you see then what I see now?

jagstang

Pa Capona ಠ_ಠ

merci en tout cas :jap:

---------------
What if I were smiling and running into your arms? Would you see then what I see now?

mareek

Et de 3 \o/

JagStang a écrit :

résultats impressionnants... surtout en tre 1004 et 40 s...

t'en as sauté un

proc : 1881,984 s.

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

jagstang

Pa Capona ಠ_ಠ

je compare pas le meilleur et le moins bon résultat. Mais des résultats qui me semblaient comparable,

Transaction + proc : 1004,984 s.
Blocs + Transaction + proc : 40,640 s.

---------------
What if I were smiling and running into your arms? Would you see then what I see now?

mareek

Et de 3 \o/

OK.

Moralité, les tests cai bon, mangézen [:joce]

Message édité par mareek le 06-07-2004 à 10:21:33

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

Harkonnen

Modérateur
Un modo pour les bannir tous

:jap: :jap:

---------------
J'ai un string dans l'array (Paris Hilton)

mareek

Et de 3 \o/

Pense bête:

Penser à ajouter le beginTrans et comitTrans

Insertion/Maj des asso TypeH:

1-delete de tout les couples IDAFFPH/IDTYPEH à insérer/updater (une seule reqête, aucun risque d'erreur)
2-Passage de l'IDETAT à 3 sur toutes les asso contenant un IDAFFPH à insérer/updater (une seule reqête, aucun risque d'erreur)
3-Insérer les couples IDAFFPH/IDTYPEH à insérer/updater

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

Quelqu'un a déjà utilisé le FORALL ? Notre DBA m'en a parlé aujourd'hui et ça a l'air d'être le moyen idéal de faire ce que je cherche à faire.

je testerai ça quand j'aurais le temps.
plus d'infos:
http://tahiti.oracle.com/pls/tahit [...] tion=48873

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

Harkonnen

Modérateur
Un modo pour les bannir tous

ça a l'air cool, je connaissais pas du tout :jap:

---------------
J'ai un string dans l'array (Paris Hilton)

Arjuna

Aircraft Ident.: F-MBSD

mareek a écrit :

L'origine des données n'est pas le problème

Bah si c'est un problème.

Parceque si tu peux passer par une table ou un truc similaire, il suffit que ta requête accepte une "table" (ou "curseur" ) en paramètre, et tu lances :

select maFonction(select mesdonnes from matable)

Arjuna

Aircraft Ident.: F-MBSD

mareek a écrit :

Bonne nouvelle: J'ai réussi \o/
Mauvaise nouvelle: C'est 4 à 5 fois plus lent en utilisant des tableaux /o\

Si ça vous intéresse, je poste le code qui permet tout ça.

arf

mareek

Et de 3 \o/

Relis le topic, ça t'évitera de débarquer 6 mois après avec une non solution

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

J'ai fait des tests avec le FORALL, mais je trouve des résultats bizarre :heink:

J'en parle à mon DBA lundi et je vous tiens au courant

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

mareek

Et de 3 \o/

Après avoir corrigé le bug monstrueux dans mon programme de test (:ange, j'ai enfin des résultats cohérents (toujours pour 100000 insertions):

Blocs + Transaction + proc : 50,859 s.
Blocs + Transaction + proc + tableaux(Obj) : 393,9838 s.
Blocs + Transaction + proc + tableaux(Simple) : 38,37413 s.
Blocs + Transaction + proc + tableaux(Simple) + Forall : 23,95225 s.

(j'ai changé de serveur de test depuis les tests précédents)

Le gain apporté par le FORALL est donc assez intéressant. Et il y a de fortes chances pour que les écrats soient encore plus important dans la pratique vu que ma table de test n'est pas indexée et que le FORALL est censé gagner un temps precieux sur l'indexation.
A noter egallement que l'utilisation des objets en PL/SQL fait chuter les perfs de manière vertigineuse.

Procedures stockées utilisées pour l'insertion via tableaux(Simple):

TYPE ARRAY_INT IS VARRAY(10000) OF NUMBER
TYPE ARRAY_STR IS VARRAY(10000) OF VARCHAR2(50)

CREATE OR REPLACE PROCEDURE TestInsertArrayNOObj(ARRAY_ID IN ARRAY_INT,ARRAY_NOM IN ARRAY_STR)
AS
BEGIN
For i in 1..ARRAY_ID.COUNT Loop
INSERT INTO TABLETEST(IDTABLETEST, NOM) VALUES(ARRAY_ID(i), ARRAY_NOM(i));
End Loop;
END TestInsertArrayNOObj;
/

Procedures stockées utilisées pour l'insertion via tableaux(Simple)+ ForAll:

CREATE OR REPLACE PROCEDURE TestInsertArrayFORALL(ARRAY_ID IN ARRAY_INT,ARRAY_NOM IN ARRAY_STR)
AS
BEGIN
FORALL i IN ARRAY_ID.FIRST..ARRAY_ID.LAST
INSERT INTO TABLETEST(IDTABLETEST, NOM) VALUES(ARRAY_ID(i), ARRAY_NOM(i));
END TestInsertArrayFORALL;
/

Prochaine étape: regarder comment utiliser un FORALL avec des séquences.

---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack

tomlameche

Et pourquoi pas ?

Question bête : pourquoi tu utilises pas sqlloader ?

skeye

tomlameche a écrit :

Question bête : pourquoi tu utilises pas sqlloader ?

sûrement à cause de ça:
http://forum.hardware.fr/hardwaref [...] tm#t785112

---------------
Can't buy what I want because it's free -

Publicité

Page : 1 2

Page Suivante

Page Précédente

Haut de page

FORUM HardWare.fr

Programmation

SQL/NoSQL

[Oracle/PL-SQL] Passer un tableau en paramètre d'une procedure stockée

Sujets relatifs
Problème de requetes SQL	Requête SQL et résultat
DBExpress SQL Server	[php/SQL] ajouter des enregistrement a laide d'une textarea
[SQL][ASP] affiche des résultats de 2tables avec même nom de colonnes	[Oracle/SQL] Différencier des séries de chiffres et de lettres
[Oracle] demarrage de la base	[php/SQL] filtre sur une liste deroulante
[VBA] Insérer numéro de page dans tableau [RESOLU]
Plus de sujets relatifs à : [Oracle/PL-SQL] Passer un tableau en paramètre d'une procedure stockée

Page générée en 0.098 secondes