Multithreaded Delphi Database Queries

By design, a Delphi application runs in one thread. To speed up some parts of the application you might want to decide to add several simultaneous paths of execution in your Delphi application.

Multithreading in Database Applications

In most scenarios, database applications you create with Delphi are single threaded—a query you run against the database needs to finish (processing of the query results) before you can fetch another set of data.

To speed up data processing, for example, fetching data from the database to create reports, you can add an additional thread to fetch and operate on the result (recordset).

Continue reading to learn about the 3 traps in multithreaded ADO database queries:

1. Solve: "CoInitialize was not called".
2. Solve: "Canvas does not allow drawing".
3. Main TADoConnection cannot be used!

Customer Order Scenario

In the well-known scenario where a customer places orders containing items, you might need to display all the orders for a particular customer along the total number of items per each order.

In a "normal" single threaded application you would need to run the query to fetch the data then iterate over the recordset to display the data.

If you want to run this operation for more than one customer, you need to sequentially run the procedure for each of the selected customers.

In a multithreaded scenario you can run the database query for every selected customer in a separate thread—and thus have the code execute several times faster.

Multithreading in dbGO (ADO)

Let's say you want to display orders for 3 selected customers in a Delphi list box control.

 type

   TCalcThread = class(TThread)

private

     procedure RefreshCount;

protected

     procedure Execute; override;

public

     ConnStr : widestring;

     SQLString : widestring;

     ListBox : TListBox;

     Priority: TThreadPriority;

     TicksLabel : TLabel;

     Ticks : Cardinal;

   end;

This is the interface part of a custom thread class we are going to use to fetch and operate on all the orders for a selected customer.

Every order gets displayed as an item in a list box control (ListBox field). The ConnStr field holds the ADO connection string. The TicksLabel holds a reference to a TLabel control that will be used to display thread executing times in a synchronized procedure.

The RunThread procedure creates and runs an instance of the TCalcThread thread class.

 function TADOThreadedForm.RunThread(SQLString: widestring; LB:TListBox; Priority: TThreadPriority; lbl : TLabel): TCalcThread;

var

   CalcThread : TCalcThread;

 begin

   CalcThread := TCalcThread.Create(true) ;

   CalcThread.FreeOnTerminate := true;

   CalcThread.ConnStr := ADOConnection1.ConnectionString;

   CalcThread.SQLString := SQLString;

   CalcThread.ListBox := LB;

   CalcThread.Priority := Priority;

   CalcThread.TicksLabel := lbl;

   CalcThread.OnTerminate := ThreadTerminated;

   CalcThread.Resume;

   Result := CalcThread;

 end;

When the 3 customers are selected from the drop down box, we create 3 instances of the CalcThread:

 var

   s, sg: widestring;

   c1, c2, c3 : integer;

 begin

   s := ' SELECT O.SaleDate, MAX(I.ItemNo) AS ItemCount ' +

        ' FROM Customer C, Orders O, Items I ' +

        ' WHERE C.CustNo = O.CustNo AND I.OrderNo = O.OrderNo ' ;

   sg := ' GROUP BY O.SaleDate ';

   c1 := Integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]) ;

   c2 := Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]) ;

   c3 := Integer(ComboBox3.Items.Objects[ComboBox3.ItemIndex]) ;

   Caption := '';

   ct1 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c1, sg]), lbCustomer1, tpTimeCritical, lblCustomer1) ;

   ct2 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c2, sg]), lbCustomer2, tpNormal,lblCustomer2) ;

   ct3 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c3, sg]), lbCustomer3, tpLowest, lblCustomer3) ;

 end;

Traps and Tricks With Multithreaded ADO Queries

The main code goes in the thread's Execute method:

 procedure TCalcThread.Execute;

var

   Qry : TADOQuery;

   k : integer;

 begin

inherited;

//CoInitialize was not called

   Qry := TADOQuery.Create(nil) ;

try// MUST USE OWN CONNECTION // Qry.Connection := Form1.ADOConnection1;

     Qry.ConnectionString := ConnStr;

     Qry.CursorLocation := clUseServer;

     Qry.LockType := ltReadOnly;

     Qry.CursorType := ctOpenForwardOnly;

     Qry.SQL.Text := SQLString;

     Qry.Open;

     while NOT Qry.Eof and NOT Terminated do

     begin

       ListBox.Items.Insert(0, Format('%s - %d', [Qry.Fields[0].asString,Qry.Fields[1].AsInteger])) ;

       //Canvas Does NOT Allow Drawing if not called through Synchronize

       Synchronize(RefreshCount) ;

       Qry.Next;

     end;

finally

     Qry.Free;

   end;

   CoUninitialize() ;

 end;

There are 3 traps you need to know how to solve when creating multithreaded Delphi ADO database applications:

1. CoInitialize and CoUninitialize must be called manually before using any of the dbGo objects. Failing to call CoInitialize will result in the "CoInitialize was not called" exception. The CoInitialize method initializes the COM library on the current thread. ADO is COM.
2. You *cannot* use the TADOConnection object from the main thread (application). Every thread needs to create its own database connection.
3. You must use the Synchronize procedure to "talk" to the main thread and access any controls on the main form.