Solved

Refresh tables in separate thread

Posted on 2006-07-14
8
256 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:Delphiwizard
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Assisted Solution

by:ciuly
ciuly earned 250 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:Delphiwizard
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:ciuly
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
 
LVL 17

Accepted Solution

by:
Wim ten Brink earned 250 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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:Delphiwizard
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:Delphiwizard
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now