Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Refresh tables in separate thread

Posted on 2006-07-14
8
Medium Priority
?
295 Views
Last Modified: 2010-04-05
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
Comment
Question by:Stef Merlijn
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Assisted Solution

by:2266180
2266180 earned 1000 total points
ID: 17106325
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
 

Author Comment

by:Stef Merlijn
ID: 17106358
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
 
LVL 28

Expert Comment

by:2266180
ID: 17106396
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 1000 total points
ID: 17106633
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
 
LVL 7

Expert Comment

by:kfoster11
ID: 17108239
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
 

Author Comment

by:Stef Merlijn
ID: 17108378
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
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 17108614
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
 

Author Comment

by:Stef Merlijn
ID: 17110438
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question