mORMot Open Source

noobies

Member

Registered: 2011-09-13

Posts: 139

big problem with Batch

today i compile my code with latest version and Butch not work (after butch program run, i see fields have data isnerting and work fine, close program, but data dont save in base, db3 20kb (except 1,2mb if compile with version 1440))
i have some backup version framework, 1622 not work too, but 1440 work fine
Now more info:
i try add to base diagnoses from csv file in batch mode

InitClient(not TFile.Exists(path_app + DBFile));

function CreateDataModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLDataRecord, TSQLDiagnose, TSQLOptions]);
end;

procedure InitClient(LoadData: Boolean = False);
begin
  Model := CreateDataModel;
  Rest := TSQLRestClientDB.Create(Model, CreateDataModel, ChangeFileExt(paramstr(0), '.db3'), TSQLRestServerDB);
  TSQLRestClientDB(Rest).Server.CreateMissingTables(0);

  if LoadData then TSQLDiagnose.CreateDiagnoses(Rest);
end;

class procedure TSQLDiagnose.CreateDiagnoses(const ARest: TSQLRest);
var
  i: integer;
  diagnose: TSQLDiagnose;
  sl, slES: TStringlist;
  ids: TIDDynArray;
begin
  sl := TStringList.Create;
  sl.LoadFromFile('MKB10.csv');
  slES := TStringList.Create;

  if Rest.TransactionBegin(TSQLDiagnose) then
  try
    Rest.BatchStart(TSQLDiagnose);

    for i := 0 to sl.Count - 1 do begin
      slES.Clear;
      ExtractStrings([';'], [' '], PChar(sl.Strings[i]), slES);
      diagnose := TSQLDiagnose.Create(Rest, 'Code = ?', [slES.Strings[0]]);
      try
        if diagnose.ID = 0 then begin
          diagnose.Code := slES.Strings[0];
          diagnose.Desc := slES.Strings[1];
          Rest.BatchAdd(diagnose, True);
        end
      finally
        FreeAndNil(diagnose);
      end;
    end;

    Rest.BatchSend(ids);
    Rest.Commit;
  except
    Rest.RollBack;
  end;
end;

this code work fine in version 1440 and create base 1.2mb, but in new version created 20kb blank base.

Last edited by noobies (2015-08-19 07:58:31)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: big problem with Batch

Do not use manual transaction, but use the AutomaticTransactionPerRow parameter of BatchStart.

I've included a similar test case to http://synopse.info/fossil/info/8b0f3d5dfd
And no problem on our side.

Which version of Delphi are you using?
What is within the ids[] result array?
Did you debug the code and see what's wrong on your side?

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

Delphi XE8U1
intresting test, i download latest version and run test with 100 count
records prepared but not inserting - program FREEZE

Prepared 100 rows in 312us
FREEZE

if comment test and uncomment test2

Prepared 100 rows in 339us
Inserted 100 rows in 10.93ms i.e. 9144 per second

in ids[] after BatchSend i see

(1, 2, 3, 4, 5,...

after change mormot to old 1440 and run u sample (15 - External DB performance) test work fine (and test2 too)

Prepared 100 rows in 390us
Inserted 100 rows in 9.37ms i.e. 10663 per second //test
Prepared 100 rows in 374us
Inserted 100 rows in 11.32ms i.e. 8832 per second //test2

i put 2 exe compiled 15 - External DB performance sample with 1440 and latest version framework to u see problem
https://www.dropbox.com/sh/vy1hm8mr550w … 8zlGa?dl=0

Last edited by noobies (2015-08-19 14:38:02)

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

in my code in first post i delete

  if Rest.TransactionBegin(TSQLDiagnose) then

and all work fine,
but intresting why not work test in 15 - External DB performance, but in old 1440 version test work fine

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

in latest mormot

procedure TSQLRest.Commit(SessionID: cardinal; RaiseException: boolean);
begin
  if self<>nil then begin
    fAcquireExecution[execORMWrite].Safe.Lock; //FREEZE here
    try
      if (fTransactionActiveSession<>0) and
         (fTransactionActiveSession=SessionID) then begin
        fTransactionActiveSession := 0; // by default, just release flag
        fTransactionTable := nil;
      end;
    finally
      fAcquireExecution[execORMWrite].Safe.UnLock;
    end;
  end;
end;

i comment

//  fAcquireExecution[execORMWrite].Safe.Lock;
...
//  fAcquireExecution[execORMWrite].Safe.UnLock;

and commit work fine

in 1440 mormot this procedure have another syntax

procedure TSQLRest.Commit(SessionID: cardinal; RaiseException: boolean);
begin
  if self<>nil then begin
    fAcquireExecution[execORMWrite].Enter;
    try
      if (fTransactionActiveSession<>0) and
         (fTransactionActiveSession=SessionID) then begin
        fTransactionActiveSession := 0; // by default, just release flag
        fTransactionTable := nil;
      end;
    finally
      fAcquireExecution[execORMWrite].Leave;
    end;
  end;
end;

Last edited by noobies (2015-08-19 14:38:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: big problem with Batch

Both Safe.Lock and Enter methods are perfectly equals.
So I guess the issue is not from there.

I do not understand why their may be a race condition in a Batch, since all is run in the very same thread.

I run the sample 15 regression tests.
No problem either.
See http://jpst.it/B4ee

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: big problem with Batch

.... and sample 15 compiled with XE6.
http://jpst.it/B4eq

No problem either..

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

OK, thanks i solve problem delete if Rest.TransactionBegin(TSQLDiagnose) (in post #5) then but u new create "test" and this test get error in my pc i tomorrow try compile sample in xe7 and some more tests.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

in xe7 test work fine in latest version. but in xe8u1 dont.

Last edited by noobies (2015-08-20 06:51:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: big problem with Batch

I tried with XE8 update 1.
No problem here.
http://jpst.it/B54W

What is your exact issue?

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: big problem with Batch

thx, i install home xe8u1, and compile sample 15 all work fine. problem in work pc. Next time before post question i test in home and work.