t e m p o r a l

d o o r w a y

Performing A Query In A Thread That Links To A Form Grid

Introduction

As a relational database grows more complex, it is not unusual to have a number of SQL queries displayed in grids - and those queries typically need to be refreshed if any one of the underlying tables change. The problem is that a the underlying tables grow larger, refreshing the queries takes longer and longer. If you have implemented refreshing the query in the AfterPost of each table, this can cause extremely long and annoying pause between finishing an edit and being able to move to the next row in a grid.

The answer to this problem lies in the C++ Builder TThread class, which allows a program to separate itself into separate independent threads of execution. Your normal C++ Builder program consists of a single thread, often referred to as the "main thread". You can, within that thread, spawn other threads, and those threads can affect the controls on any of the forms in your project - handled carefully.

Sharing, Synchronization, Events, and Deadlock

A thread is a class like any other. Its interface can consist of properties, variables, and methods. That interface can be accessed by any thread which can access the thread class instance. However - that sharing must be handled carefully.

Two threads are running. Under pre-emptive multitasking, a thread can be suspended by the operating system at any time, during any instruction. Thus, complex operations may not be completed before a thread is suspended to let some other thread run. If the other thread accesses an only partly completed data structure of the first thread, it may operate incorrectly or it may abort.

TThread provides a special method called "Synchronize". It takes the name of a method in the thread. When called, it calls the specified method, but it does so in the context of the main thread. This allows threads to access main thread data structures without difficulty. Threads can use this not only to access main thread form controls and data structures, but can also, via those controls and data structures, indirectly communicate with any other threads.

Under a pre-emptive multitasking operating system, it is improper for a thread to use resources to do nothing but wait - for instance by executing an endless loop. The Windows API provides "events", which allow threads to signal each other, and to wait for an event without consuming resources. Events are created, destroyed and activated with the Windows API, using CreateEvent, CloseHandle and SetEvent respectively. A thread waits on an event with WaitSingleObject.

Events come with their own built in danger. If a thread is waiting for an event from the main thread, and the main thread waits on an event from the thread, deadlock can result, with neither thread continuing to execute. For that reason, WaitSingleObject provides for a timeout interval, after which, even if an event has not been signaled, the thread proceeds.

Databases and Threads

Every thread must have its own TSession object to arbitrate access to databases. The main thread automatically gets a hidden TSession object, but your thread must create its own before working with any database or data aware objects.

Interestingly, the main thread can use datasets and datasources within another thread in its data aware controls. And that is what makes this project possible.

The Design

The design needs to meet the following requirements:

Query refreshing must proceed with minimum inteference in the peformance of the main thread.
Query refreshing must show as little as possible of its thread orientation to the main thread or to any of the form controls.

With this in mind, the design plays out as follows:

There will be two threads - the main thread with its main form, and the query thread, which executes the SQL whose result is displayed in a grid on the main form.
The query thread will create its own data source and table, and connect those to the grid in the main thread.
After the query thread starts and creates / attaches everything, it will wait on an event in a loop. When the event is signaled, it will detach everything from the grid, refresh the query, and then reattach to the grid (reasons given below).
The main thread will signal the query thread by calling the Refresh() function of the thread, which will set the event.
Terminate() on the thread will set the event, and the thread will check every loop iteration to see if the Terminated property (set by Terminate()) is set.

The Implementation

Here is the header file. The thread implementation is entirely in the header file for convenience.

//---------------------------------------------------------------------------
#ifndef MainFormH
#define MainFormH
//---------------------------------------------------------------------------
#include <vcl\Classes.hpp>
#include <vcl\Controls.hpp>
#include <vcl\StdCtrls.hpp>
#include <vcl\Forms.hpp>
#include <vcl\DBTables.hpp>
#include <vcl\DB.hpp>

class TThreadedQuery; // Forward declaration to be resolved below

class TThreadedSQLForm : public TForm
{
   __published:   // IDE-managed Components

      TDBGrid *RealTableGrid;
      TDBGrid *SQLGrid;
      TLabel *RealTableLabel;
      TLabel *SQLTableLabel;
      TButton *BackgroundRegenButton;
      PVTable *Table;
      TDataSource *Source;

      void __fastcall FormShow(TObject 
      *Sender);

      void __fastcall BackgroundRegenButtonClick(TObject 
      *Sender);

private: // User declarations

TThreadedQuery *myThreadedQuery;

   public:      // User 
      declarations

      __fastcall TThreadedSQLForm(TComponent* 
      Owner);

      __fastcall ~TThreadedSQLForm(void);
};
//---------------------------------------------------------------------------
extern TThreadedSQLForm *ThreadedSQLForm;
//---------------------------------------------------------------------------
class TThreadedQuery: public TThread
{
   public:

      typedef TThread Superclass;

   private:

      TSession    *Session;
      TQuery      *Query;
      TDataSource *Source;

      HANDLE   RefreshOrTerminateRequestedEvent;

   public:

      __fastcall TThreadedQuery(void): 
      TThread(TRUE)

{

         RefreshOrTerminateRequestedEvent 
      = CreateEvent(NULL,FALSE,FALSE,NULL);

         FreeOnTerminate 
      = TRUE;

         Session = new 
      TSession(NULL);

            Session->SessionName 
      = "Thread"+IntToStr(ThreadID);

         Query = new 
      TQuery(NULL);

            Query->Name 
      = "ThreadedSQL";

            Query->DatabaseName 
      = "d:\\test\\Threaded SQL";

            Query->SessionName 
      = Session->SessionName;

         Source = new 
      TDataSource(NULL);

            Source->Name 
      = "ThreadedQuerySource";

            Source->DataSet 
      = Query;

         Priority = tpLower; 
      // Keep the priority lower than the main thread

         Resume();
      };

      __fastcall ~TThreadedQuery(void)
      {
         delete Source;
         delete Query;
         delete Session;
         CloseHandle(RefreshOrTerminateRequestedEvent);
      };

      void __fastcall Refresh(void) 
      // Called by client

{

         SetEvent(RefreshOrTerminateRequestedEvent); 
      // Windows API

      };

      void __fastcall Terminate(void)
      {
         Superclass::Terminate();

         SetEvent(RefreshOrTerminateRequestedEvent); 
      // Windows API

      };

   private:

      void __fastcall LinkToGrid(void)
      {

         ThreadedSQLForm->SQLGrid->DataSource 
      = Source;

      };

      void __fastcall UnlinkFromGrid(void)
      {

         ThreadedSQLForm->SQLGrid->DataSource 
      = NULL;

      };

      void __fastcall Execute(void)
      {

         Query->SQL->Add("select 
      * from Test");

         Query->Active 
      = TRUE;

         Synchronize(LinkToGrid);

         while (!Terminated)
         {

            WaitForSingleObject(RefreshOrTerminateRequestedEvent,INFINITE); 
      // Windows API

            if 
      (!Terminated)

{
Synchronize(UnlinkFromGrid);

               Query->Active 
      = FALSE;

               Query->Active 
      = TRUE;

               Synchronize(LinkToGrid);
            };
         };
      };
};

#endif

The body is relatively simple, mostly concerned with starting and terminating the thread:

//---------------------------------------------------------------------------
#include <vcl\vcl.h>
#pragma hdrstop

#include "MainForm.h"
//---------------------------------------------------------------------------
#pragma resource "*.dfm"
TThreadedSQLForm *ThreadedSQLForm;
//---------------------------------------------------------------------------
__fastcall TThreadedSQLForm::TThreadedSQLForm(TComponent* Owner)
   : TForm(Owner)
{
}
//---------------------------------------------------------------------------
__fastcall TThreadedSQLForm::~TThreadedSQLForm(void)
{
   myThreadedQuery->Terminate();
}
//---------------------------------------------------------------------------
void __fastcall TThreadedSQLForm::FormShow(TObject *Sender)
{
   myThreadedQuery = new TThreadedQuery();
}
//---------------------------------------------------------------------------

void __fastcall TThreadedSQLForm::BackgroundRegenButtonClick(TObject 
      *Sender)

{
myThreadedQuery->Refresh();
}
//---------------------------------------------------------------------------

The Rationale Behind Disconnecting

Because the Synchronize method executes in the context of the main thread, it is a safe bet that the main thread cannot progress while the method being synchronized is being executed. Since that method, in this case, would be opening and closing the query, it might as well be run in the main thread if it were in the body of the method being synchronized. Thus, the thread disconnects the query from the grid, the query is closed and opened in the thread, and then the query is reconnected in a Synchronize, giving true parallel operation.

Caution

Remember that all of the tables used by the thread end up implicitly owned by the thread as if the thread were another user. This means that attempts to gain exclusive access from the main thread to a table used by the query in the thread will fail, since the thread has its own database session. You will need to signal the thread to release the table prior to attempting exclusive access.

Conclusion

Developing a threaded query refresh is not only fairly simple, but it reveals very little of its nature to the caller. Obviously, some work would need to be done to make the thread a particpant in data awareness so that the main thread could simply be connected to it at design time. Have fun getting that done!