Discussion :

[suiX-]

Bonjour,

J'utilises un composant TAdoQuery pour effectuer des requêtes SQL (2 fonctions : 1/ pour sélectionner des données 2/pour exécuter une requête (insert/update/etc).

EDIT : j'utilise Delphi 7 (Build 4.453)

J'ai remarqué, grâce à un fichier de 70000 lignes (fichier *.csv), un message d'erreur du type : "out of memory". Après analyse je pense que cela vient du composant TAdoQuery (et d'après des recherches sur le net aussi). J'ai essayé différentes solutions (fermer, libérer le composant, utiliser les paramètres pour n'avoir que le readonly, etc) mais sans succès. Voici un exemple d'une des fonctions. J'ai ajouté du log qui me permet de contrôler la mémoire physique.

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
function GetQuery(const ASql :string):TDataSet;
Var
 fTmpQuery : TAdoQuery;
begin
   AddLog('Mem Usage before GetQuery : '+ FormatFloat(' Mem.: ,.# K', CurrentMemoryUsage / 1024));
   fTmpQuery := TAdoQuery.create(NIL);
     try
       fTmpQuery.connection := vgAdoConnectionCible;
       fTmpQuery.SQL.Add(ASQL);
       AddDebug(TraceQuery(fTmpQuery));
       fTmpQuery.Open;
       Result := fTmpQuery;
       AddLog('Mem Usage after GetQuery : '+ FormatFloat(' Mem.: ,.# K', CurrentMemoryUsage / 1024));
     except
       On E:Exception do
       begin
         Result := NIL;
         Inc(vgNbError);
         AddLog('ERREUR GetQuery : '+ ASql + ' '+E.Message);
       end;
     end;
end;

Il faut savoir que cette fonction est appelé dans un script Pascal (composant PascalScript by Lazarus), et donc j'ai eu des problèmes lorsque je cherchais à libérer le TAdoQuery dans un finally.


J'ai essayé de modifié ADODB.pas comme indiqué sur un poste des groupes google :

Code :

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

1
2
3
4
5
destructor TADOQuery.Destroy;
begin
   FreeAndNil(FSQL);
   inherited Destroy;
end;

&

Code :

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

1
2
3
4
5
6
7
8
destructor TADOCommand.Destroy;
begin
   Connection := nil;
   FCommandObject := nil;
   FreeAndNil(FParameters);
 
   inherited Destroy;
end;

Mais sans succès.
Je ne vois vraiment pas quoi faire et je suis encore débutant dans la programmation.

Merci pour votre aide

[hmira]

Pouvez-vous préciser et poster les informations suivantes :
- Le contenu exact du paramètre ASql de la fonction GetQuery au moment du plantage,
- Les DDL (Data Definition Language) des différentes tables mises en jeux dans la requête SQL (ie, contenu du paramètre ASql mentionné ci-dessus),
- Le SGDB utilisées (ORACLE, SQL Server, DB2, MySQL, etc.. ).

PS : Le composant TADOQuery fonctionne très bien sous Delphi 7 (Build 4.453) ! C'est vraisemblablement l'utilisation que vous en faites qui est foireuse !

A+

[suiX-]

Pouvez-vous préciser et poster les informations suivantes :
- Le contenu exact du paramètre ASql de la fonction GetQuery au moment du plantage,
- Les DDL (Data Definition Language) des différentes tables mises en jeux dans la requête SQL (ie, contenu du paramètre ASql mentionné ci-dessus),
- Le SGDB utilisées (ORACLE, SQL Server, DB2, MySQL, etc.. ).

PS : Le composant TADOQuery fonctionne très bien sous Delphi 7 (Build 4.453) ! C'est vraisemblablement l'utilisation que vous en faites qui est foireuse !

A+

Cette fonction est utilisé dans le cadre d'une interface d'import.

Asql : Contient la requête SQL, pour faire simple, dans le cas ou je l'utilise je cherche une colonne dans une table, qui correspond à la clé primaire (ex: select colonnePK from table where colonnePK = 'id').
Dans une autre fonction, j'ai la même structure avec un Exec Query.
DDL : /
SGDB : SQL server dans ce cas.

Maintenant je ne vois pas le rapport avec la requête. Je suis ok pour dire que l'utilisation est peut être foireuse, mais je ne pense pas que cela vient de la requête...


NB : Cette fonction est appelé pour chaque ligne d'un fichier *.CSV que je souhaite importer. EN gros j'ai un script pascal "compilé", qui permet de traiter chaque ligne que j'importe. Dans celui-ci je fais appel à ces fonctions.

[hmira]

Est-ce que vous fermez proprement la requête après son utilisation (parce qu'on le voit pas dans ton post) du style :

Code PASCAL :

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

1
2
3
4
5
6
7
8
9
10
aQuery := GetQuery(... ); 
 
try 
  { ... utilisation de la query aQyery  } 
 
 
finally
   aQuery.Close; 
   FreeAndNil(aQuery); 
end;

Le "Close" est important pour libérer les ressources y compris au niveau Serveur de base ce données (Cursor etc..).

[ShaiLeTroll]

A lire : Création dynamique TADODataSet

Ton GetQuery ne libère pas en cas d'exception, il ne fait qu'affecter à nil, c'est déjà une 1ère fuite mémoire
Le GetQuery dans le sujet cité plus haut évitait cela !

Et je confirme les propos de hmira que l'appel à GetQuery est peut-être mal géré, mais comme suiX évoque du PascalScript cela complique l'affaire !
A savoir que le Destroy du TDataSet invoque systématiquement le Close !

Il faut savoir que cette fonction est appelé dans un script Pascal (composant PascalScript by Lazarus), et donc j'ai eu des problèmes lorsque je cherchais à libérer le TAdoQuery dans un finally.

Même sans le finally as-tu correctement libéré chaque instance renvoyé par GetQuery ?

Ensuite si c'est dans une boucle ET que le SQL ne bouge pas,
il serait préférable de faire une nouvelle fonction de script CreatePreparedQuery

Il te faut appeler CreatePreparedQuery pour chaque Query, 1 pour le SELECT, 2 pour le INSERT\UDPATE
Ton import ira plus vite si tu conserves tes requêtes paramétrées ET préparées !
C'est ce que j'ai fait pas plus tard qu'hier pour 250 000 lignes sur Oracle
avec 2 requêtes préparés un SELECT de contrôle et un INSERT (dans une table tampon d'import) et j'aurais peut-être à ajouter l'UPDATE (manque de spec pour le moment)
Mieux vaut créer 3 TADOQuery au début et les préparer
Que d'en créer 70000 en boucle, perte de temps côté client, perte de temps côté serveur, perte de temps réseau à envoyer 70000 le même SQL alors que seul les valeurs changent (peut-être 50% de réseau en moins)

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
function CreatePreparedQuery(const ASql :string):TDataSet;
begin
   Result := TAdoQuery.create(NIL);
   try
     Result.connection := vgAdoConnectionCible;
     Result.SQL.Add(ASQL);
     AddDebug(TraceQuery(Result));
     Result.Prepared := True; // charge la requete sur le server DB mais ne l'exécute pas !
   except
     on E:Exception do
     begin
       FreeAndNil(Result);
       Inc(vgNbError);
       AddLog('ERREUR CreatePreparedQuery: '+ ASql + ' '+E.Message);
     end;
   end;
end;

Le SQL contient des paramètres, ensuite dans le code, il suffit d'utiliser ParamByName et de faire le Open,
Cela peut être bien plus rapide !

[hmira]

- Merci ShaiLeTroll pour ces précisions intéressantes.
- La notion de "Préparer un DataSet" (Prepared) est une notion très subtile, souvent mal comprise par les développeurs et pourtant, son impact est très important sur les performances notamment sur le "Cache Plan" au niveau Serveur de base données.

Sous SQL Server, j'ai tracé les événements Serveur (SQL Server) survenus avec et sans prepared, et ce, pour une instruction simple (sélection d'un client à partir de son Id). ci-dessous le résultat, ie, ce qui se passe derrière les coulisses :

CAS A : Avec Prepared

Code PASCAL :

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
procedure TForm1.Button2Click(Sender: TObject);
var
  lADOQuery : TADOQuery;
begin
  with ADOConnection1 do
    begin
       if Connected then
         Connected := False;
       Connected := True;
    end;
 
   lADOQuery := TAdoQuery.create(NIL);
     try
         try
       with lADOQuery do
       begin
         { 1 -------------- }
         if Active then
           Close;
         Connection := ADOConnection1;
         SQL.Clear;
         SQL.Add('SELECT * FROM dbo.Client WHERE IdClient = :IdClient' );
 
         Parameters.Clear;
         with Parameters.AddParameter do
             begin
           Name := 'IdClient';
           Attributes := [paSigned];
           DataType := ftInteger;
           Precision := 10;
           Value := Null;
             end;
         { 2 ------------------ }
           Prepared := True;   { <<<<<<<   Très important  }
 
         { 3 ------------------ }
         Parameters.ParamByName('IdClient').Value := 100;
         Open;
 
         { 4 ------------------ }
         if Active then
           Close;
         Parameters.ParamByName('IdClient').Value := 200;
         Open;
 
         { 5 -------------- }
         if Active then
           Close;
         { ------------------ }
             end;
         except
             on E:Exception do
             begin
               FreeAndNil(lADOQuery);
             end;
         end;
   finally
        if Assigned(lADOQuery) then
        begin
           lADOQuery.Close;
       FreeAndNil(lADOQuery);
           end;
   end;
 
 with ADOConnection1 do
    begin
       if Connected then
         Connected := False;
    end;
end;

CAS B : Sans Prepared

Code PASCAL :

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

1
2
3
   { Idem, mais avec la ligne ci-dessous mise en commentaire } 
.... 
//           Prepared := True;   { <<<<<<<   Très important  }

RESULTAT

RESULTAT CAS A : Avec Prepared

Code SQL :

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

1
2
3
4
5
6
7
8
9
 
-- Etape 3 :
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT * FROM dbo.Client WHERE IdClient = @P1',100
select @p1
 
-- Etape 4 :
exec sp_execute 2, 200

Conclusion CAS A : OK
Requête paramétrées utilisant le même handle (@p1=2) du cache plan (plan d'exécution de la requête)
==> Très bon pour les performance du fait de la réutilisation du cache plan de la requête.


RESULTAT CAS B : Sans Prepared

Code SQL :

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

1
2
3
4
-- Etape 3 :
exec sp_executesql N'SELECT * FROM dbo.Client WHERE IdClient = @P1',N'@P1 int',100
-- Etape 4 :
exec sp_executesql N'SELECT * FROM dbo.Client WHERE IdClient = @P1',N'@P1 int',200

Conclusion CAS B : KO
Requêtes SQL "Ad hoc" non paramétrées, ce qui est très mauvais pour les performances et pour le cache plan des requêtes ((plan d'exécution de la requête). le cache plan n'étant pas réutilisé !

A+

[suiX-]

Bonjour,

Je vous remercie pour tous vos conseils et vos retours (comme d'habitude ce forum et encore plus ce sous forum sont une mine d'or en terme de réponses et de connaissances).

J'ai mis en application pas mal de vos conseils. Mais malheureusement, je me suis rendu compte que la fuite mémoire ne venait pas de mes différents TAdoQuery (je pensais avoir bien tracé cette fuite au départ, mais en fait non). Donc j'étudie encore le problème. En attendant je ferme la discussion et merci encore pour vos conseils avisés.