multithreading, refresh datasets
lazarus / 16-Databases

----------------------------------------------------------------------
tatamata · 2007-10-22 20:46

Hello.
I have a form with 6 dbgrids. Sometimes I need to refresh all 6
datasets. It takes a lot of time to refresh them consecutively.
Therefore I tried to create separate threads for every dataset to be
refreshed. Unfortunately I get an Access violation error. Can you help
me with this?

Code: [Select]
//Interface

type
  TThreadRefreshDatasets=class(TThread)
  private
    FDataset:TZQuery;
    FBookmark:TBookmark;
  protected
    procedure Execute; override;
  public
    constructor Create(pDataset:TZQuery);
  end;


//Implementation

constructor TThreadRefreshDatasets.Create(pDataset:TZQuery);
begin
  inherited Create(True);
  FDataset:=pDataset;
  FreeOnTerminate:=True;
  Resume;
end;

procedure TThreadRefreshDatasets.Execute;
begin
  try
    if FDataset.Active=True then begin
      //Get bookmark.
      FBookmark:=FDataset.GetBookmark;
      //Disable controls.
      FDataset.DisableControls;
      //Refresh the dataset.
      FDataset.Refresh;
      //Goto bookmark
      FDataset.GotoBookmark(FBookmark);
    end;
  finally
    //Free bookmark.
    FDataset.FreeBookmark(FBookmark);
    //Enable controls.
    FDataset.EnableControls;
  end;
end;

//This procedure refreshes the datasets.
procedure RefreshOrdersRequirementsDatasets;
var
  I:Integer;
  vElapsedTime:TDateTime;
begin
  try
    ShowMessage('Program will now refresh all datasets on the form.
Please, be patient.');
    vElapsedTime:=Now;
    with FormMain do begin
      //Create threds for refreshing the dataset.
      TThreadRefreshDatasets.Create(ZQueryQryOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryRqrmts);
      TThreadRefreshDatasets.Create(ZQueryQryFrmMtrls);
      TThreadRefreshDatasets.Create(ZQueryQryFrmMtrlsOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryFrmOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryFrmOrdrsMtrls);
    end;
    vElapsedTime:=Now-vElapsedTime;
    ShowMessage('Datasets refreshed. Elapsed time:
'+FormatDateTime('hh:nn:ss',vElapsedTime));
  except
    ShowMessage('Error while refreshing datasets.');
  end;
end;

----------------------------------------------------------------------
theo · 2007-10-22 22:05

Synchronize
http://wiki.lazarus.freepascal.org/Multithreaded_Application_Tutorial

----------------------------------------------------------------------
tatamata · 2007-10-23 10:23

Do you suggest that I should wrap Dataset.Refresh into Synchronize?
If so, then mutithreading would be worthless, because a method inside
Synchronize is executed in the main thread, so multithreading would be
reduced to one thread...

----------------------------------------------------------------------
ssamayoa · 2007-10-23 14:16

What RDBMS are using you?

Check if the RDBMS's client library is thread safe and can share a
connection.

Regards.

----------------------------------------------------------------------
tatamata · 2007-10-23 15:45

Firebird/ZeosLib

----------------------------------------------------------------------
theo · 2007-10-23 16:52

Quote from: "tatamata"Do you suggest that I should wrap
Dataset.Refresh into Synchronize?

I don't know database-stuff well, but afair EnableControls is updating
the DB-Aware (Visual-) Controls.
Whenever you access the GUI from a thread, you have to do this in
Synchronize (main thread context).

----------------------------------------------------------------------
tatamata · 2007-10-23 17:57

Ok. Thanks for answer, I did it.
But, performance is exactly the same as without threading (I measured)
which proves my assumption. Synchronize executes the passed method in
the main thread, therefore the whole thing bacomes worthless in this
case...
Does anyone has an idea how to solve this? Or maybe multithreading is
not solution for my problem?
Code: [Select]
//Interface

type
  TThreadRefreshDatasets=class(TThread)
  private
    FDataset:TZQuery;
    FBookmark:TBookmark;
    procedure MyRefresh;
    procedure MyEnableControls;
    procedure MyDisableControls;
    procedure MyGetBookmark;
    procedure MyGotoBookmark;
    procedure MyFreeBookmark;
  protected
    procedure Execute; override;
  public
    constructor Create(pDataset:TZQuery);
  end;

var
  vThreadCount:Integer;

//Implementation

constructor TThreadRefreshDatasets.Create(pDataset:TZQuery);
begin
  inherited Create(True);
  FDataset:=pDataset;
  FreeOnTerminate:=True;
  Resume;
end;

procedure TThreadRefreshDatasets.MyDisableControls;
begin
   FDataSet.DisableControls;
end;

procedure TThreadRefreshDatasets.MyEnableControls;
begin
   FDataSet.EnableControls;
end;

procedure TThreadRefreshDatasets.MyRefresh;
begin
   FDataSet.Refresh;
end;

procedure TThreadRefreshDatasets.MyGetBookmark;
begin
   FDataSet.GetBookmark;
end;

procedure TThreadRefreshDatasets.MyGotoBookmark;
begin
   FDataset.GotoBookmark(FBookmark);
end;

procedure TThreadRefreshDatasets.MyFreeBookmark;
begin
   FDataset.FreeBookmark(FBookmark);
end;

procedure TThreadRefreshDatasets.Execute;
begin
  try
    if FDataset.Active=True then begin
      //Get bookmark.
      FBookmark:=FDataset.GetBookmark;
      //Disable controls.
      Synchronize(@MyDisableControls);     //FDataset.DisableControls;
      //Refresh the dataset.
      Synchronize(@MyRefresh);          //FDataset.Refresh;
      //Goto bookmark
      Synchronize(@MyGotoBookmark);      
//FDataset.GotoBookmark(FBookmark);
    end;
  finally
    //Free bookmark.
    Synchronize(@MyFreeBookmark);      
//FDataset.FreeBookmark(FBookmark);
    //Enable controls.
    Synchronize(@MyEnableControls);         //FDataset.EnableControls;
    vThreadCount:=vThreadCount+1;
  end;
end;    

procedure RefreshOrdersRequirementsDatasets;
var
  I:Integer;
  vElapsedTime:TDateTime;
begin
  try
    ShowMessage('Program will now refresh all datasets on the form.
Please, be patient.');
    vElapsedTime:=Now;
    with FormMain do begin
      //Create threads for refreshing the dataset.
      TThreadRefreshDatasets.Create(ZQueryQryOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryRqrmts);
      TThreadRefreshDatasets.Create(ZQueryQryFrmMtrls);
      TThreadRefreshDatasets.Create(ZQueryQryFrmMtrlsOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryFrmOrdrs);
      TThreadRefreshDatasets.Create(ZQueryQryFrmOrdrsMtrls);
    end;
    //Wait until all threads finish.
    vThreadCount:=0;
    while (vThreadCount<6) do begin
      Application.ProcessMessages;
    end;
    vElapsedTime:=Now-vElapsedTime;
    ShowMessage('Datasets refreshed. Elapsed time:
'+FormatDateTime('hh:nn:ss',vElapsedTime));
  except
    ShowMessage('Error while refreshing datasets.');
  end;
end;

----------------------------------------------------------------------
theo · 2007-10-23 18:35

If the bottleneck is in "EnableControls", then Multi-threading won't
help you at all.
Besides the fact that this has to be done in the main-thread context
anyway, multi-threading in my expierence does not necessarily speed-up
things (at least on single processor systems).
It's good for having a responsive GUI during lenghty, simultaneous
operations (like blocking-socket operations), or for Servers to handle
simultaneous connections etc.

Maybe some DB-Gurus can help you better.

----------------------------------------------------------------------
ssamayoa · 2007-10-23 19:22

Quote from: "tatamata"Firebird/ZeosLib

AFAIR you must have a connection per thread with Firebird.

Regards.

----------------------------------------------------------------------
tatamata · 2007-11-06 13:03

I tried multithreading for some other cases where no data-aware
controls are involved, only non-visual components (such as ZQuery), so
without Synchronize. And performance is always equal or worse than
without multithreading. Therefore, I conclude that multithreading is
worthless for working with databases...It seems that data retrieval
from hard disk is limiting factor, so multithreading doesn't help....