Delphi: TAdoQuery Memory Leak?

Question

I'm developing as small diabetes program using Delphi 5 and ADO. I do a little query like this:

function GetLowestGlucoseLevel(StartDate:string;EndDate:string): Integer;
var
  Q:TADOQuery;
begin
   try
      Q:=TADOQuery.Create(Application); //Separate unit, owner set to App
      Q.Connection:=dtMod.ADOCon;
      Q.DisableControls;
      Q.Close;
      Q.SQL.Clear;
      Q.SQL.Add('SELECT Min(qGlucose.Glucose) AS MinOfGlucose from qGlucose');
      Q.Parameters[0].Value:=StartDate;
      Q.Parameters[1].Value:=EndDate;
      Q.Open;

      Result:=Q.FieldByName('MinOfGlucose').AsInteger;

      Q.Close;
    finally
      Q:=nil;
      Q.Free; 
    end; 
end;

The query runs OK and returns the result as expected. However, when I checked Windows Task Manager, memory usage keeps on rising rather than decreasing after the query.

How to fix this?

Thanks!

Answer 1

You are leaking the TADOQuery by first setting it to nil, and then calling Free on the nil variable (which does nothing)

Answer 2

• Did you install Delphi 5 updates? The RTM ADO implementation is known to have issues.
• Use FastMM4, it should work with Delphi 5 as well, and tell you more about where the leaks are.

Answer 3

The Delphi way:

function GetLowestGlucoseLevel(const StartDate:string; const EndDate:string): Integer;
var
  Q:TADOQuery;

begin

    Q:=TADOQuery.Create(nil); //(nil) because local use only. Placed before try\finally block 
                              //because if it fails to .create then there would be no object to
                              //.Free 
    try

      Q.Connection := dtMod.ADOCon;

      //------can erase these------
      //Q.DisableControls; //No controls attached so unnecessary
      //Q.Close;           //Q is local and was never opened so no need to close
      //Q.SQL.Clear;       //Q is local and was never filled so no need to clear

      Q.SQL.Add('SELECT Min(qGlucose.Glucose) AS MinOfGlucose from qGlucose');
      Q.Parameters[0].Value:=StartDate;
      Q.Parameters[1].Value:=EndDate;
      Q.Open;

      Result := Q.FieldByName('MinOfGlucose').AsInteger;

      Q.Close;

    finally 

      Q.Free;

      //Q := nil          //not needed because Q's scope is local

    end; 
end;

Answer 4

Quote:

finally
  Q:=nil;
  Q.Free; 
end; 

You're kidding, right? First nil the variable, then free it? You're a genius! :-)

Use:

finally
  Q.Free; 
  Q:=nil;
end; 

Or don't even bother assigning nil to it, since Q is a local variable...

---

But rereading your code, I notice you use Application as owner. As a result, it will not really be a leak, since it will be freed when the application is freed. If you use a form, it would be freed when the owner form gets freed.
What you should try is to call this query about 100.000 times to check if it keeps reserving memory or if it's just increasing memory until a certain size has been reached. The latter is more likely, since the memory is reserved for future ADO calls.

Answer 5

As others pointed out, the finally section should have the 2 statements reversed, like so:

finally
  Q.Free; 
  Q:=nil;  // <- not even necessary since this is a local var
end; 

Or you can call SysUtils.FreeAndNil(Q) (if that is available in Delphi 5, not sure).

Beside that, the TaskManager is an awful instrument to determine memory usage anyway. You might release the memory for Q, but that does not automatically mean the Delphi memory manager releases the memory to the OS.

Answer 6

Aside inversing the lines as Arjan, jasonpenny and WorkShop Alex said, you can use Process Explorer to see the real consumption of memory (Private Bytes) of the process. Task Manager is not really suited to this task, as it only shows the working set of the process.