• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Refresh tables in separate thread

Hi,

In the AfterRefresh-event of Table1 there are serveral other tables that need to be refreshed.

procedure TForm1.TTable1AfterRefresh(DataSet: TDataSet);
begin
  TTable2.Requery;
  TTable3.Requery;
  TTable4.Requery;
  TTable5.Requery;
end;

What I want to do is adding the AfterRefresh-code into a separate thread. This way the user doesn't have to wait till the other tables are refreshed. At least that is the way I think I might increase the performance.

Can anybody supply me with an example on how to set this up (including cleanup of the thread after usage). Any other solutions are welcome too.

Thank you for your help.
Stef
 
0
Stef Merlijn
Asked:
Stef Merlijn
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
2266180Commented:
Hi Delphiwizard,

you have to do something like this:

unit Unit2;

interface

uses
  Classes;

type
  tt = class(TThread)
  private
    { Private declarations }
  protected
    procedure myrun;
    procedure Execute; override;
  end;

implementation

{ Important: Methods and properties of objects in visual components can only be
  used in a method called using Synchronize, for example,

      Synchronize(UpdateCaption);

  and UpdateCaption could look like,

    procedure tt.UpdateCaption;
    begin
      Form1.Caption := 'Updated in a thread';
    end; }

{ tt }

procedure tt.Execute;
begin
  { Place thread code here }
  FreeOnTerminate:=true;// this cleans it up
  Synchronize(myrun);
end;

procedure tt.myrun;
begin
  form1.TTable2.Requery;
  form1.TTable3.Requery;
  form1.TTable4.Requery;
  form1.TTable5.Requery;
end;

end.


Cheers!
0
 
Stef MerlijnDeveloperAuthor Commented:
I don't understand how I execute the thread from within the AfterRefresh-event.
Could you please clearify that?
You also mention some procedures (tt.Execute and tt.myrun). Which procedures should I add to my code and where?

I'm not very familiar with threads as you notice... :-)
0
 
2266180Commented:
all that code you put in a unit called unde2. OR you change the unit name and place it in that file. doesn't matter. than you add that to your uses clause.

then
procedure TForm1.TTable1AfterRefresh(DataSet: TDataSet);
begin
  tt.create(false);// this is sufficient as the thread will free on terminate and you don't need a reference to it anyway.
end;

keep in mynd that TT is the type, the class name (just in case you want to keep a reference)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Wim ten BrinkSelf-employed developerCommented:
Please be careful when doing database actions from a secondary thread. Your database driver might not be threadsafe. Furthermore, there is an additional risk because the tables are refreshed from the separate thread. Thus if your main thread would be altering some data in the table and the second thread is refreshing the same table then it's unpredictable to say what is going to happen. It might go fine. It might cause a crash. Maybe the user just loses the data they just entered. Or perhaps you'll end up with a Blue Screen Of Death, forcing you to restart your computer.

Maybe do this instead:
procedure TForm1.TTable1AfterRefresh(DataSet: TDataSet);
begin
  Application.ProcessMessages;
  TTable2.Requery;
  Application.ProcessMessages;
  TTable3.Requery;
  Application.ProcessMessages;
  TTable4.Requery;
  Application.ProcessMessages;
  TTable5.Requery;
  Application.ProcessMessages;
end;

The Application.ProcessMessages will keep your GUI a bit more responsive while it is refreshing the tables. And you won't need a separate thread with all the additional pains this separate thread will cause.

I have some experience with using multiple threads for database manipulations and my advise is: DON'T DO IT! Unless it's absolutely required and no other solution exists.
0
 
Kyle FosterCEOCommented:
That is correct.  The TDatabase is not thread safe so instead of working around the problem just fix the issue.

Look at the sql code in the queries, add indexes where necessary (any joins and where clauses) and make the queries run at breakneck speed.

otherwise if you need the background thread, you will need a new database connection in the new thread, then you have to... Not worth the risk!
0
 
Stef MerlijnDeveloperAuthor Commented:
The solution with   Application.ProcessMessages; saved 1 second overall.
Not very much, considering that the user has to wait for it.
Any other options are more then welcome.
0
 
Wim ten BrinkSelf-employed developerCommented:
Other options depend on several other factors. For example, the database that you use and the number of indices you have defined and the relations between these indices. From another Q I seem to remember that you're using MS Access, right? Are you using it combined with the BDE? Or are you using ADO to connect to this database?

Using ADO instead of the BDE will likely improve performance a bit more.

Next, are you using TTable components or TQuery components? With Access you can use updateable Queries, meaning that you just write the select statement and Delphi is smart enough to figure out the related update/insert/delete queries. This only works on simple, single-table queries, btw. But the use of TQuery instead of TTable might also improve speed a bit more since the TQuery has a little less overhead than TTable.

More improvements can be gained from optimizing your database. Maybe drop a few table relations or add a few indices. Referential integrity in a database is nice, but it costs some performance. And indices can speed up queries a bit more.

And just keep in mind that Access does has some limitations. Now, I will show you a very simple way to execute those refreshes from inside a thread:
-----------------------------------------
uses
  SysUtils;  

function DoRefresh(Parameter: Pointer): Integer;
begin
  TForm1(Parameter).TTable2.Requery;
  TForm1(Parameter).TTable3.Requery;
  TForm1(Parameter).TTable4.Requery;
  TForm1(Parameter).TTable5.Requery;
  Result := 0;
end;

procedure TForm1.TTable1AfterRefresh(DataSet: TDataSet);
var
  ThreadID: Cardinal;
begin
  BeginThread(nil, 0, DoRefresh, pointer(Self), 0, ThreadID);
end;
-----------------------------------------

But, PLEASE! Be extremely careful with this since you won't know when the thread is done running. If Table1 is refreshed very often then you will end up refreshing the other tables quite a few times and might end up running 50 threads all doing just a refresh.
And worse, this code might not even work since the Database/Table drivers you're using are not thread-safe. But it is the shortest way to start (and stop) a thread.

The thread will stop at the end of the DoRefresh function, btw. And well, it might work just fine but if you're going to use this solution then PLEASE test your application thoroughly. Hire some typing monkeys if need be to just do all kinds of funny stuff with it. It is my experience that using threads with databases tend to be a scenario from Hell, but sometimes you can get very nice solutions.
0
 
Stef MerlijnDeveloperAuthor Commented:
Yes I do use MS Access and TADOTable, but also some TADOQuery (only for lookup and mass-updates).
I think I better stay away from threaded refreshing of the tables.
Maybe I start rebuilding some of my application by using more TADOQuery for day-to-day purposes as well. Wouldn't be a bad thing anyway, as I want to migrate to MS SQL in time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now