Using Firedac in a multithreaded app

Viewed 4245

I am currently working on a multithreaded server app and I plan to use Firedac for data access. From the docs provided here: http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Multithreading_(FireDAC), it seems that the same TFDConnection and/or TFDQuery should not be accessed from multiple threads at the same time (instead these objects should be created on per-thread basis).

Therefore, the example shown in the previous link, centralizes the TFDConnection and TFDQuery in a TThread object. However, in my case I don't have control on thread creation (which is managed by the server environment). I am therefore limiting the lifecycle of my TFDConnection and TFDQuery objects to the lifetime of a procedure, which can be potentially called from multiple threads:

procedure TAPMFiredacTemplate.executeSQL(sql:string);
  var
  oConn: TFDConnection;
  oQuery: TFDQuery;
  begin
  oConn := TFDConnection.Create(nil);
  oQuery := TFDQuery.Create(nil);
  try
    oConn.ConnectionDefName := self.ConnectionDefinitionName;
    oConn.Connected := True;
    oQuery.Connection := oConn;
    oQuery.Open(sql);
    while not oQuery.Eof do
    begin
      // process query
      oQuery.Next;
    end;

  finally
    if assigned(oQuery) then
    begin
      oQuery.Close;
      oQuery.Free;
    end;
    if assigned (oConn) then
    begin
      oConn.Connected := False;
      oConn.Free;
    end;

  end;

Is this approach valid? Is there a performance hit by systematically creating the TFDQuery object?

Note: in order to improve performance I am planning to use a private pooled connection definition (which is used by the TFDConnection). So from my understanding even when I free the TFDConnection, the physical connection is not destroyed but returned to the pool instead:

oParams := TStringList.Create;
oParams.Add('Database=localhost:c:\apm\databases\mydb.FDB');
oParams.Add('User_Name=xxxxx');
oParams.Add('Password=xxxxxx');
oParams.Add('Pooled=True');
FDManager.AddConnectionDef('Firebird_Pooled','FB',oParams);
FDManager.Active := True;
1 Answers
Related