Recent

Author Topic: TSQLQuery (memory leak)  (Read 11098 times)

mith

  • New Member
  • *
  • Posts: 13
TSQLQuery (memory leak)
« on: December 30, 2013, 11:18:55 pm »
Hi,
I'm trying to export data to a *.csv file using a TSQLQuery (connected to a postgres database). Everything works fine for a small results. When the result is bigger (for example 80k rows, each with 6 fields of varchar 255 - which isn't of course huge amount of data), my program exhausts all possible memory (more than 1,5GB). I'm wondering what's wrong. During search in google and this forum I found some similar problems but none resolved.
Did anyone met with this problem?
I made tests on Windows 7 64b, Win 8.1.
Application was compiled on Lazarus (stable) 1.0.10.
Please find below a part of code:

Code: [Select]
          try
            AssignFile(plik, Utf8ToAnsi(SaveDialog.FileName));
            Rewrite(plik);
            Form1.QueryReports.First;

            Count := Form1.QueryReports.FieldCount - 1;
            pg.Max := Form1.QueryReports.RowsAffected;
            pg.Position := 0;

            Form1.Datasource1.Enabled := False;
            Form1.Datasource1.DataSet := nil;

            //nagłówek
            for i := 0 to Count do
              begin
                if i = Count then
                  Writeln(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"')
                else
                  Write(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"' + ',');
              end;

            //zawartość pliku
            while not Form1.QueryReports.EOF do
              begin
                for i := 0 to Count do
                  begin
                    if i = Count then
                      begin
                        tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"';
                        tmp_string:= StringReplace(tmp_string, #13, ' ', [rfIgnoreCase, rfReplaceAll]);
                        tmp_string:= StringReplace(tmp_string, #10, ' ', [rfIgnoreCase, rfReplaceAll]);
                        Writeln(plik, tmp_string);
                      end
                    else
                      begin
                        tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"' + ',';
                        tmp_string:= StringReplace(tmp_string, #13, ' ', [rfIgnoreCase, rfReplaceAll]);
                        tmp_string:= StringReplace(tmp_string, #10, ' ', [rfIgnoreCase, rfReplaceAll]);
                        Write(plik, tmp_string);
                      end;
                  end;
                Form1.QueryReports.Next;
                pg.Position := pg.Position + 1;
              end;

            CloseFile(plik);
            ShowMessage(Form1.GetLanText('Eksport zakończony!'));
            pg.Position := 0;
            Form1.Datasource1.DataSet := Form1.QueryReports;
          except
            on E: Exception do
              Form1.Log('ERROR >> ' + E.Message + ' ' + IntToStr(E.HelpContext));
          end;   

Thanks in advance

BlueIcaro

  • Hero Member
  • *****
  • Posts: 576
Re: TSQLQuery (memory leak)
« Reply #1 on: December 30, 2013, 11:28:06 pm »
Try to compile your program with program, with use HeauTrc checked. You can find it on: Options project (shift+ctrl+F11)-->> linking.

When you close it, you will see some info about the use of memory, and if there is some leak (http://www.freepascal.org/docs-html/rtl/heaptrc/


/BlueIcaro
Remenber, the lazarus wiki is your friend: http://wiki.lazarus.freepascal.org/Main_Page
General questions (several lenguages) http://wiki.lazarus.freepascal.org/

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #2 on: December 31, 2013, 09:17:57 am »
Many thanks BlueIcaro for your quick answer.
I did as you wrote. It's confirmed that I have a memory leak, but unfortunately I think it's inside the SQLQuery unit.  (I turned off "write" to a file, keeping simply iterating through SQLQuery by using '.next').
I made a DumpHeap after each 10000 records saved. You can see results below. Don't know what next, probably create a bug?

After first 10000 records:
Code: [Select]
Heap dump by heaptrc unit
1002361 memory blocks allocated : 886168757/890603048
944126 memory blocks freed     : 452070306/456341968
58235 unfreed memory blocks : 434098451
True heap size : 448954368 (112 used in System startup)
True free heap : 10104368
Should be : 10966136
Call trace for block $087554F8 size 28
  $004CABEA  TFORMA_RAPORTY__MENUITEM14CLICK,  line 200 of form_raporty.pas
  $00601568  TMENUITEM__CLICK,  line 83 of ./include/menuitem.inc
  $00601B26  TMENUITEM__DOCLICKED,  line 278 of ./include/menuitem.inc
  $0040C0F6
  $75877694
  $75878BAA
  $75878468
  $75879C40
Call trace for block $087A07B8 size 14
  $004CABBD
  $00601568
  $00601B26
  $0040C0F6
  $75877694
  $75878BAA
  $75878468
  $75879C40
Call trace for block $4140A270 size 42764
[...]

On 20000 records:
Code: [Select]
Heap dump by heaptrc unit
1828722 memory blocks allocated : 1735404672/1743811016
1760487 memory blocks freed     : 873666230/881869944
68235 unfreed memory blocks : 861738442
True heap size : 885948416 (112 used in System startup)
True free heap : 18618432
Should be : 19640192
[...]

On 30000 records:
Code: [Select]
Heap dump by heaptrc unit
2660904 memory blocks allocated : 2585303174/2597715304
2582669 memory blocks freed     : 1295924723/1308094224
78235 unfreed memory blocks : 1289378451
True heap size : 1322942464 (112 used in System startup)
True free heap : 27132464
Should be : 28314232
[...]

After a while: RunError(203), EOutOfMemory

Once again - thanks for your help.

BlueIcaro

  • Hero Member
  • *****
  • Posts: 576
Re: TSQLQuery (memory leak)
« Reply #3 on: December 31, 2013, 07:23:34 pm »

Code: [Select]
Heap dump by heaptrc unit
1002361 memory blocks allocated : 886168757/890603048
944126 memory blocks freed     : 452070306/456341968
58235 unfreed memory blocks : 434098451
True heap size : 448954368 (112 used in System startup)
True free heap : 10104368
Should be : 10966136
Call trace for block $087554F8 size 28
  $004CABEA  TFORMA_RAPORTY__MENUITEM14CLICK,  line 200 of form_raporty.pas
(..) 


Hi, the first think you should  check is the procedure Menuitem14click, in the line 200 of form_raporty.pas, here acording to the memory leak report, you have same object that never is free.

/BlueIcaro
Remenber, the lazarus wiki is your friend: http://wiki.lazarus.freepascal.org/Main_Page
General questions (several lenguages) http://wiki.lazarus.freepascal.org/

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #4 on: January 01, 2014, 11:52:50 am »
Thanks BlueIcaro,
Menuitem 14 Click is a procedure to export data to csv. The code from this procedure I posted in first message.
I reported an issue here: http://bugs.freepascal.org/view.php?id=25477

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TSQLQuery (memory leak)
« Reply #5 on: January 01, 2014, 12:08:54 pm »
Unfortunately the bug you reported is not reproducible the way it is now - please read the note on the bug report.

(Also: TSQLQuery is part of FPC - the fcl-db package - which is different from Lazarus. If you are sure the problem lies with tsqlquery, please provide an *FPC* sample project - see the link in the bug report on an example. If you think the problem has to do with Lazarus use of db components, please provide a Lazarus example - but please also see the link: the program should be self-contained so it can be used to reproduce the bug).

Thanks.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #6 on: January 01, 2014, 02:11:16 pm »
Sample lazarus app has been added with DDL. (Into reported bug)
Thanks and sorry

BlueIcaro

  • Hero Member
  • *****
  • Posts: 576
Re: TSQLQuery (memory leak)
« Reply #7 on: January 01, 2014, 02:31:40 pm »
What do you have on line 200?.
Why this:
Code: Pascal  [Select]
  1. Form1.Datasource1.DataSet := nil;
  2.  

/BlueIcaro
Remenber, the lazarus wiki is your friend: http://wiki.lazarus.freepascal.org/Main_Page
General questions (several lenguages) http://wiki.lazarus.freepascal.org/

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #8 on: January 01, 2014, 02:42:34 pm »
The report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process.
Simple and complete application example I have added to reported bug (it is without displaying and unnecessary code).

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TSQLQuery (memory leak)
« Reply #9 on: January 01, 2014, 02:57:12 pm »
Sample lazarus app has been added with DDL. (Into reported bug)
Thanks and sorry
No problem. Simplified it to an FPC console program and see the problem in FPC 2.6.2 x86 and trunk x86 on Windows.

Will comment on bug report if I find out more.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mangakissa

  • Hero Member
  • *****
  • Posts: 948
Re: TSQLQuery (memory leak)
« Reply #10 on: January 01, 2014, 03:08:03 pm »
Quote from: mith
The report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process.
Use enable/disablecontrontrols to set your display of.
Code: [Select]
begin
   try
     SQLQuery.disablecontrols;
     ......
  finally
     SQLQuery.enablecontrols;
  end;
end;
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #11 on: January 01, 2014, 03:17:26 pm »
Quote from: mith
The report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process.
Use enable/disablecontrontrols to set your display of.
Code: [Select]
begin
   try
     SQLQuery.disablecontrols;
     ......
  finally
     SQLQuery.enablecontrols;
  end;
end;

Thank you for your advice.

mith

  • New Member
  • *
  • Posts: 13
Re: TSQLQuery (memory leak)
« Reply #12 on: January 05, 2014, 11:39:40 pm »
I'm trying to export data to a *.csv file using a TSQLQuery (connected to a postgres database). Everything works fine for a small results. When the result is bigger (for example 80k rows, each with 6 fields of varchar 255 - which isn't of course huge amount of data), my program exhausts all possible memory (more than 1,5GB). I'm wondering what's wrong. D

For anyone who has this problem.
Please use UniDirectional:=True (before Open dataset) on TSQLQuery. It should help.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: TSQLQuery (memory leak)
« Reply #13 on: January 07, 2014, 02:03:05 pm »
Mmm yes. I suppose building memory management into bufdataset is asking too much...

Closing the bug and added some docs
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memory_errors

@@Lacak: I suppose you're right that there's nothing much we can do about bufdataset.

Have added some docs
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memory_errors

@Michał: please close the bug report if it's ok with you. Thanks.
« Last Edit: January 07, 2014, 02:09:54 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified