Using Firedac in a multithreaded app

Question

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;

Answer 1

It is a valid approach for thread context execution, but it has a performance penalty in database connection establishment and query preparing with each client request (assuming you are using some Indy server).

To fix the first issue use the connection pooling (you can follow the example).

To fix the latter issue there can be a solution as well. If your server supports keep alive kind of connection, create the query object and prepare the query when the client connects and destroy it when disconnects. This prepared object you can pass to the server request processing method through the extended context class.

For example with TIdTCPServer it could be:

type
  { server context in the meaning of a "task" class }
  TMyContext = class(TIdServerContext)
  private
    FQuery: TFDQuery;
  public
    constructor Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil); override;
    destructor Destroy; override;
    property Query: TFDQuery read FQuery;
  end;

  TForm1 = class(TForm)
    IdTCPServer1: TIdTCPServer;
    FDConnection1: TFDConnection;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure IdTCPServer1Connect(AContext: TIdContext);
    procedure IdTCPServer1Disconnect(AContext: TIdContext);
    procedure IdTCPServer1Execute(AContext: TIdContext);
    procedure IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
  end;

implementation

constructor TMyContext.Create(AConnection: TIdTCPConnection; AYarn: TIdYarn; AList: TThreadList = nil);
begin
  inherited;
  FQuery := TFDQuery.Create(nil);
end;

destructor TMyContext.Destroy;
begin
  FQuery.Free;
  inherited;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  Params: TStrings;
begin
  Params := TStringList.Create;
  try
    Params.Add('Database=localhost:C:\MyDatabase.fdb');
    Params.Add('User_Name=xxxxx');
    Params.Add('Password=xxxxx');
    Params.Add('Pooled=True');
    { add the definition to the global connection manager singleton }
    FDManager.AddConnectionDef('FirebirdPooled', 'FB', Params);
  finally
    Params.Free;
  end;

  { use the added definition and establish the connection to the DB }
  FDConnection1.ConnectionDefName := 'FirebirdPooled';
  FDConnection1.Connected := True;

  { setup the context class, add a port binding and start the TCP server }
  IdTCPServer1.ContextClass := TMyContext;
  IdTCPServer1.Bindings.Add.Port := 6000;
  IdTCPServer1.Active := True;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  { stop the TCP server and destroy all pooled physical connections }
  IdTCPServer1.Active := False;
  FDManager.CloseConnectionDef('FirebirdPooled');
end;

procedure TForm1.IdTCPServer1Connect(AContext: TIdContext);
begin
  { client just connected, assign to the context query object the pooled
    connection and a command text }
  TMyContext(AContext).Query.Connection := FDConnection1;
  TMyContext(AContext).Query.SQL.Text := 'SELECT * FROM MyTable WHERE ID=:ID';
  { preparing the query will acquire one physical connection from the pool
    as this method internally opens the connection }
  TMyContext(AContext).Query.Prepare;
end;

procedure TForm1.IdTCPServer1Disconnect(AContext: TIdContext);
begin
  { client just disconnected, return the physical connection to the pool }
  TMyContext(AContext).Query.Connection.Close;
end;

procedure TForm1.IdTCPServer1Execute(AContext: TIdContext);
var
  ID: Integer;
  Query: TFDQuery;
begin
  { read an integer from socket }
  ID := AContext.Connection.IOHandler.ReadInteger;
  { just a reference helper }
  Query := TMyContext(AContext).Query;

  { fill the parameter and refresh the prepared query object's dataset }
  Query.Params[0].AsInteger := ID;
  Query.Refresh;

  while not Query.Eof do
  begin
    { process the dataset somehow }
    Query.Next;
  end;

  { do not close the dataset, keep it prepared for the next possible request }
end;

procedure TForm1.IdTCPServer1Exception(AContext: TIdContext; AException: Exception);
begin
  if AException is EFDException then
  begin
    { something bad happened with the DB, this is a base FireDAC exception
      class but you can be more specific of course }
  end;
end;