Bacth* Performance issue

coblongpamor · 2012-04-07 04:16:06

I tried to implement BatchUpdate to update the record on the client side.
but its performance is very slow, takes more than 5 minutes.
while, a lot faster if I use EngineExecuteAll on the server side.

what is wrong with the code that I created the following?

constructor TFileClient.Create(const aServer: AnsiString);
begin
  Model:= CreateFileModel(Self);
  Filename:= ChangeFileExt(paramstr(0),'.dat');
  inherited Create(Model,CreateFileModel(Self),Filename,TSQLRestServerDB,False);
  Server.CreateMissingTables(ExeVersion.Version.Version32);
  ...
 end;


 procedure SetWinner(aClient: TFileClient; aPID: Integer; aNumber4: RawUTF8);
var
  aN2,aN3: RawUTF8;
  aSQLMC: TSQLMasterCode;

  procedure StartSendBacth(aSQLNClass: TSQLRecordClass; aMCID: Integer);
  var aSQLN:TSQLBaseNumber;
      aR: TIntegerDynArray;
      aN: RawUTF8;
  begin
    aSQLN:= nil;

    if aSQLNClass = TSQLNumber2 then begin
      aN:= aN2;
      aSQLN:= TSQLNumber2.Create;
    end else
    if aSQLNClass = TSQLNumber3 then begin
      aN:= aN3;
      aSQLN:= TSQLNumber3.Create;
    end else
    if aSQLNClass = TSQLNumber4 then begin
      aN:= aNumber4;
      aSQLN:= TSQLNumber4.Create;
    end;
    if aSQLN = nil then
      Exit;

    with aClient, aSQLN do try
      /// Winner
      BatchStart(aSQLNClass);
      if FillPrepare(aClient,'MasterCodeID = % AND NumberText = %',[aMCID,aN],'ID,IsMatch') then begin
        while FillOne do begin
          IsMatch:= True;
          BatchUpdate(aSQLN);
        end;
        BatchSend(aR);
      end;
      /// Loser
      BatchStart(aSQLNClass);
      if FillPrepare(aClient,'MasterCodeID = % AND NumberText <> %',[aMCID,aN],'ID,IsMatch') then begin
        while FillOne do begin
          IsMatch:= False;
          BatchUpdate(aSQLN);
        end;
        BatchSend(aR);
      end;
    finally
      aSQLN.Free;
    end;
  end;

begin
  if aNumber4 = '' then
    Exit;
  aN2:= Copy(aNumber4,3,2);
  aN3:= Copy(aNumber4,2,3);

  aSQLMC:= TSQLMasterCode.Create;
  with aClient, aSQLMC do
  if FillPrepare(aClient,'PeriodID = %',[aPID],'ID') then try
    if TransactionBegin(TSQLBaseNumber) then
    while FillOne do try
      {Server Side Version}
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number2 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aN2]));
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number2 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aN2]));
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number3 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aN3]));
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number3 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aN3]));
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number4 SET IsMatch = 1 WHERE MasterCodeID = % AND NumberText = %',[ID,aNumber4]));
      //Server.EngineExecuteAll(FormatUTF8('UPDATE Number4 SET IsMatch = 0 WHERE MasterCodeID = % AND NumberText <> %',[ID,aNumber4]));
      {Client Side Version}
      StartSendBacth(TSQLNumber2,ID); //araund 10.890 records
      StartSendBacth(TSQLNumber3,ID); //araund 1.804 records
      StartSendBacth(TSQLNumber4,ID); //araund 1.624 records
      Commit;
    except
      RollBack;
    end;
  finally
    aSQLMC.Free;
  end;
end;

thank you
regards

coblongpamor · 2012-04-07 04:22:43

while, a lot faster if I use EngineExecuteAll on the server side.

it only takes less than 5 seconds.

ab · 2012-04-07 07:16:44

Your code is incorrect: you begin the transaction only one time before your loop.

You have to nest transaction begin to transction commit calls.

Otherwise you won't use transactions, and individual updates will be slow, due to SQLite3 design.

coblongpamor · 2012-04-07 08:01:05

Yes. now feels much faster. takes an average of only 5 seconds.

{Client Side Version}
      if TransactionBegin(TSQLNumber2) then begin
        StartSendBacth(TSQLNumber2,ID);
        Commit();
      end;
      if TransactionBegin(TSQLNumber3) then begin
        StartSendBacth(TSQLNumber3,ID);
        Commit();
      end;
      if TransactionBegin(TSQLNumber4) then begin
        StartSendBacth(TSQLNumber4,ID);
        Commit();
      end;

or you have a better suggestion, to make it faster?
Thank you.

ab · 2012-04-07 11:21:17

I do not see anything else.

You reached the sqlite limit i think ..

You may use our in memory engine instead.

coblongpamor · 2012-04-07 15:28:15

ok, i'll try In Memory ORM latter. i think it is faster than Embedded ORM. isn't it?

mORMot Open Source

#1 2012-04-07 04:16:06

Bacth* Performance issue

#2 2012-04-07 04:22:43

Re: Bacth* Performance issue

#3 2012-04-07 07:16:44

Re: Bacth* Performance issue

#4 2012-04-07 08:01:05

Re: Bacth* Performance issue

#5 2012-04-07 11:21:17

Re: Bacth* Performance issue

#6 2012-04-07 15:28:15

Re: Bacth* Performance issue

Board footer