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
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
In the AfterRefresh-event of Table1 there are serveral other tables that need to be refreshed.
procedure TForm1.TTable1AfterRefresh
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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)
then
procedure TForm1.TTable1AfterRefresh
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
The solution with Application.ProcessMessage
Not very much, considering that the user has to wait for it.
Any other options are more then welcome.
Not very much, considering that the user has to wait for it.
Any other options are more then welcome.
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.
TForm1(Parameter).TTable3.
TForm1(Parameter).TTable4.
TForm1(Parameter).TTable5.
Result := 0;
end;
procedure TForm1.TTable1AfterRefresh
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.
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.
TForm1(Parameter).TTable3.
TForm1(Parameter).TTable4.
TForm1(Parameter).TTable5.
Result := 0;
end;
procedure TForm1.TTable1AfterRefresh
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.
ASKER
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.
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.
ASKER
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... :-)