mORMot Open Source

h.hasenack

Member

From: Nijmegen, Netherlands

Registered: 2012-08-01

Posts: 173

Website

Writing Blobs in BatchModes

Finally I managed to update our nexusDB driver so it can read and write blob fields.

But I discovered there's no support for writing blob fields in batchadd/batchupdate (checked this NG too). What are the chances of getting this? I do understand batches can become quite large using large blobs, but it would seriously enhance my performance if available.

Hans

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Writing Blobs in BatchModes

Finally I managed to update our nexusDB driver so it can read and write blob fields.

Perhaps you have some code to share?
What are the performance feedback?

BATCH format uses an array of JSON objects...
Adding BLOB content would expect the content to be Base64 encoded. It will be fast (SynCommons.pas has very optimized functions), but perhaps won't be not very optimized for the bandwidth.

Updating BLOB fields should already be available, if you retrieve the fields using a FillPrepare (since updated fields will follow the one retrieved with the FillPrepare).

But we may better add a generic way.
What about adding a FieldNames optional RawUTF8 parameter, with the field list as CSV?
Or directly a PSQLFieldBits parameter, with a list of field index bits?
Or as an open array of RawUTF8 field names?

h.hasenack

Member

From: Nijmegen, Netherlands

Registered: 2012-08-01

Posts: 173

Website

Re: Writing Blobs in BatchModes

Perhaps you have some code to share?

Yes, we intend to share the Nexus "driver". Only I fear it's not so ready for production/serious beta testing yet. e.g. batch writes are missing from what I understood from Bascy. I'll ask Bascy anyway to send the unit to you anyway so you can chew on it .

What are the performance feedback?

It's not bad at all, depending a bit on using the NexusDB embedded engine or the remote engine. I'm not sure wheter NexusDB allows batch update like Oracle: prepare one statement and next only send tons of data. I understand NexusDB will require a (prepared) query to be executed for each insert/update statement.

Anyway, Here's some performance output, NexusDB compared to Oracle in the same test.

NexusDB, Batch=False, Transaction=False
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 806 records added, avg speed 403 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 806 records fetched, avg speed 1,011 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 806 records modified, avg speed 580 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 806 records deleted, avg speed 2,575 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)

NexusDB, Batch=False, Transaction=True
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 891 records added, avg speed 446 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 891 records fetched, avg speed 1,018 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 891 records modified, avg speed 594 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 47 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 891 records deleted, avg speed 3,160 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)

NexusDB, Batch=True, Transaction=False
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 6,006 records added, avg speed 2,687 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 375 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 6,006 records fetched, avg speed 1,077 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 329 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 6,006 records modified, avg speed 2,301 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 297 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 6,006 records deleted, avg speed 3,231 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)

NexusDB, Batch=True, Transaction=True
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 8,008 records added, avg speed 3,635 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 485 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 8,008 records fetched, avg speed 1,077 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 391 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 8,008 records modified, avg speed 2,683 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 391 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 8,008 records deleted, avg speed 4,343 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)


Oracle, Batch=False, Transaction=False
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 1,003 records added, avg speed 502 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 16 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,003 records fetched, avg speed 558 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 15 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,003 records modified, avg speed 845 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 16 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,003 records deleted, avg speed 1,834 rps Batch=False Transaction=False Process SGTestrunner_D17.exe (2260)

Oracle, Batch=False, Transaction=True
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 1,267 records added, avg speed 634 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 16 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,267 records fetched, avg speed 513 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 0 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,267 records modified, avg speed 1,126 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 0 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 1,267 records deleted, avg speed 2,192 rps Batch=False Transaction=True Process SGTestrunner_D17.exe (2260)

Oracle, Batch=True, Transaction=False
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 17,017 records added, avg speed 8,509 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 266 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 17,017 records fetched, avg speed 1,129 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 250 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 17,017 records modified, avg speed 14,145 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 234 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 17,017 records deleted, avg speed 35,087 rps Batch=True Transaction=False Process SGTestrunner_D17.exe (2260)


Oracle, Batch=True, Transaction=True
Debug Output: ---CREATE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: 23,023 records added, avg speed 10,375 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---READ TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 391 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 23,023 records fetched, avg speed 1,121 rps  Process SGTestrunner_D17.exe (2260)
Debug Output: ---MODIFY TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 343 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 23,023 records modified, avg speed 15,349 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)
Debug Output: ---DELETE TEST--- Process SGTestrunner_D17.exe (2260)
Debug Output: Query took 328 ms Process SGTestrunner_D17.exe (2260)
Debug Output: 23,023 records deleted, avg speed 5,457 rps Batch=True Transaction=True Process SGTestrunner_D17.exe (2260)

BATCH format uses an array of JSON objects...
Adding BLOB content would expect the content to be Base64 encoded. It will be fast (SynCommons.pas has very optimized functions), but perhaps won't be not very optimized for the bandwidth.

Updating BLOB fields should already be available, if you retrieve the fields using a FillPrepare (since updated fields will follow the one retrieved with the FillPrepare).

I'll have to check this, Ididn't see a blob on/off in fillprepare yet.

But we may better add a generic way.
What about adding a FieldNames optional RawUTF8 parameter, with the field list as CSV?

I think something like this is already available isn't it?
Still I don't think fetching the blob's separately is a bad idea at all. I just feel the Fetch/Update/Delete methods are a bit oddly spread across the REST client and the TSQLRecord... Having a TSQLRecord.FillOne with a blob selection parameter as described earlier seems a nice solution to me. It might be hard to implement though, I read below it depends on the FillPrepare statement (which obviously prepares the fieldlist of the fetched fields).

Or directly a PSQLFieldBits parameter, with a list of field index bits?

This seems a bit "enforced artificial" to me. I like the CSV ot TStrings solution better in that case.

Or as an open array of RawUTF8 field names?

That could work, and requires no escape characters in any case. But wait, why not a TStrings type? That seems to very common practice to send over a list of strings between modules, controls etcetera.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Writing Blobs in BatchModes

i too try add images in batch mode, but blob field is null
how fast add many records with one image in blob?
i write simple sample with 1 record, but me need add about 2000-4000 record
https://www.dropbox.com/s/5j07635nwt387 … om_base.7z

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Writing Blobs in BatchModes

BLOBs are not transmitted by default, for performance reasons, and better compatibility with the REST protocol.

You can try to set TSQLRestClientURI.ForceBlobTransfert property to TRUE.

Or just use a TByteDynArray kind of field, which will be transmitted as BLOB.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Writing Blobs in BatchModes

in sample i trying set DataBase.ForceBlobTransfert := True; but nothing happend
how correct allow blob batch mode?

Updating BLOB fields should already be available, if you retrieve the fields using a FillPrepare (since updated fields will follow the one retrieved with the FillPrepare).

i try use this code  but it too not work

  if Database.TransactionBegin(TSQLProduct) then
  try
    Database.BatchStart(TSQLProduct);

    prd := TSQLProduct.Create;
    try
      prdtable := Database.ExecuteList([TSQLProduct], 'select * from product');
      prd.FillPrepare(prdtable);
      while prd.FillOne do
        with prd do begin
          pic := '';
          g := TPicture.Create;
          g.LoadFromFile(prd.pathtofile);
          SaveAsRawByteString(g, pic, gptJPG);
          picture := pic;
          Database.BatchUpdate(prd);
        end;
      finally
        FreeAndNil(prd);
      end;

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

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Writing Blobs in BatchModes

You are right: ForceBlobTransfert will only work with individual Update/Add, not with BatchUpdate/BatchAdd.
My mistake. Sorry.

First of all, I suspect you have a huge memory/resource leak in your code: g is not released, AFAI. Worth adding a "try..finally g.Free;".

Then your ExecuteList() is not the best way to do it, since it won't be ORM ready, but SQL oriented.

You should better execute:

prd := TSQLProduct.CreateAndFillPrepare(Database,'');

As stated in the doc, the update will only change the fields retrieved during a previous FillPrepare/FillOne, since you are using the same prd: TSQLProduct instance.

- aCustomFieldsCSV can be used to specify which fields must be retrieved
        (default is to retrieve all table fields, but you may need to access only
        one or several fields, and will save remote bandwidth by specifying the
        needed fields): notice that you should not use this optional parameter
        if you want to Update the retrieved record content later, since the
        missing fields will be left with previous values - but BatchUpdate() will
        set only ID, TModTime and mapped fields }

In your case, you should specified the needed fields in the :

prd := TSQLProduct.CreateAndFillPrepare(Database,'','pathtofile,picture');

I suppose it will do the trick.
But in this case, we will retrieve huge BLOB fields which will then be just changed... not very optimized at all.

We should be able to add the fields to the internal Batch mapping.
I'm not able to find an easy workaround to your issue.
I'll check how to add some way of doing it.

In the meanwhile, why not use a TByteDynArray property, and not a TSQLRawBlob published property?
AFAIK this dynamic array will be transmitted within the simple fields!
In this case, you may have just to write:

prd := TSQLProduct.CreateAndFillPrepare(Database,'pathtofile,otherfile,...'); // List here all SIMPLE fields (excluding BLOBs and arrays)

Then use a DEDICATED TSQLProduct instance (not the same prd instance) for tha BatchUpdate.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Writing Blobs in BatchModes

thanks for reply, i find good sample with TByteDynArray: http://synopse.info/forum/viewtopic.php?pid=3832#p3832

Last edited by noobies (2013-10-09 12:46:24)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Writing Blobs in BatchModes

I've just added a new optional CustomFields parameter to TSQLRestClientURI.BatchUpdate() and BatchAdd() methods.
See http://synopse.info/fossil/info/56f240ed05

It would be able to do directly what you expect to do.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Writing Blobs in BatchModes

I've just added a new optional CustomFields parameter to TSQLRestClientURI.BatchUpdate() and BatchAdd() methods.
See http://synopse.info/fossil/info/56f240ed05

It would be able to do directly what you expect to do.

great, but I do not understand how to use this opportunity

my current realization

  TSQLProduct = class(TSQLRecord)
  private
    fCategory: Integer;
    fPath, fName, fWeight: string;
    fPicture: TByteDynArray;
  published
    property category: Integer read fCategory write fCategory;
    property path:       string read fPath       write fPath;
    property name:    string read fArtikul    write fArtikul;
    property weight:     string read fWeight     write fWeight;
    property picture: TByteDynArray read fPicture write fPicture;
  end;

...

  if Database.TransactionBegin(TSQLProduct) then
  try
    Database.BatchStart(TSQLProduct);

    cat_id := 1;
    d := SetPathSep([TDirectory.GetCurrentDirectory, 'zl', 'valuables']);
    for directory in TDirectory.GetDirectories(d, TSearchOption.soTopDirectoryOnly, nil) do begin
      for f in TDirectory.GetFiles(Directory) do begin
        prd := TSQLProduct.Create;
        try
          with prd do begin
            category := cat_id;
            path := ExtractRelativePath(d, f);
            GetInfoFromFileName;
            name := sl[0];
            weight  := sl[1];
            ms := TMemoryStream.Create;
            try
              ms.LoadFromFile(f);
              SetLength(picBin, ms.Size);
              Move(ms.Memory^, picBin[0], ms.Size);
            finally
              ms.Free;
            end;
            picture := picBin;

            Database.BatchAdd(prd, True);
          end;
        finally
          FreeAndNil(prd);
        end;
      end;
      Inc(cat_id);
    end;
    Database.BatchSend(ids);
    Database.Commit;
  except
    Database.RollBack;
  end;

I deliberately removed checks for the existence of records, as each time the database will be clear.
after approv base will not be changing.

Last edited by noobies (2013-10-10 04:55:25)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Writing Blobs in BatchModes

I've added TSQLRecordProperties.FieldIndexsFromCSV()/FieldIndexsFromRawUTF8() methods (with functions ready to be used e.g. in BatchAdd/BatchUpdate).

So you can specify the fields at BatchUpdate.
See for instance in the new regression tests:

  ClientDist.BatchUpdate(V, TSQLRecordPeople.RecordProps.FieldIndexsFromCSV('YearOfBirth'))

This will work for memo fields also.