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

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

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

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

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

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

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.

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.

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.

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.

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

Sorry, I am using FormCreate, FormDestroy, and FormClick... you will need to assign those.

Hi Experts

Thank you all , I am having a look at all the examples now

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

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.

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.

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

Thank you very much

Henry