1

I am debugging an application that uses ADO for database connectivity, mostly TAdoConnection and TAdoQuery After some testing it turned out that the TAdoQuery keeps consuming and not releasing memory. The problem can be reproduced quite easily with this code:

procedure TForm1.RunQueries;
var
  Q: TADOQuery;
  Conn: TADOConnection;
begin
  Conn := TADOConnection.Create(nil);
  Conn.ConnectionString := 'Provider=PGNP.1;Password=*****;User ID=*****;Data Source=*****;Initial Catalog=*****;Extended Properties="PORT=5432"';
  Conn.LoginPrompt:=False;
  Conn.Connected := true;

  Q := TADOQuery.Create(nil);
  Q.Connection := Conn;
  Q.SQL.Text := 'select * from sometable where extract(year from now()-Field1)::int/60>=15 or Field2>=50 limit 5';

  q.Open;
  q.Close;
  FreeAndNil(Q);
  FreeAndNil(Conn);
end;

If this is run with a timer at some interval (e.g. 200ms) the memory consumed keeps rising with various speeds (20-50MB per hour). The SQL text itself is not really relevant. It also consumes memory with 'select * from Table1', only slower. ExecSQL with a 'delete ...' statement does not seem to cause a problem.

I did some tests with GetProcessMemoryInfo and it seems that the memory is consumed and not released after the Open method is called. Not all executions resulted in the same memory increase though.

This happens at the development server with PostgreSQL and different ADO providers, but I could not reproduce it with MySQL. Other apps using the ADO provider from http://www.pgoledb.com seem to be working properly, so the problem is not only in the provider I tried AQTime and FastMM4, but both report no leaks. The code built with D6 and XE2 works the same.

I found this question Delphi: TAdoQuery Memory Leak?, but the problem there was caused by errors in the code.

My problem is similar to this error report http://qc.embarcadero.com/wc/qcmain.aspx?d=7018.

Do you think this is a bug in Delphi and is there a workaround?

An update: This problem actually appears even if the objects are static. E.g a Connection and Query components which are put on the form, and the connection is kept open. Only the Query SQL text is changed and executed.

I tried to install a different provider from PGfoundry.org, but the results are strange for me.

The memory leaks appear with both Postgres providers on different OSs, but not with MySQL. I am not sure what that means though. If it is a VCL problem, shouldn't it be always present? If not, what layer is causing it considering that it happens with different providers for the same DB server?

Community
  • 1
  • 1
VGeorgiev
  • 491
  • 5
  • 16
  • Did you update the MDAC of the system to the latest release? Did you try accessing directly to the OleDB layer - you may try [our Open Source units](http://blog.synopse.info/post/2011/06/27/SynOleDB%3A-OpenSource-Unit-for-direct-access-to-any-database-via-OleDB). – Arnaud Bouchez Jun 12 '12 at 11:21
  • The QC comments suggest trying to use server side cursors instead of client side. Did you test this? – whosrdaddy Jun 12 '12 at 12:50
  • I updated the MDAC and tested on both Server 2008 and 2003 with the same results unfortunately. The cursors also had no effect on the memory consumption. As for the direct access, I have not tried it yet as I want to avoid rewriting too many portions of the code, which is quite large and not written by me. I will try it also if there are no other options. – VGeorgiev Jun 13 '12 at 05:57
  • One option would be to change driver for example http://www.devart.com/ – Roland Bengtsson Jun 13 '12 at 16:14
  • Since it is apparently linked to the SQL query itself, try to test with and without calling the functions in `extract(year from now()-Field1)`, i.e. replacing `now()` by a constant , then `extract(...)`. Then try to change 1 part of the query at a time to see if it makes a difference... Divide and conquer ;-) – Francesca Jun 13 '12 at 18:00
  • Well, the SQL query has an effect as it seems complicated ones take memory faster. I checked with different ones at the beginning, but it didn't help get to the cause of it :) It seems we will have to move to dbExpress or a third-party component. – VGeorgiev Jun 14 '12 at 08:37
  • Note that [QualityCentral has now been shut down](https://community.embarcadero.com/blogs/entry/quality-keeps-moving-forward), so you can't access `qc.embarcadero.com` links anymore. If you need access to old QC data, look at [QCScraper](http://www.uweraabe.de/Blog/2017/06/09/how-to-save-qualitycentral/). – Remy Lebeau Jun 09 '17 at 18:02

2 Answers2

1

I don't know if this would apply to your case, but we had similar memory problems in the past with Windows Server 2008 (was also applicable in Win7) connecting to SQL Server.

There were 2 causes:

  1. A MS bug in the MDAC stack that caused a leak when the ConnectionString did not include Persist Security Info=true

  2. A change ("as-designed" behavior) in the Critical Section implementation by MS that would not free debug information.

A possible workaround is to keep the Connections open as much as practical instead of closing and reopening them all the time.

Community
  • 1
  • 1
Francesca
  • 21,452
  • 4
  • 49
  • 90
  • Thank you for the suggestions. The Persist Security Info unfortunately doesn't change the behaviour. I updated my questions above to reflect the latest tests. The problems seems to be with the Query itself, as it is not affected by keeping the connection or not. – VGeorgiev Jun 13 '12 at 06:19
0

I am suffering from the same while connecting to DB2 so your problem is not limited to MS/SQL. I had a thread going on the Embarcadero forums but quit. There was no way I could convince these people that it was something other than my code. Even when I stripped it down top the barest, I got back recommends that had absolutely nothing to do with the issue. Since my process is a 7/24/365 multi-thread program, I finally resigned myself to running it under the scheduler and having it shut itself down each day. It is an ugly fix but I have to be able to invoice my client!!

I also wrote a little test app in C# to see if I could create the same issue. I did not see the memory footpront grow there. This is a Delphi issue.

Meta Mussel
  • 548
  • 1
  • 5
  • 19