[FB] Insert massif et lenteur... [Résolu]

[Benjamin GAGNEUX]

Tutoriel Importation et exportation massives de données sous InterBase 6.0 / FireBird 1.5.x

Bonjour,

Je souhaite réaliser des insertions massives dans une table avec FireBird 1.5 et Delphi (IBX).

Malgré les divers posts sur ce forum en rapport avec ce problème, je n'ai pas su le résoudre...

Tout d'abord j'ai réalisé ce code à titre expérimental mais un équivalent (importation de données) doit etre présent dans mon projet... Et malheureusement, pour un insert de 2000 enregistrement j'arrive à un temps de réponse entre 21 et 23 sec, et plus j'augmente le nombre d'insertion massive plus le temps augemente de manière exponentiel.

Voici un bout de mon code

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
try
  iChrono := GetTickCount;
 
  WITH IBSQL1 do begin // TIBSQL
 
    sSQL := 'INSERT INTO CLIENT (IDCLT,CIVILID,NAME,FIRSTNAME,DESCRIPT) '+
                'VALUES (null,:civ,:nom,:prenom,:desc);';
    // Ajout de l'index par trigger
    // IDCLT : BigInt; CIVILID : SmallInt; NAME,FIRSTNAME,DESCRIPT : varchar
 
    SQL.Clear;
    SQL.Add(sSQL);
 
    for iBcl := 0 to StrToInt64(EditMassNbRec.Text) do 
    // Boucle dont EditMassNbRec.text fourni le nombre d'enregistrement a faire.
    begin
      IBTrans.StartTransaction;
 
      FGerer(sNom,sPrenom,sDesc);
      // traitement des chaines sNom,sPrenom et sDesc pour champs différents pour 
      //chaque enregistrement
 
      inc(iCiv);
      IF iCiv>2 then iCiv:=0;
      // changement de l'identifiant de civilité
 
      Params[0].AsInteger := iCiv;
      Params[1].AsString := sNom;
      Params[2].AsString := sPrenom;
      Params[3].AsString := sDesc;
      // affectation des paramètres de la requete
 
      Prepare;
 
      ExecQuery;
      IBTrans.Commit;
    end;
  end;
except
  if DMCltSQLDirect.IBTrans.InTransaction then DMCltSQLDirect.IBTrans.Rollback;
  ShowMessage('erreur');
end;
IBSQL1.Close;
 
LabMassInsertChrono.Caption := IntToStr(GetTickCount - iChrono)+' ms';
//indique le temps écoulé depuis le début du traitement

J'ai essayé de le documenté au mieux, mais s'il vous manque des détails n'hésitez pas à me les demander.

Que pensez-vous de ce code ? Comment l'optimiser afin d'atteindre des temps raisonnables ?
Est-ce envisageable pour une insertion de 20 000, 200 000, 2 000 000 enregistrement ?

Merci,

PS : j'ai vu l'ajout possible dans une table intermédiaire qui améliorerait nettement les temps d'exécution mais dans ce cas présent la table est vide donc ...

[qi130]

Ca aurait été bien de connaître la table, ses index et ses éventuelles FK...

J'ai l'impression que tu commites à chaque Insert ? ou ai-je rêvé ?

[Benjamin GAGNEUX]

Ca aurait été bien de connaître la table, ses index et ses éventuelles FK...

Voila, voila ...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CLIENT
________________________________________________________
IDCLT             -> BigInt + PRIMARY KEY ASC + INDEX DESC
CIVILID           -> SmallInt + FOREIGN KEY ASC sur la TABLE CIVILITE(IDCIV,LABEL)
NOM               -> Varchar + INDEX ASC + INDEX DESC
PRENOM            -> Varchar
DESCRIPT          -> Varchar
 
 
CIVILITE
________________________________________________________
IDCIV              -> SmallInt + PRIMARY KEY ASC
LABEL              -> Varchar

J'ai l'impression que tu commites à chaque Insert ? ou ai-je rêvé ?

Bah en faite j'ai testé 3 méthodes :
-Commit après 500 insert
-Commit à chaque insert
-Commit une fois pour tous les insert.

La première méthode est sensiblement plus rapide mais semble poser problème (MSN se déconnecte+systeme instable durant les quelques secondes après l'insertion réalisée).

La deuxième marche a coup sur.

La 3e plante tout au court d'une insertion massive.

Donc du coup j'ai préféré opter pour la 2e.

( j'ai oublié une petite précision, je ne peux pas faire marcher la BD sur un serveur dédié (contrainte client) )


Donc si vous avez une idée je suis preneur...
Merci,

[qi130]

Bon, quelques recettes de grand-mère:

1/ on suppose que tu insères tes lignes dans l'ordre "normal" (correspondant à l'ordre des clés) ?
2/ si le pas de commit à 500 pose quelques soucis, essaies de le mettre à 400 (ou 300...) c'est toujours ça de pris
3/ si tu peux le faire : lève temporairement la contrainte FK sur la civilité
4/ 2 index sur le nom c'est bien en recherche alpha, mais c'est gourmand...en plus, à l'issue de ton chargement, les index doivent avoir besoin d'une réorg.... droppe les avant le chargement ?

[Benjamin GAGNEUX]

Ah les recettes de grand-mère

1/ on suppose que tu insères tes lignes dans l'ordre "normal" (correspondant à l'ordre des clés) ?

oui enfin plus ou moins.
L'ajout se fait avec un Identifiant renseigné par Trigger ( Gen_ID(MonGen,1) )
Cependant, lors de la consultation de la BD en test (60 000 enregistrement) toute nouvelle série d'ajout ce fait en début de table ce qui renvoie lors d'une select * :
58 889
58 892
58 895
....
59 999
55 000 // enregistrement correspodant a une insertion antérieur.
55 003

(les identifiants augmentent de trois mais je pense que j'ai laissé une erreur quelque part dans la BD)

2/ si le pas de commit à 500 pose quelques soucis, essaies de le mettre à 400 (ou 300...) c'est toujours ça de pris

le problème est de savoir si les futurs clients ne rencontreront pas le meme problème (meme si celui ci n'apparait plus sur mon PC) ?

3/ si tu peux le faire : lève temporairement la contrainte FK sur la civilité

Si je la lève temporairement, elle ne me sert plus a rien dans l'exemple. Lors de l'insertion la FK sera vérifié.

4/ 2 index sur le nom c'est bien en recherche alpha, mais c'est gourmand...en plus, à l'issue de ton chargement, les index doivent avoir besoin d'une réorg.... droppe les avant le chargement ?

Ca je n'y avais pas pensé...
Mais comment faire ?
2 PS qui s'en occupe ?


Merci,

[Edit]
Ceci n'est qu'une base test, la base final comprendra entre 10 et 15 FK sans compter tous les index. Donc il faut que ca soit fiable et performant.
[/dit]

[Barbibulle]

Quel est le type de transaction ?
Combien il y a de mémoire sur le poste ? Et le systeme d'exploitation?
Pourquoi ne pas passer par un fichier et une table externe ?

[jean-jacques varvenne]

as tu essayé avec une table externe, regarde un peux:
http://www.developpez.net/forums/viewtopic.php?t=286529

c'est un moyen d'insérer des millions d'enregistrements en quelques minutes.

cela demande du travail, construire une table temporaire, transférer de la table externe à une table interne, reconstruire les index, mais cela décoiffe!

pense à détruire les index (ou les désactiver) avant le transfert, puis recrés les ou réactives les, cela va beaucoup plus vite. sans cela, en fin d'un long transfert, les index sont désorganisés et la "base" devient lente.

[Benjamin GAGNEUX]

Salut et merci a tous pour vos réponse

Quel est le type de transaction ?

le type de transaction est readCommited (read_committed,rec_version,nowait)
Est-ce bon ?

L'application devra fonctionner sur un pc ayant min 256 Mo RAM sous Win98/WinXP mais obtiendra un serveur dédié à partir de quelques postes (3 à 5 poste) (contrainte client = plus il y a de poste, plus il est facile d'en dédié un au serveur et d'améliorer sa configuration).
C'est pourquoi je dois vraiment optimiser cela (et comme ma machine test fonctionne avec 384 Mo RAM, je laisse Delphi de lancé qui occupe ainsi quelques ressources systemes)


L'utilisation des tables externes :
Cela me semblait en fait au début une bonne idée mais ... (et oui ya toujours un mais)
Comment puis-je créer le script à éxécuter sachant que celui-ci importera les données d'un tableur Excel ou meme style?
Comment l'éxécuter ? (avec un TIBSQL, un TIBStoredProc ?)

Merci à tous.

PS: je me renseigne sur la facon de gérer les index au mieux (création et destruction ou Activation/desactivation par PS)...
PPS : J'arrive actuellement à 2000 enregistrement en 1,3 sec donc ya du progrès

[Barbibulle]

Une table externe ce n'est qu'un fichier externe. La seule contrainte c'est que le fichier doit être avec des enregistrements à longueur fixes.

Vous n'allez pas faire de insert dans cette table externe mais créer ce fichier (soit depuis Excel soit depuis votre appli). Une fois crée, vous pourrez accèdez aux données via cette table externe (que vous pouvez créer à la vollée ou une fois pour toute si vous risquez de la réutiliser plusieurs fois).

Depuis Interbase il ne vous restera plus qu'à lancer l'ordre SQL (ou une PS) pour recopier les données présentes dans la table externe vers votre table "normale". Si la structure sont identiques un simple
insert INTO MATABLE SELECT * FROM MATABLE_EXTERNE;

Et bien entendu il est préférable de désactiver les indexes de la table pour les insertions massives.

[Benjamin GAGNEUX]

Désolé j'ai du mal a comprendre...

La seule contrainte c'est que le fichier doit être avec des enregistrements à longueur fixes.

Cela veut-il dire qu'il faut éviter le Varchar et autre champs à taille variable ?

Vous n'allez pas faire de insert dans cette table externe mais créer ce fichier (soit depuis Excel soit depuis votre appli). Une fois crée, vous pourrez accèdez aux données via cette table externe (que vous pouvez créer à la vollée ou une fois pour toute si vous risquez de la réutiliser plusieurs fois).

Désolé, le Francais est ma langue maternelle mais il y a des fois ou j'ai vraiment du mal...
Si je comprends bien, je crée un fichier script ayant ma liste d'insert pour l'ajout dans la table externe? ou alors je réalise directement mes inserts sur une nouvelle table vide et sans index ?


Sinon pour les index, je sais les supprimer et les recréer mais il semble exister une possibilité de désactiver les index. Je serais bien intéressé par cette dernière car probablement plus rapide. Je compte gérer cela grace à une PS (comme la copie de Table_Ext ver MaTable)


Merci pour ces infos et désolé si j'ai le cerveau ramoli aujourd'hui...

[jean-jacques varvenne]

la table(fichier plus exactement) externe doit absolument avoir des enregistrements de longueurs fixes, même si les champs "cible" sont des varchar.

un varchar n'est rien d'autre qu'un char dont ont coupes les espaces de droites. je ne suis même pas sûr que sur le serveur cela fasse la différence.

donc il faut créer un fichier "plat" dont toutes les colonnes sont parfaitement alignées et ensuite créer une table externe qui décrit ce fichier.

de la sorte, un select sur cette table doit présenter les données correctement.

ensuite, créer si cela n'est pas déjà fait la table "interne" et ensuite

Code :

 INSERT INTO <la TABLE interne> SELECT * FROM <la TABLE externe>

et hop le diable est dans la boîte en un minimum de temps.

il n'est pas possible de faire plus vite (si quelqu'un sait comment faire autrement, je suis preneur)

un exemple avec un script sql existe sur http://www.developpez.net/forums/viewtopic.php?t=286529

maintenant la question est de savoir si excel est capable de sauvegarder dans un fichier plat à colonne fixe ou pas.

question performance, il est hautement préférable de chercher à construire un fichier plat colonnés, de l'associer en "external table" que de chipoter avec les inserts et commits.

[Benjamin GAGNEUX]

Je crois avoir compris.

merci beaucoups.

maintenant la question est de savoir si excel est capable de sauvegarder dans un fichier plat à colonne fixe ou pas.

Je pense pouvoir me charger de cela en recupérant la valeur de chaques cellules et de rajouter des espaces jusqu'a la taille définie.


Je fais une série de tests et je vous tiens au courant des résultats.
(Je laisse le post ouvert en attendant)

[Benjamin GAGNEUX]

bonjour,

Malgré plusieurs essaies, je n'arrive pas a faire fonctionner mon script et ce sous mon application Delphi comme sous IB Expert.

Sous Delphi la 2e commande du script a interpreter est signalé comme inconnu :

Dynamic SQL Error
SQL error code = -104
Token unknown - line 12, char 1
CREATE

Je le lance avec un TIBSQL en ajoutant chaque ligne du script à IBSQL.SQL


Sous IB Expert

Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Access to external file "D:\DONNEES.TXT" is denied by server administrator.

voici mon script :

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*Creation de la table externe en fonction des donnees de d:\donnees.txt*/
CREATE TABLE T_EXTERNAL
EXTERNAL FILE 'D:\DONNEES.TXT' (
    CIV           char( 1),
    NAME          char(40),
    FIRSTNAME     char(30),
    DESCRIPT      char(60),
    RT            char(2)
);
 
/*Création de la ps de transfert*/
CREATE PROCEDURE PS_TRANSFERT
AS 
  DECLARE VARIABLE CIV              SmallInt;
  DECLARE VARIABLE NAME            Char(40);
  DECLARE VARIABLE FIRSTNAME        Char(30);
  DECLARE VARIABLE DESCRIPT        Char(60);
 
BEGIN 
 
  FOR 
    SELECT *
    FROM T_EXTERNAL
  INTO :CIV, :NAME, :FIRSTNAME, :DESCRIPT
  DO 
  BEGIN 
    INSERT INTO CLIENT (IDCLT,CIVILID,NAME,FIRSTNAME,DESCRIPT)
    VALUES (NULL,:CIV,:NAME,:FIRSTNAME,:DESCRIPT);
 
  END
END
;
 
/*execution de la pc, commit et drop de la ps et de la table*/
EXECUTE PROCEDURE PS_TRANSFERT;
COMMIT;
 
 
DROP PROCEDURE PS_TRANSFERT;
DROP TABLE     T_EXTERNAL;
 
 
EXIT;

C'est surement une petite erreur que j'ai commise mais je ne vois pas ou ?

Merci,

[Barbibulle]

Sous FB1.5 la sécurité a été modifiée. On ne peux plus créer des tables externes sans devoir modifier le paramétrage de FB.

Pour pouvoir en créer il faut changer le firebird.conf et mettre
un truc du genre :

Code :

ExternalFileAccess = Restrict c:\MesTablesExternes\

pour autoriser les tables externes dans le répertoire C:\MesTablesExternes\
Sinon

Code :

ExternalFileAccess = Full

pour autoriser n'importe où (comme sous IB6). Mais celà diminue grandement la sécurité.

[Benjamin GAGNEUX]

merci,

Cependant je dois automatiser tout cela et malheureusement la seule chose qui nécessite d'etre dynamique est la table externe.


y a t'il un composant ou une possibilité de modifier cela via un logiciel sans destructurer le fichier firebird.conf pour éviter les poblème de compatibilité avec les autres programmes?

(il y a toujours la solution de remplacer le fichier a chaque fois ... mais bon ... c'est pas terrible).

[Barbibulle]

Je ne comprend pas ou est le probleme ??

Lors de l'installation de FB vous modifiez le firebird.conf pour ajouter le bon paramètre.

Si FB existe déjà et qu'une autre appli utilise aussi les tables externes vous n'avez qu'à ajouter votre path vers votre répertoire à la suite séparé par un ";".

Code :

ExternalFileAccess = Restrict c:\AutreAppli\;c:\MesTablesExternes\

[Benjamin GAGNEUX]

Le problème se situe au niveau de mes clients.

Ce qui aurait été bien c'est de leur mettre un bouton qui leur permette d'importer facilement les données.

Je pense que je vais rajouter une procedure gérant cela :

Code :

1
2
3
4
- chercher dans le fichier 'ExternalFileAccess ='
- si pas trouver alors créer 'ExternalFileAccess = Restrict C:\...'
- si # devant alors la ligne est changé en 'ExternalFileAccess = Restrict C:\...'
  sinon vérifier fin de ligne + ajouter à la fin de la ligne '; c:\...'

Sinon en ce qui concerne le script, il marche mais ... après 350 000 entrées ... toutes les ressources sont prises (j'avais oublié les indexs) donc gros ralentissement.
Mais j'ai constaté qu'en faisant un Delete total, rien ne s'améliorait meme pire :
La base est vide mais lors d'un select il me faut 20 sec pour avoir la réponse de la requete.
J'ai tenté de désactivé les index et puis les réactivé (je pense que cela aurait été plus utile au moment de l'insertion mais j'ai oublié )
Bilan : PC planté redémarrage obligatoire et les requetes sur la table vide sont toujours aussi longue.
(je suppose que la mémoire nécessaire pour le serveur est resté pour 350 000 entrées)

j'ai vraiment du mal, aussi je vous remercie de votre patiences.

Je vous tiens au courant de l'avancement.
merci


[EDIT] Les ressources se sont libérés comme par magie

[Benjamin GAGNEUX]

Voici les résultats après plusieurs testes :

Ajout de 100 000 ligne en 25 secondes (contre 35 avec les indexs).

Cela vous parait correct ou il est possible de faire encore mieux ?

voici un bout de mon code

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
  try
      IBTrans.StartTransaction; // TIBTransaction
 
     // sSQL de type string : contient la requete ou le script
      sSQL := 'CREATE TABLE T_EXTERNAL '+
              'EXTERNAL FILE '''+ExtractFilePath(Application.ExeName)+'DONNEES.TXT'' ('+
              ' CIV           char( 1),'+
              ' NAME          char(40),'+
              ' FIRSTNAME     char(30),'+
              ' DESCRIPT      char(60),'+
              ' RT            char(2)'  +
              ');';
 
      // TIBSQL : execution de la requete ou du script
      WITH IBSQL1 do
      begin
        SQL.Clear;
        SQL.ADD(sSQL);
        Prepare;
        ExecQuery;
      end;
 
      IBTrans.Commit;
      IBTrans.StartTransaction;
 
      sSQL := 'CREATE PROCEDURE PS_TRANSFERT '+
              'AS '+
              'DECLARE VARIABLE CIV              Char(1) ; '+
              'DECLARE VARIABLE NAME            Char(40); '+
              'DECLARE VARIABLE FIRSTNAME        Char(30); '+
              'DECLARE VARIABLE DESCRIPT        Char(60); '+
              'BEGIN '+
              'FOR '+
              'SELECT CIV,NAME,FIRSTNAME,DESCRIPT '+
              'FROM T_EXTERNAL '+
              'INTO :CIV, :NAME, :FIRSTNAME, :DESCRIPT '+
              'DO '+
              'BEGIN '+
              'INSERT INTO CLIENT (IDCLT,CIVILID,NAME,FIRSTNAME,DESCRIPT) '+
              'VALUES (null,:CIV,:NAME,:FIRSTNAME,:DESCRIPT); '+
              'END '+
              'END;';
 
      WITH IBSQL1 do
      begin
        SQL.Clear;
        SQL.ADD(sSQL);
        Prepare;
        ExecQuery;
      end;
 
      IBTrans.Commit;
      IBTrans.StartTransaction;
 
      sSQL := 'EXECUTE PROCEDURE PS_TRANSFERT;';
 
      WITH IBSQL1 do
      begin
        SQL.Clear;
        SQL.ADD(sSQL);
        Prepare;
        ExecQuery;
      end;
 
      IBTrans.Commit;
      IBTrans.StartTransaction;
 
      sSQL := 'DROP PROCEDURE PS_TRANSFERT;';
 
      WITH IBSQL1 do
      begin
        SQL.Clear;
        SQL.ADD(sSQL);
        Prepare;
        ExecQuery;
      end;
 
      IBTrans.Commit;
      IBTrans.StartTransaction;
 
      sSQL := 'DROP TABLE     T_EXTERNAL;';
 
      WITH IBSQL1 do
      begin
        SQL.Clear;
        SQL.ADD(sSQL);
        Prepare;
        ExecQuery;
      end;
 
      IBTrans.Commit;
  except
      IBTrans.Rollback;
  end;

(Delphi)
Qu'en pensez vous ?


Sinon n'y a-t-il pas moyen de construire et detruire les index via une procedure stocké ?

J'ai tenté de créer cette PS sous IBExpert mais sans succès :

Code :

1
2
3
4
5
6
7
begin
  /* Procedure Text*/
 
  CREATE INDEX idx_name_asc ON client (NAME, ASC);
 
  suspend;
end

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 6, char 3.
create.

Merci beaucoups a tous !

[Barbibulle]

Envoyé par yobenzen

Le problème se situe au niveau de mes clients.

Ce qui aurait été bien c'est de leur mettre un bouton qui leur permette d'importer facilement les données.

Je pense que je vais rajouter une procedure gérant cela :

Code :

Je ne comprend pas pourquoi vous ne laissez pas le paramètre dans le firebird.conf. Un fois mis lors de l'installation vous le laissez. Si votre client y touche c'est son probleme. Les clients qui touchent à tout il n'y a pas pire car le plus souvant il ne se souviennent pas de ce qu'ils ont fait ou pire ils vont dire qu'ils n'ont rien fait.
Mais bon si le paramètre n'y est plus et que je dois intervenir pour réparer, je facture. Et en général ça calme les touches à tout...

De même que la table externe, je ne les crée pas dynamiquement, ni les detruits après utilisation.
Elles sont crées lors de l'installation et c'est tout.
Lorsque je veux faire un transfert je n'ai qu'a copier le fichier au bon endroit et lancer la procédure d'import (effacement des indexes (indexes, PK et FK), Désactivation des triggers, insertion (en utilisant un sous select avec jointure ) ou à défaut de jointure une vérification après coup de l'intégrité puis création des indexes (PK, FK et indexes).

Voilà.

[Benjamin GAGNEUX]

Merci,

je crois que je ne me pose pas les bonnes questions...

Mais bon si le paramètre n'y est plus et que je dois intervenir pour réparer, je facture. Et en général ça calme les touches à tout...

Problème : les clients ont très peu de moyen (je mise sur le nombre au niveau national).

Solution : un client veut importer des données -> il enverra son fichier et un petit chèque (pas bien gros puisque tout le monde doit avoir actuellement un fichier excel ou csv) -> je lance la procédure sur ma machine -> je renvoie le CD.

Bilan : Moins de problème de sécurité, pas de déplacement car logiciel autonome ou réparable à distance. Moins d'erreur d'utilisations.

Lorsque je veux faire un transfert je n'ai qu'a copier le fichier au bon endroit et lancer la procédure d'import (effacement des indexes (indexes, PK et FK), Désactivation des triggers, insertion (en utilisant un sous select avec jointure ) ou à défaut de jointure une vérification après coup de l'intégrité puis création des indexes (PK, FK et indexes).

Je suis encore relativement nouveau sur IB/FB et mes notions d'oracle sont bien loins (ancien utilisateur de MDAC Access mais je préfère oublier !!!). Du coup, je me pose plusieurs questions malgré le tuto Introduction aux procédures stockées d'Interbase 6 de Etienne Bar et les fichiers d'aide d'IB.

- est-il possible de mettre un sript en procédure stocké (ajout de SET TERM semble ne pas suffir) ?
- Comment activer/désactiver les PK/FK/Index via une PS ?
- Comment activer/désactiver les triggers ?
- Comment reconstruire les index notament PK (update ?)?

Existe-t-il un tuto ou une aide expliquant bien cela ?

Merci