How to show the elapsed time while a long SQL Query is executed?

Question

I need to show a popup window before a query execution, show the time elapsed while the sql query is being executed, and close that window when the query ends.

Actually I do something like this

var
  frm : tFrmPopupElapsed;
  // this form has a TTimer and a TLabel to show the elapsed time
  // but the label is not updated, I tried using update and refresh
  // but nothing happens
begin
  frm := tFrmPopupElapsed.Create(nil);
  try
    frm.Init; // this procedure enables the timer
    frm.Show();
    ExecuteMyVeryLongQuery();
  finally
    frm.Close; 
  end;
end;

How can the label be updated to show the elapsed time while the query executes? Using a timer? Or a thread?

Answer 1

This question is a lot more complex than initially envisaged; which makes it a good question.

Initially I thought Application.processmessages was the right way, however it is a potential minefield unless you're careful (thanks @skamradt for pointing this out). It also doesn't help with a single blocking call.

A background thread is needed as follows: (thanks @mghie for pointing out the errors which are now resolved). There may still be issues with the database object being called in different threads - so the background thread may need to have its own database connection for this operation (if practicable).

In the example below I've not specifically shown the code for creating and destroying the progress window as it will make the code even longer, and its easy to do.

So we need two objects to do this:

Firstly the background thread to process the query.

unit BackgroundProcess;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs, windows;

const
  WM_MY_BACKGROUNDNOTIFY = WM_USER + 555; { change this }
  NOTIFY_BEGIN = 22;
  NOTIFY_END   = 33;

type
  TBackgroundQueryThread = class(TThread)
  private
    hwndnotify : HWND;
  protected
    procedure Execute; override;
  public
    constructor Create(owner: TForm);
  end;


implementation

constructor TBackgroundQueryThread.Create(owner: TForm) ;
begin
    inherited Create(False);
    hwndnotify := owner.Handle;
    FreeOnTerminate := true;
    resume;
end;

procedure TBackgroundQueryThread.Execute;
begin
     PostMessage(hwndnotify, WM_MY_BACKGROUNDNOTIFY, NOTIFY_BEGIN, 0);
     Sleep(2000); (* Query goes here. *)
     PostMessage(hwndnotify, WM_MY_BACKGROUNDNOTIFY, NOTIFY_END, 0);
end;

end.           

The form that invokes the query:

unit mainform;

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
  StdCtrls, ExtCtrls, windows, BackgroundProcess;

type
  TForm1 = class(TForm)
  private
    frm : tFrmPopupElapsed;
    { private declarations }
    procedure OnMyBackgrounNotify(var Msg: TMessage); message WM_MY_BACKGROUNDNOTIFY;
  public
    { public declarations }
  end; 

var
  Form1: TForm1; 

implementation


procedure TForm1.OnMyBackgrounNotify(var Msg: TMessage);
begin
  if (msg.WParam = NOTIFY_BEGIN) THEN
  BEGIN
     if (frm = nil) THEN
     BEGIN
         frm := tFrmPopupElapsed.Create(nil);
         frm.Init; // this procedure enables the timer
         frm.Show();
     END;
  END;

  if (msg.WParam = NOTIFY_END) THEN
  BEGIN
     if (frm <> nil) THEN
     BEGIN
         frm.Close;
     END;
  END;

end;

end.

Answer 2

You will need to run the query in a background thread if you want the user interface to be responsive during the query execution. If the query supports cancellation, you could also add a cancel button. I believe only ADO queries support this, however.

Answer 3

You need to run the query asynchronously, allowing you to update the Form in the meantime.
The simplest way to do it without getting your hands dirty with the threads is to use Andreas Hausladen's AsynCalls library.
You can also give a look at the OmniThread Library by Primoz Gabrijelcic.