Composant TAdoQuery & problème de mémoire (out of memory)

Version imprimable

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:

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:

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

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

04/08/2014, 20h29
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+
05/08/2014, 07h00
suiX-

Citation:

Envoyé par 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+

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.
05/08/2014, 11h57
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:

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..).

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 !

Citation:

Envoyé par suiX-

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:

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 !

- 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:

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:

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:

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:

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+

08/08/2014, 09h30
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.