[Delphi XE8] Optimisation execution de 50 000 requete.

**Aooka** · 14/08/2015, 10h02

Bonjour à tous,

Je cherche à optimiser mon code. Je m'explique, je souhaite effectuer un transfère d'une table d'une taille de 90 000ko (50 000 enregistrements dans la table) Paradox9 vers la SGBD SQLiteBrowser. Cependant, le transfère dure 1h30, ce qui est assez dérangeant sachant que je vais avoir des tonnes de tables à convertir ..

Le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
unit transfere;
 
interface
 
uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs, FMX.StdCtrls,
  FMX.Controls.Presentation, Data.Win.ADODB, Data.DB,
 
  DatabaseHelper, Data.sqlexpr, Data.DbxSqlite, FMX.Menus, System.Math.Vectors,
  FMX.Controls3D, FMX.Objects3D, FMX.Types3D, FMX.Viewport3D;
 
type
  TForm1 = class(TForm)
    Label1: TLabel;
    Button1: TButton;
    LBL_AvancementTransfere: TLabel;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    ADOTable1: TADOTable;
    ADODataSet1: TADODataSet;
    ProgressBar1: TProgressBar;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    LBL_AffTempsDebut: TLabel;
    LBL_AffTempsFin: TLabel;
    LBL_AffTempsExec: TLabel;
    Label5: TLabel;
    LBL_AffTempsTotExec: TLabel;
    Label7: TLabel;
    LBL_AffTempsMoyen: TLabel;
    procedure Button1Click(Sender: TObject);
  private
 
  public
 
end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.fmx}
 
 
procedure TForm1.Button1Click(Sender: TObject);
var
  ADOSelectQuery : TADOQuery;
 
  ConnexionBdd: TDbHelper;
  SQLInsertQuery : TSQLQuery;
 
  J : Integer;
 
  TempsDebut, TempsFin, TempsExec, TempsTotExec, TempsMoyen : TDateTime;
 
  Cnt : Integer;
begin
  Button1.Enabled := False;
 
 
  ConnexionBdd := TDbHelper.Create('', '.\bdd\', 'bddSQLitre.db');
  SQLInsertQuery := TSQLQuery.Create(nil);
 
 
  with SQLInsertQuery do
  begin
    SQLConnection := ConnexionBdd.GetConnection;
    Active := false;
 
    SQL.Text := 'INSERT INTO Ma_Table (A, B,C, D, E, F, G, H, ';
    SQL.Add('I, J, K, L, M, N, O, P, Q, R, S, T, U, V ');
    SQL.Add('VALUES (:pA, :pB, :pC, :pD, ');
    SQL.Add(':pE, :pF, :pG, :H, :pI, :pJ, :pK, :pL, ');
    SQL.Add(':pM, :pN, :pO, :pP, :pQ, :pR, :pS, :pT, :pU, :pV;');
  end;
 
  ADOSelectQuery := TADOQuery.Create(nil);
  with ADOSelectQuery do
  begin
    ConnectionString := 'Provider=MSDASQL.1; Persist Security Info=False; Data Source=bddParadox9';
    Active := False;
    SQL.Text := 'SELECT * FROM Ma_Table';
    Open;
    First;
  end;
 
  ProgressBar1.Create(nil);
  ProgressBar1.Max := ADOSelectQuery.RecordCount;
  J := 1;
 
  TempsDebut := Time;
  TempsTotExec := EncodeTime(0, 0, 0, 0);
  Cnt := 0;
  while (SQLInsertQuery.EOF) do
  begin
      SQLInsertQuery.ParamByName('pA').AsInteger := StrToInt(VarToStr(ADOSelectQuery['A']));
      SQLInsertQuery.ParamByName('pB').AsString := VarToStr(ADOSelectQuery['B']);
      SQLInsertQuery.ParamByName('pC').AsString := VarToStr(ADOSelectQuery['C']);
      SQLInsertQuery.ParamByName('pD').AsString := VarToStr(ADOSelectQuery['D']);
      SQLInsertQuery.ParamByName('pE').AsString := VarToStr(ADOSelectQuery['E']);
      SQLInsertQuery.ParamByName('pF').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['F']), 0);
      SQLInsertQuery.ParamByName('pG').AsString := VarToStr(ADOSelectQuery['G']);
      SQLInsertQuery.ParamByName('pH').AsString := VarToStr(ADOSelectQuery['H']);
      SQLInsertQuery.ParamByName('pI').AsString := VarToStr(ADOSelectQuery['I']);
      SQLInsertQuery.ParamByName('pJ').AsString := VarToStr(ADOSelectQuery['J']);
      SQLInsertQuery.ParamByName('pK').AsString := VarToStr(ADOSelectQuery['K']);
      SQLInsertQuery.ParamByName('pL').AsString := VarToStr(ADOSelectQuery['L']);
      SQLInsertQuery.ParamByName('pM').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['M']), 0);
      SQLInsertQuery.ParamByName('pN').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['N']), 0);
      SQLInsertQuery.ParamByName('pO').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['O']), 0);
      SQLInsertQuery.ParamByName('pP').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['P']), 0);
      SQLInsertQuery.ParamByName('pQ').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['Q']), 0);
      SQLInsertQuery.ParamByName('pR').AsString := VarToStr(ADOSelectQuery['R']);
      SQLInsertQuery.ParamByName('pS').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['S']), 0);
      SQLInsertQuery.ParamByName('pT').AsString := VarToStr(ADOSelectQuery['T']);
      SQLInsertQuery.ParamByName('pU').AsString := VarToStr(ADOSelectQuery['U']);
      SQLInsertQuery.ParamByName('pV').AsInteger := StrToIntDef(VarToStr(ADOSelectQuery['V']), 0);
 
      SQLInsertQuery.ExecSQL;
 
      Inc(Cnt, 1);
 
      ProgressBar1.Value := J;
      LBL_AvancementTransfere.Text := 'Nombre d''enregistrements transférés vers SQLiteBrowser : '  + IntToStr(J)
                                                                                                    + '/'
                                                                                                    + IntToStr(ADOSelectQuery.RecordCount);
      Inc(J);
      Application.ProcessMessages;
 
      { TDateTime }
      LBL_AffTempsDebut.Text := FormatDateTime('ss.zzz', TempsDebut);
      TempsFin := Time;
      LBL_AffTempsFin.Text := FormatDateTime('ss.zzz', TempsFin);
      TempsExec := TempsFin - TempsDebut;
      LBL_AffTempsExec.Text := FormatDateTime('ss.zzz', TempsExec);
      TempsTotExec := TempsTotExec + TempsExec;
      LBL_AffTempsTotExec.Text := FormatDateTime('hh:nn:ss.zzz', TempsTotExec);
      TempsMoyen := TempsTotExec / Cnt;
      LBL_AffTempsMoyen.Text := FormatDateTime('ss.zzz', TempsMoyen);
      TempsDebut := TempsFin;
 
      ADOSelectQuery.Next;
  end;
  ADOSelectQuery.Close;
  ADOSelectQuery.DisposeOf;
  SQLInsertQuery.DisposeOf;
  ConnexionBdd.Close;
end;
 
end.

Le problème est que j’exécute 50 000 requête INSERT INTO. Le temps moyen d’exécution d'une requête est de 0.095 sec environ ce qui est bien trop long .. J'ai essayé divers moyens sans aboutissement. Si quelqu'un à une idée ?

Je vous remercie d'avance,
Bien Cordialement,
Bonne fin de journée,

**ShaiLeTroll** · 14/08/2015, 10h25

Ajoute un Prepare explicite avec le dernier SQL.Add('...;
Evidemment faut espérer que SQLite gère les requetes préparées

Pense que SQLite est aussi conçu plutôt pour de l'embarquée, ce n'est pas conçu pour d'important volume de données.
Mais Paradox pouvait devenir très lent sous Windows avec un gros volume, tu es loin de dépasser les capacités de SQLite

Exécuter un grand nombre d'INSERT reste toujours lent
Regarde si tu peux désactiver les index durant l'insertion, ainsi leur calcul ne sera fait qu'une bonne foie pour toute à la fin
Regarde si tu as un opérateur BULK

Pense qu'il y a des ETL sur le marché qui permettent aussi de faire des transferts de base, leur concepteur ont justement bossé bien plus longtemps que tu ne pourras le faire sur les performances

**Barbibulle** · 14/08/2015, 10h38

+1 avec ShaiLeTroll

Voir aussi du coté des imports de fichiers plats. SQLLite browser à la possibilité d'importer des fichiers txt/CSV.

C'est peut être plus rapide et optimisé.

Sinon l'utilisation d'une table externe est souvent un bon moyen d'importer des données en masse.

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

**ShaiLeTroll** · 14/08/2015, 10h51

Coté code, quelques secondes pourrait être gagner :
- Mapper les champs TField au lieu d'appeler ADOSelectQuery['...'] à chaque fois
- Reduire la fréquence d'affichage, ProcessMessages cela coute du temps, appel le uniquement toutes les 250 fois comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
var
...
  FieldSourceA, FieldSourceB, FieldSourceC ... FieldSourceV : TField;
begin
...
    Open;
    First;
  end;
...
 
  FieldSourceA := ADOSelectQuery['A'];
  FieldSourceB := ADOSelectQuery['B'];
  FieldSourceC := ADOSelectQuery['C'];
  ...
  FieldSourceV := ADOSelectQuery['V'];
 
...
  while ...
...
      SQLInsertQuery.ParamByName('pA').AsInteger := StrToInt(VarToStr(FieldSourceA ));
      SQLInsertQuery.ParamByName('pB').AsString := VarToStr(FieldSourceB);
      SQLInsertQuery.ParamByName('pC').AsString := VarToStr(FieldSourceC);
      ...
      SQLInsertQuery.ParamByName('pV').AsInteger := StrToIntDef(VarToStr(FieldSourceV), 0);
...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Inc(J);
      if not ByteBool(J) then
      begin
        Application.ProcessMessages;
 
        { TDateTime }
        LBL_AffTempsDebut.Text := FormatDateTime('ss.zzz', TempsDebut);
        TempsFin := Time;
        LBL_AffTempsFin.Text := FormatDateTime('ss.zzz', TempsFin);
        TempsExec := TempsFin - TempsDebut;
        LBL_AffTempsExec.Text := FormatDateTime('ss.zzz', TempsExec);
        TempsTotExec := TempsTotExec + TempsExec;
        LBL_AffTempsTotExec.Text := FormatDateTime('hh:nn:ss.zzz', TempsTotExec);
         TempsMoyen := TempsTotExec / Cnt;
        LBL_AffTempsMoyen.Text := FormatDateTime('ss.zzz', TempsMoyen);
        TempsDebut := TempsFin;
      end;
 
      ADOSelectQuery.Next;

**e-ric** · 14/08/2015, 13h51

Salut

Est-ce que les tables cibles de l'insertion sont munies d'index, sont-ils désactivables ?
Dans ce cas désactive-les le temps des insertions et reconstruit les index après. La mise à jour des index peut devenir coûteuse si elle est faite au fil de l'eau.
Au pire détruis les index (DROP INDEX) et récrée-les (CREATE INDEX) après les insertions.

Il me semble, c'est à vérifier, je n'en ai pas le temps, que la notion de table virtuelle permette de se connecter directement à un fichier texte.

Comme le dit Shai, limite les affichages au maximum. Le ProcessMessage doit avoir lieu après la mise à jour des textes dans les labels pas avant.

Enfin, si tu rencontre des problèmes de volumétrie, ce n'est pas dit que SQLite te sauveras sur ce point, peut-être te faudra-t-il un SGBD plus musclé (tel Firebird qui propose une version embedded i.e. qui ne nécessite pas l'installation d'un serveur)

Cdlt

[Delphi XE8] Optimisation execution de 50 000 requete.

Langage Delphi

Discussions similaires

Partager

Partager