TUniQuery and memory management

[cross_join]

Hello,

Indeed, there are two related problems that block using of large datasets.

• After TUniQuery deletion the memory is not released until exiting application. But there is no memory leaks.
• TUniQuery.SmartFetch does not work if UniDirectional is true. After scrolling on few records Query.EOF is true.

This problem is reproduced always but it more critical on large datasets scrolling (100K rows and more). I.e. when scrolling on 10000 rows with column of type varchar(255) the memory consumption is about 188 Kbytes (see results).

Example was built with Delphi 7, same problem with Lazarus.

Code: Select all

program UniMemoryUsage;

{$IFDEF FPC}
  {$MODE Delphi}
{$ENDIF}

{$APPTYPE CONSOLE}

uses
  Classes, SysUtils,
  Windows,
  {$IFDEF FPC}
  jwapsapi,
  {$ELSE}
  PSAPI,
  {$ENDIF}
  Uni, InterBaseUniProvider;

function CurrentProcessMemory: cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess,
  {$IFDEF FPC}
      MemCounters,
  {$ELSE}
      @MemCounters,
  {$ENDIF}
      SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize
  else
    RaiseLastOSError;
end;

var
  Conn: TUniConnection;
  Qry: TUniQuery;
  Row: integer = 0;
  TextValue: string;
  m1, m2: cardinal;

begin
  try
    Conn := TUniConnection.Create(nil);
    Conn.ProviderName := 'InterBase';
    Conn.Database := ExtractFilePath(ParamStr(0)) + 'test.gdb';
    Conn.Server := 'localhost';
    Conn.UserName := 'SYSDBA';
    Conn.Password := 'masterkey';
    Conn.Open;

    m1 := CurrentProcessMemory;
    writeln('Before TUniQuery.Create. Current memory: ', CurrentProcessMemory);
    Qry := TUniQuery.Create(nil);
    Qry.Connection := Conn;
    Qry.SQL.Text := 'SELECT id, name FROM test_memo';
    // Settings to reduce memory usage
    Qry.UniDirectional := true;
    Qry.SpecificOptions.Values['FetchAll'] := 'false';
    Qry.FetchRows := 25;
    //Qry.SmartFetch.Enabled := true;
    //Qry.SmartFetch.LiveBlock := true;
    //Qry.SmartFetch.PrefetchedFields := 'name';
    Qry.Open;
    writeln('Qry.Open. Current memory: ', CurrentProcessMemory);
    while not Qry.EOF do
    begin
      Inc(Row);
      TextValue := Qry.FieldByName('NAME').AsString;
      if Row mod 1000 = 0 then
          writeln('Fetched ', Row, ' rows. Current memory: ', CurrentProcessMemory);
      Qry.Next;
    end;
    Qry.Close;
    writeln('Qry.Close. Current memory: ', CurrentProcessMemory);
    Qry.Free;
    m2 := CurrentProcessMemory;
    writeln('Qry.Free. Current memory: ', m2);
    writeln('Difference, Kbytes: ', (m2 - m1) div 1024);
    Conn.Free;
  except
    on E: Exception do
      writeln('Error: ', E.Message);
  end;
end.
 

Results:

Before TUniQuery.Create. Current memory: 4947968
Qry.Open. Current memory: 5160960
Fetched 1000 rows. Current memory: 5160960
Fetched 2000 rows. Current memory: 5160960
Fetched 3000 rows. Current memory: 5160960
Fetched 4000 rows. Current memory: 5160960
Fetched 5000 rows. Current memory: 5160960
Fetched 6000 rows. Current memory: 5160960
Fetched 7000 rows. Current memory: 5160960
Fetched 8000 rows. Current memory: 5160960
Fetched 9000 rows. Current memory: 5160960
Qry.Close. Current memory: 5160960
Qry.Free. Current memory: 5136384
Difference, Kbytes: 184

[ViktorV]

1. Thank you for the information. We have reproduced the problem - and investigation is in progress. We will inform you when we have any results.
2. Yes, you are right - TUniQuery.SmartFetch doesn't work when TUniQuery.UniDirectional is set to True. The UniDirectional mode is designed for memory saving during navigation through records, and the SmartFetch mode is designed for fast navigation through a large number of records. To save memory in the SmartFetch mode, you can set the TUniQuery.SmartFetch.LiveBlock property to True.

[ViktorV]

We have investigated the issue, but we haven't detected any memory leaks due to UniDAC.
Such behavior is related to memory manager functioning, You can ensure this by running the following code:

Code: Select all

program TestMemory;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  PSAPI, System.SysUtils, Windows;

function CurrentProcessMemory: cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess, {$IFDEF FPC} MemCounters, {$ELSE} @MemCounters, {$ENDIF} SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize
  else
    RaiseLastOSError;
end;

var
  m1, m2: cardinal;
  p: pointer;

begin
  m1 := CurrentProcessMemory;
  GetMem(p, 1);
  m2 := CurrentProcessMemory;
  Writeln('GetMem(p, 1). Memory allocated: ', m2 - m1);
  m1 := CurrentProcessMemory;
  FreeMem(p, 1);
  m2 := CurrentProcessMemory;
  Writeln('FreeMem(p, 1). Memory freed: ', m1 - m2);
  ReadLn;
end.