Recent

Author Topic: multithreading, refresh datasets  (Read 10216 times)

tatamata

  • Hero Member
  • *****
  • Posts: 734
    • ZMSQL - SQL enhanced in-memory database
multithreading, refresh datasets
« on: October 22, 2007, 10:46:31 pm »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1891

tatamata

  • Hero Member
  • *****
  • Posts: 734
    • ZMSQL - SQL enhanced in-memory database
multithreading, refresh datasets
« Reply #2 on: October 23, 2007, 12:23:49 pm »
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

  • Full Member
  • ***
  • Posts: 163
multithreading, refresh datasets
« Reply #3 on: October 23, 2007, 04:16:22 pm »
What RDBMS are using you?

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

Regards.

tatamata

  • Hero Member
  • *****
  • Posts: 734
    • ZMSQL - SQL enhanced in-memory database
multithreading, refresh datasets
« Reply #4 on: October 23, 2007, 05:45:56 pm »
Firebird/ZeosLib

theo

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1891
multithreading, refresh datasets
« Reply #5 on: October 23, 2007, 06:52:46 pm »
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

  • Hero Member
  • *****
  • Posts: 734
    • ZMSQL - SQL enhanced in-memory database
multithreading, refresh datasets
« Reply #6 on: October 23, 2007, 07:57:20 pm »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 1891
multithreading, refresh datasets
« Reply #7 on: October 23, 2007, 08:35:20 pm »
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

  • Full Member
  • ***
  • Posts: 163
multithreading, refresh datasets
« Reply #8 on: October 23, 2007, 09:22:07 pm »
Quote from: "tatamata"
Firebird/ZeosLib


AFAIR you must have a connection per thread with Firebird.

Regards.

tatamata

  • Hero Member
  • *****
  • Posts: 734
    • ZMSQL - SQL enhanced in-memory database
multithreading, refresh datasets
« Reply #9 on: November 06, 2007, 02:03:59 pm »
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....

 

TinyPortal © 2005-2018