Creating a thread to run two database querys at the same time

Good day Experts

Can someone please help me with a example on how to create a thread to run to stored procedures from a firebird database at one time.

At this momement I have two procedures that run after each other to populate my two grids, I would like to have a thread to increase my application performance when loading data.

thank you

henry
henryreynoldsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnjcesCommented:
You are not talking about stored procedures on the database server, are you?

One thing to be certain of is that these two procedures can in fact co-exist and run together and that there are no table dependencies that could screw things up.

OK... I am lazy. I know someone will post some good code on creating threads from scratch but I use the free version of ABF components that has a nice thread component.

http://www.abf-dev.com/

Put your procedures in two threads in the OnExecute event,  then execute the threads.

Be cautious in doing database stuff in threads..

John


0
ziolkoCommented:
>>At this momement I have two procedures that run after each other to populate my two grids

does it mean you use stored procs only to fetch data? if so you'll aviod concurency problems described by Johnjces.

how to use extra threads with db? basically each thread need it's own Query component, it also might need separate connection components but it depends which components you use (ADO works nice with single TADOConnection for all threads).
so create class descending from TThread override Execute; method and within this method make SQL call, problems begin when you want display records returned by SQL command on your form to make it work you'll need synchronization.

it's pretty general question, do you have some code already or need simple demo?

ziolko.
0
henryreynoldsAuthor Commented:
Hi

If possible I simple demo would be nice.

Why should someone be cautious when loading queries in threads ?

Is it wrong of me two use a thread to omptimize my application to populate my two grids.

thanx
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

ziolkoCommented:
>>Is it wrong of me two use a thread to omptimize my application to populate my two grids.

no, it's absolutelly ok

>>Why should someone be cautious when loading queries in threads ?

because of possible AccessViolation errors... lots of them:)

>>If possible I simple demo would be nice.

which components you use?

ziolko.
0
JohnjcesCommented:
If you are just doing queries to simply populate two separate grids with separate data, threads should be fine. One query for each thread.

I must ask, if all you are doing is populating two grids, that should not take that long even in sequence unless you are using a client cursor then ALL the data comes over. A thread or two may not save you that much time unless they are really complex multi-joined queries.

So... we really need to see what you are trying to do.. Your code/query, size of your db/table etc would help.

John
0
henryreynoldsAuthor Commented:
I am using Interbase IBX / or Crlab Firebird components , but a ADO demo is also fine.

Standard DB grid demo is perfect with ADO or IBX, the easiest for you.
0
henryreynoldsAuthor Commented:
I am using Interbase IBX / or Crlab Firebird components , but a ADO demo is also fine.

Standard DB grid demo is perfect with ADO or IBX, the easiest for you.
0
henryreynoldsAuthor Commented:
I am using Interbase IBX / or Crlab Firebird components , but a ADO demo is also fine.

Standard DB grid demo is perfect with ADO or IBX, the easiest for you.
0
ziolkoCommented:
here's form with 2 grids and 2 datasets (hooked to grids)

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, Grids, DBGrids, ADODB, StdCtrls;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOConnection1: TADOConnection;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses Unit2;

procedure TForm1.Button1Click(Sender: TObject);
var t1, t2: TSQLThread;
begin
  ADOConnection1.Open;
  t1 := TSQLThread.Create('select * from Categories', ADOConnection1, DataSource1, True);
  t2 := TSQLThread.Create('select * from Customers', ADOConnection1, DataSource2, False);
end;

end.


here's thread
unit Unit2;

interface

uses Classes, DB, ADODB;

type

  TSQLThread = class(TThread)
  private
    FSQL: string;
    FQuery: TADOQuery;
    FDataSourceRef: TDataSource;
    FWait: Boolean;
    procedure DisplayResults;
  protected
    procedure Execute; override;
  public
    constructor Create(const ASQL: string;AConnection: TADOConnection;ADataSource: TDataSource;AWait: Boolean);
    destructor Destroy;override;
  end;

implementation

uses SysUtils;

{ TSQLThread }

constructor TSQLThread.Create(const ASQL: string;AConnection: TADOConnection;ADataSource: TDataSource;AWait: Boolean);
begin
  inherited Create(False);
  FWait := AWait;
  FSQL := ASQL;
  FQuery := TADOQuery.Create(nil);
  FQuery.Connection := AConnection;
  FDataSourceRef := ADataSource;
end;

destructor TSQLThread.Destroy;
begin
  FreeAndNil(FQuery);
  inherited Destroy;
end;

procedure TSQLThread.DisplayResults;
begin
  FDataSourceRef.DataSet := FQuery;
end;

procedure TSQLThread.Execute;
begin
  if FWait then
    Sleep(3000);
  FQuery.SQL.Text := FSQL;
  FQuery.Open;
  Synchronize(DisplayResults);
end;

end.

as this is very simple demo threads are created but not destroyed.

have fun:)

ziolko.
0
henryreynoldsAuthor Commented:
Hi ziolko

thank you for the demo, I have to go now, why and when must you destroy the threads.

Is there any disadvantaged  if I run this in my application.

thank you very much
0
developmentguruPresidentCommented:
There are many possible problems from executing queries in side threads.  You cannot have your query attached to any display elements while running in a thread or it will break the display synchronization (which can get very ugly).  My example provides an OnQueryFinished that must be specified so you know when the query is done.  This gives you the opportunity to assign it's Q (query object) to a TDataSource for display.  Here is working example code that I threw together.  Look it over yourself and test it.  It works, but I have seen that creating data objects with nil as the parent can cause memory leaks.  This is just intended to give you a starting point.  Just place two grids on a form (leave some form showing), and paste the code.  Adjust the name of the form if necessary.  

Let me know how it goes.
unit Unit2;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, ActiveX, ADODB, DB;
 
type
  TQueryThread = class(TThread)
  private
    fOnQueryFinished : TNotifyEvent;
    fConStr, fQuery : string;
    fCon : TADOConnection;
  protected
    procedure Execute; override;
    procedure QueryFinished;
  public
    Q : TADOQuery;
    constructor Create(ConStr, Query : string; OnQueryFinished : TNotifyEvent);
    destructor Destroy; override;
  end;
 
  TForm2 = class(TForm)
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormClick(Sender: TObject);
  private
    { Private declarations }
    Query1, Query2 : TQueryThread;
    procedure OnQueryFinished(Sender : TObject);
    procedure ReQuery;
  public
    { Public declarations }
  end;
 
var
  Form2: TForm2;
 
implementation
 
{$R *.dfm}
 
const
  ADOConStr = 'Provider=SQLNCLI.1;Password=Ni800x600;' +
    'Persist Security Info=True;User ID=sa;Initial Catalog=VAMUP;' +
    'Data Source=localhost\VAMUP_LOCAL';
 
{ TQueryThread }
 
constructor TQueryThread.Create(ConStr, Query : string;
  OnQueryFinished : TNotifyEvent);
begin
  assert(Assigned(OnQueryFinished), 'An attempt was made to create a query ' +
    'thread with no OnQueryFinished handler');
 
  fConStr := ConStr;
  fQuery := Query;
  fOnQueryFinished := OnQueryFinished;
  inherited Create(false);
end;
 
destructor TQueryThread.Destroy;
begin
  if Assigned(fCon) then
    FreeAndNil(fCon);
  if Assigned(Q) then
    FreeAndNil(Q);
  inherited;
end;
 
procedure TQueryThread.Execute;
begin
  //
  CoInitialize(nil); //needed due to using ADO which is COM
 
  if Assigned(fCon) then
    FreeAndNil(fCon);
  fCon := TADOConnection.Create(nil);
  fCon.ConnectionString := fConStr;
  fCon.LoginPrompt := false;
 
  if Assigned(Q) then
    FreeAndNil(Q);
  Q := TADOQuery.Create(nil);
  Q.Connection := fCon;
  Q.SQL.Text := fQuery;
  Q.Open;
  Synchronize(QueryFinished);
end;
 
procedure TQueryThread.QueryFinished;
begin
  if Assigned(fOnQueryFinished) then
    fOnQueryFinished(Self);
end;
 
{ TForm2 }
 
procedure TForm2.FormClick(Sender: TObject);
begin
  ReQuery;
end;
 
procedure TForm2.FormCreate(Sender: TObject);
begin
  ReQuery;
end;
 
procedure TForm2.FormDestroy(Sender: TObject);
begin
  if Assigned(Query1) then
    FreeAndNil(Query1);
  if Assigned(Query2) then
    FreeAndNil(Query2);
end;
 
procedure TForm2.OnQueryFinished(Sender: TObject);
begin
  if Sender = Query1 then
    DataSource1.DataSet := Query1.Q;
  if Sender = Query2 then
    DataSource2.DataSet := Query2.Q;
end;
 
procedure TForm2.ReQuery;
begin
  if Assigned(Query1) then
    Query1.Free;
  Query1 := TQueryThread.Create(ADOConStr, 'Select * from vam_arinvc',
    OnQueryFinished);
 
  if Assigned(Query2) then
    Query2.Free;
  Query2 := TQueryThread.Create(ADOConStr, 'Select * from vam_arinvc',
    OnQueryFinished);
end;
 
end.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developmentguruPresidentCommented:
Sorry, I am using FormCreate, FormDestroy, and FormClick... you will need to assign those.
0
henryreynoldsAuthor Commented:
Hi Experts

Thank you all , I am having a look at all the examples now
0
ziolkoCommented:
>>thank you for the demo, I have to go now, why and when must you destroy the threads.

in my demo you should destroy threads once you don't need them, note that in this demo when you destroy thread data will disapper from grid:)

slightly changed demo in which you dont have to care about destroying threads, but in this case
dont use ADOQueries while thread is still running

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, Grids, DBGrids, ADODB, StdCtrls;

type
  TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOConnection1: TADOConnection;
    Button1: TButton;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses Unit2;

procedure TForm1.Button1Click(Sender: TObject);
var t: TSQLThread;
begin
  ADOConnection1.Open;
  TSQLThread.Create('select * from Orders', ADOQuery1, True);
  TSQLThread.Create('select * from Products', ADOQuery2, False);
end;

end.

unit Unit2;

interface

uses Classes, DB, ADODB;

type

  TSQLThread = class(TThread)
  private
    FSQL: string;
    FQueryRef: TADOQuery;
    FWait: Boolean;
  protected
    procedure Execute; override;
  public
    constructor Create(const ASQL: string;AQuery: TADOQuery;AWait: Boolean);
  end;

implementation

uses SysUtils;

{ TSQLThread }

constructor TSQLThread.Create(const ASQL: string;AQuery: TADOQuery;AWait: Boolean);
begin
  inherited Create(False);
  FreeOnTerminate := True;
  FWait := AWait;
  FSQL := ASQL;
  FQueryRef := AQuery;
end;

procedure TSQLThread.Execute;
begin
  if FWait then
    Sleep(3000);
  FQueryRef.SQL.Text := FSQL;
  FQueryRef.Open;
end;

end.


ziolko.
0
ziolkoCommented:
... and if you want to know when thread has finished use .OnTerminate event, you don't need to synchronized it, it's already done in VCL

ziolko.
0
developmentguruPresidentCommented:
As with ziolko's example you can destroy the threads when you no longer need access to the respective queries.  By the design of my example you have no access to the query while it is running.  The thread is dormant once it is done, but still contains the conneciton and query.
0
ziolkoCommented:
henryreynolds as you can see there are different approaches to the problem,

little comment do devguru's sample, this sample creates two seperate connections, this is relevant in some databases for example in MSSQL because two different ADOConnections means two different logins to database this is important if you use per connection licensing model.
but I guess it's not the case with firebird.

ziolko.
0
henryreynoldsAuthor Commented:
Hi ziolko , developmentguru

I had a look at both examples, and I can only say thank you very much. I have decided to increase the points to 500, and then split it up 250 a piece.

thank you very much

henry
0
henryreynoldsAuthor Commented:
Thank you very much

Henry
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.