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