FireDac freezes GUI

Question

I am working with FireDac under Delphi 10.1 Berlin.

For displaying data to the user i use data aware controls like TDBEdit.

I use TFDQuery and TDataSource to link them with the controls.

This works but long sql queries that take some time to exectute will freeze the GUI.

I am wondering how to stop the gui from freezing while performing those long running queries.

I was thinking about background threads.

On the wiki i read that FireDac can work with multithreads: http://docwiki.embarcadero.com/RADStudio/XE6/en/Multithreading_(FireDAC)

However in embarcadero community forums thread Jeff Overcash writes:

One thing I didn't see asked or Dmitry mention is you can not have TDataSource or LiveBindings against your background threaded queries. If you are background threading a query that displays the results you should disconnect the LB or DataSource, open and fetch all the data then re establish the connection.

Those two will be trying to move the cursor on you or querying the buffer for display while the buffer is very volatile being moved around in a different thread.

I am wondering if someone that also uses FireDac and displays the values on a form can help me out here.

Answer 1

The code sample below shows one way to retrive records from an MSSql Server in a background thread using FireDAC. This omits a few details. For example, in practice, rather than the TQueryThreads Execute opening the query only once and then terminating, you would probably want the thread's Execute to contain a while loop in which it waits on a semaphore after the call to Synchronize and then close/re-open the query to update the main thread as often as you want.

type

  TForm1 = class;

  TQueryThread = class(TThread)
  private
    FConnection: TFDConnection;
    FQuery: TFDQuery;
    FForm: TForm1;
  published
    constructor Create(AForm : TForm1);
    destructor Destroy; override;
    procedure Execute; override;
    procedure TransferData;
    property Query : TFDQuery read FQuery;
    property Connection : TFDConnection read FConnection;
    property Form : TForm1 read FForm;
  end;

  TForm1 = class(TForm)
    FDConnection1: TFDConnection;
    FDQuery1: TFDQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  public
    QueryThread : TQueryThread;
  end;

[...]

constructor TQueryThread.Create(AForm : TForm1);
begin
  inherited Create(True);
  FreeOnTerminate := True;
  FForm := AForm;
  FConnection := TFDConnection.Create(Nil);
  FConnection.Params.Assign(Form.FDConnection1.Params);
  FConnection.LoginPrompt := False;

  FQuery := TFDQuery.Create(Nil);
  FQuery.Connection := Connection;
  FQuery.SQL.Text := Form.FDQuery1.SQL.Text;
end;

destructor TQueryThread.Destroy;
begin
  FQuery.Free;
  FConnection.Free;
  inherited;
end;

procedure TQueryThread.Execute;
begin
  Query.Open;
  Synchronize(TransferData);
end;

procedure TQueryThread.TransferData;
begin
  Form.FDQuery1.DisableControls;
  Form.FDQuery1.Data := Query.Data;
  Form.FDQuery1.EnableControls;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  QueryThread.Resume;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  QueryThread := TQueryThread.Create(Self);
end;

MJN's comment about bookmarks tells you how to preserve the current data row position in the gui.

Btw, although I've often done this with TClientDataSets, putting this answer together was the first time I'd tried it with FireDAC. In terms of configuring the components, all I did was to drag the components off the Palette, "wire them together" as you'd expect and then set the FDConnection's Params and the FDQuery's Sql.