Reduce Firebird memory consumption when closing Dataset

Question

I have an app Delphi XE7 which rotates on a seat Firebird 2.5.
When I open a FDTable with a 10,000 records for example, Firebird server memory consumption increases by about 1 MB. So far so good, it’s part.
But when I close Table (FDTable.Close;) memory consumption does not decrease... continues with the 1 MB that increased when the table was opened.
The same goes for FDQuerys and also with other databases.

I realized that when I disconnected the FDConnection from the bank, then the consumption goes back to normal. But at the moment I can’t do it in my system, at least not in the short term.

There is some way that when a dataset is closed, proportionally reduce SGDB consumption on the server?

Answer 1

I’ve been working with Firebird for a long time, the problem may even be open transactions.

Here we do not usually use the FDTable, we use our own component, but it’s the same.

I’ll give you an example with TDataSet, which is the basis of everything.

var
  vDataSet_Teste : TDataSet;
begin
  vDataSet_Teste := ResultSQL('SELECT ALGUMA_COISA FROM ALGUM_LUGAR');

  while not vDataSet_Teste.Eof do
  begin
    Self.Caption := vDataSet_Teste.FieldByName('ALGUMA_COISA').AsString;
    vDataSet_Teste.Next;
  end;

  vDataSet_Teste.Close;
end;

For this example, consider that ResultSQL return me a dataset of data.

vDataSet_Teste.Close; closes only the possibility to read the data, but does not remove them from memory, because in this scenario the ResultSQL opened a transaction, consulted the data and handed it to me for consideration. That way we still have an open transaction, because all DataSet in Delphi is passed as memory, in this case the dataset that was loaded by ResultSQL still exists and has a transaction with the Server.

The way we found 100% effective was to use a command set for the dataset. It is a TQuery or a TDataSet:

vDataSet_Teste.Close;
vDataSet_Teste.Free;

Remember that in this format, we do not use visual components.

Answer 2

Have a routine to reduce the memory you placed inside a timer or after your process:

    procedure TForm1.Timer1Timer(Sender: TObject);
    begin
        if (((GetTickCount - LastTick) / 1000) > 120) or
             (Self.WindowState = wsMinimized) then
        begin
            //Memo1.Lines.Add(FormatFloat('Memory used: ,.# K', CurrentMemoryUsage / 1024));
            TrimAppMemorySize;
            //Memo1.Lines.Add('Reduz memória');
            //Memo1.Lines.Add(FormatFloat('Memory used: ,.# K', CurrentMemoryUsage / 1024));
        end;
    end;

The routine to free up memory:

    procedure TrimAppMemorySize;
    var
        MainHandle : THandle;
    begin
        try
            MainHandle := OpenProcess(PROCESS_ALL_ACCESS, false, GetCurrentProcessID) ;
            SetProcessWorkingSetSize(MainHandle, $FFFFFFFF, $FFFFFFFF) ;
            CloseHandle(MainHandle) ;
        except
        end;
        Application.ProcessMessages;
    end;

To check the memory:

    function CurrentMemoryUsage: Cardinal;
    var
        pmc: TProcessMemoryCounters;
    begin
        pmc.cb := SizeOf(pmc) ;
        if GetProcessMemoryInfo(GetCurrentProcess, @pmc, SizeOf(pmc)) then
            Result := pmc.WorkingSetSize
        else
            RaiseLastOSError;
    end;