Reduce FireBird memory consumption when closing Dataset

Working with Firebird for a long time, the problem may even be open transactions.

Here we do not usually use FDTable , we use a component of its own, but it's the same thing.

I'll give you an example with TDataSet itself, 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 returns me a data dataset.

vDataSet_Teste.Close; closes only the ability to read the data, but does not remove it from memory, because in this scenario ResultSQL opened a transaction, consulted the data, and handed it to me for consideration. This 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. If it is a TQuery or a TDataSet :

vDataSet_Teste.Close;
vDataSet_Teste.Free;

Note that in this format we do not use visual components.

You have a routine to reduce memory that is 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 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;