1. Technology

Multithreaded Delphi Database Queries

How to Execute Database Queries using Several Threads

By

Multithreaded Database Queries in Delphi

Multithreaded Database Queries in Delphi

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 - Orders - Items

In the well known scenario where a customer places orders containing items, you might need to display all the orders for a particual 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 - Multithreaded ADO Queries

The main code goes in the thread's Execute method:
 procedure TCalcThread.Execute;
 var
   Qry : TADOQuery;
   k : integer;
 begin
   inherited;
 
   CoInitialize(nil) ; //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.

Be sure to download the demo application to explore and learn more about multithreaded queries with ADO and Delphi.

©2014 About.com. All rights reserved.