Multithreaded Delphi Database Queries

How to Execute Database Queries using Several Threads

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 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.

  TCalcThread = class(TThread)
    procedure RefreshCount;
    procedure Execute; override;
    ConnStr : widestring;
    SQLString : widestring;
    ListBox : TListBox;
    Priority: TThreadPriority;
    TicksLabel : TLabel;
    Ticks : Cardinal;

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;
  CalcThread : TCalcThread;
  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;
  Result := CalcThread;

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

  s, sg: widestring;
  c1, c2, c3 : integer;
  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) ;

Traps and Tricks - Multithreaded ADO Queries

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

 procedure TCalcThread.Execute;
  Qry : TADOQuery;
  k : integer;
  CoInitialize(nil) ; //CoInitialize was not called
  Qry := TADOQuery.Create(nil) ;
// MUST USE OWN CONNECTION // Qry.Connection := Form1.ADOConnection1;
    Qry.ConnectionString := ConnStr;
    Qry.CursorLocation := clUseServer;
    Qry.LockType := ltReadOnly;
    Qry.CursorType := ctOpenForwardOnly;
    Qry.SQL.Text := SQLString;
    while NOT Qry.Eof and NOT Terminated do
      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) ;
  CoUninitialize() ;

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.

More About Delphi Database Programming

mla apa chicago
Your Citation
Gajic, Zarko. "Multithreaded Delphi Database Queries." ThoughtCo, Mar. 27, 2017, Gajic, Zarko. (2017, March 27). Multithreaded Delphi Database Queries. Retrieved from Gajic, Zarko. "Multithreaded Delphi Database Queries." ThoughtCo. (accessed May 22, 2018).