Thread concurrent Update Performance

Data Abstract
1

Hi,

My Environment: Delphi 2010
RO/DA: 8.1.87.1147
Db:Firebird 2.5
Connection Driver: AnyDAC?AuxDriver=IB;Server=localhost;Database=DATA.FDB;UserID=SYSDBA;Password=masterkey;Protocol=TCPIP;Characterset=utf8;

I break the table applydate into a few thread and run it in concurrently.
I expect the update time should be less than a main thread in a simple loop.

Simple Loop is:

srcTable.First;
While Not srcTable.Eof Do
begin
TargetTable .Close;
TargetTable .Open;
some calculation logic;
TargetTable .Edit;
change field value here
TargetTable .Post;
TargetTable .Applyupdate;
end;

In Concurrent Thread:

I Partition the data into equal section.
Then execute each section Data Updat and create all requried component as below within each thread.

ClientChannel := TROSuperTCPChannel.Create(Nil);
ClientChannel.Port := TROSuperTCPChannel(Task.Param[2].AsObject).Port;
ClientChannel.Host := TROSuperTCPChannel(Task.Param[2].AsObject).Host;

ROBinMessage:=TROBinMessage.Create;
DataStreamer:=TDABin2DataStreamer.Create(Nil);
DataStreamer.SendReducedDelta := True;
RemoteService:=TRORemoteService.Create(Nil);
RemoteService.Message := ROBinMessage;
RemoteService.ServiceName := ‘DataService’;
RemoteService.Channel := ClientChannel;
DARemoteDataAdapter:= TDARemoteDataAdapter.Create(Nil);
DARemoteDataAdapter.RemoteService := RemoteService;
DARemoteDataAdapter.DataStreamer := DataStreamer;
DAMemDataTable:= TDAMemDataTable.Create(Nil);
DAMemDataTable.LogicalName := ‘CAPR_ROLL_DET’;
DAMemDataTable.RemoteDataAdapter := DARemoteDataAdapter;

srcThreadTable.First;
While Not srcThread.Eof Do
begin
TargetThreadTable .Close;
TargetThreadTable .Open;
some calculation logic;
TargetThreadTable .Edit;
change field value here
TargetThreadTable .Post;
TargetThreadTable .Applyupdate;
end;

In My i5 4-cpu machine, the sample for simple loop and thread the time is nearly the same.

If I try to change server connection pool size or change channel result is same.

Please advise how can I gain the maximum performance on concurrent thread update.

I suppose thread update is faster than a simple loop as thread run concurrently.
But I dont know which setting block the performance.
By The way I m using omnithread library to do the thread.
I attached my testing project for your checking , you can check the (Frame Woker way) or (Parrallel Task) Button it is thread. Simple Loop is Just a Loop without thread.

Please advise Thank You

2

look like you forgot to attach testcase…

3

O Yes I forgot SorryThreadTest.zip (102.5 KB) , I attached it now

4

DB isn’t included into testcase so I can’t test it.
Can you replace DevEx components with standard ones, include db, and reattach testcase?

How much time is required for updating data in single and concurrent thread modes?
You can add some delay before Post, (0.5-1 sec) and compare update time again. will be concurrent thread mode faster than single mode?

5

I am preparing the data and will attach it soon. At the same time, I tested your suggestion if I add delay in the simple loop it take around 9 min . In thread it take around 3 min.

Mean thread really run it concurrently right?

Any suggestion?

6

yep, this mean that your code works correctly -

looks like you have received similar results at beginning because you have not very much records in table(s) so say, updating 20 records in main thread are similar to updating 5 records in 4 threads.

7

Thank you for your explain. Actually the first time I test Loop take 37 Second and Thread take 35 seconds
And update record is ~2000 record.

But now I run the same test again with the less amount of data but update same no of row ( only 2000 row record in db) . Original DB over 100,000 row. I find the thread faster than loop which is expected in new testing db.

I check the DB side Different first. Anyway Thank you for your suggestion.

One Point, My approach on create all the required component within thread is correct right?
Any Better Approach?

8

Now you have situation when each thread perform 25% of updates and send them to server. server also received them in 4 threads and apply to server simultaneously.

You can improve your performance as:
1st thread performs 10% updates and sends them to server
2nd thread performs 20% updates and sends them to server
3rd thread performs 30% updates and sends them to server
4th thread performs 40% updates and sends them to server
ofc, you can play with % which each thread performs.

as a result, you will decrease workload of your app and DB server because they will apply updates consistently and not simultaneously.

you can simplify it as

  DARemoteDataAdapter:= TDARemoteDataAdapter.Create(Nil);
  lRemoteDataAdapter.DataServiceName := 'DataService';
  lRemoteDataAdapter.TargetURL := 'supertcp://host:port/bin';
  DARemoteDataAdapter.DataStreamer.SendReducedDelta := True;

  DAMemDataTable:= TDAMemDataTable.Create(Nil);
  DAMemDataTable.LogicalName := 'CAPR_ROLL_DET';
  DAMemDataTable.RemoteDataAdapter := DARemoteDataAdapter;