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?

Dismuke

What database? At least one that I know (DBISAM / ElevateDB) offers a very handy OnProgress event on their query component. You can code in their to report elapsed time, percent complete, etc. – Primulaceous

small suggestion: I would put the try before the frm.Init – Nylanylghau

Note that calling frm.Close will result in memory leaks unless the form has an OnClose handler that sets caFree. Using frm.Release would be much safer. – Furmark

Answer 1

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.

Poised

+1 for beating me to almost the same answer. (I was going to suggest Omnithread first, and asyncalls second) :) – Mothball

Either of these is probably better than using a TThread descendant. But executing a query in a different thread may be problematic. It's of course necessary to block any work with the connection objects in parallel in the main thread, unless the connection objects explicitly allow parallel access from different threads (most probably not the case with VCL classes). There may be additional restrictions, like having to execute all queries in the same thread in which the connection was established. See 17slon.com/blogs/gabr/2009/02/building-connection-pool.html for example. – Furmark

+1 Thanks Very much for you advice, the AsynCalls library works perfect. – Dismuke

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.

Okelley

Answer 3

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.

Baerl

Be very careful of using Application.ProcessMessages as it can easily cause other problems if your not careful, and should generally be treated as the last thing you would want to do, not the first. Look for another solution, such as a background thread, first. – Mothball

You're right - I remeber now that you can get into terrible reentrancy problems unless you're careful. So the correct approach is just a thread and a ttimer - keeping the background thread for the update. – Baerl

-1 for calling VCL code from a background thread, and for calling methods of components that may be freed already. – Furmark

You're right - it's a terrible answer - I'd downvote it myself if I could. I'll fix it properly with messages and post it by way of penance. – Baerl

@Furmark - I think I've fixed it properly now: your thoughts? – Baerl

Thanks for taking the time to fix it, I've taken the down vote back. It's much better now. There is however the whole problem of database access not necessarily working from different threads, even when only one thread is active at any given time. That's however a general problem, which can not be worked around. AsyncCalls or the OTL would not help there either. The only way to do it if this applies is to put all database access into the worker thread, but that's beyond the scope of this question. – Furmark

@mghie. Thanks for removing the downvote (and for downvoting when it was bad). I agree about the database access, it might work bit it could fail, depending on the database and I'm not comfortable with anything that could fail – Baerl