mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

How to save efficiently a bunch of blobs into a MySQL table?

Good evening! :-)

I have a bunch of pictures stored as files and I want to save all of them into the MySQL table.

I have a code which works good but slow for a big amount of data. How could the following code be improved in respect of speed? It would be great if somebody could help me!

I am using FireDAC.

var
  Pic: TSynPicture;
  PicRawBlob: TSQLRawBlob;
  RecID: TID;
begin

  for I := 0 to aFiles.Count-1 do
  begin
    ...
    Pic.LoadFromFile(aFiles[i]);
    SaveAsRawByteString(Pic, PicRawBlob, gptJPG);
    RecID := fClient.Add(TSQLTable, True);
    fClient.UpdateBlob(TSQLTable, RecID, 'BlobField', PicRawBlob);
    ...
  end;

end;

Last edited by cypriotcalm (2015-08-18 05:04:00)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to save efficiently a bunch of blobs into a MySQL table?

1. Do not use a Pic: TSynPicture variable, but directly the file content from disk into memory using StringFromFile().

2.A Concatenate blobs in a single big binary, send it in one step to the server, then write a dedicated method-based service to de-concatenate those blobs on the server side, and update the blobs within a transaction (and perhaps direct SQL access) at once.

2.B As an alternative, try to use a Batch process, with explicit sending of the blob content with the other fields: blobs would be sent as Base64, but in one step, so it may be worth it.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: How to save efficiently a bunch of blobs into a MySQL table?

1. Do not use a Pic: TSynPicture variable, but directly the file content from disk into memory using StringFromFile().

2.A Concatenate blobs in a single big binary, send it in one step to the server, then write a dedicated method-based service to de-concatenate those blobs on the server side, and update the blobs within a transaction (and perhaps direct SQL access) at once.

2.B As an alternative, try to use a Batch process, with explicit sending of the blob content with the other fields: blobs would be sent as Base64, but in one step, so it may be worth it.

Hi ab,

thank you for your help!

I have tried to implement 2B, it works, but the blobs are not transmitted. And the documentation says:

"- by default, TSQLRawBlob properties are serialized as null, unless woSQLRawBlobAsBase64 is defined"

I don't understand where I should set this option!?

The 2B implementation looks like:

  TSQLTable= class(TSQLRecord)
  private
    fFirstname: RawUTF8;
    fLastname: RawUTF8;
    fPicture: TSQLRawBlob;
   ...
  end;
   
  ...

  fClient.ForceBlobTransfert := True;
  fClient.ForceBlobTransfertTable[TSQLTable] := True; 

  fClient.BatchStart(TSQLTable);

  for I := 0 to aFiles.Count-1 do
  begin
    ...
    DataTable.Firstname:= 'John';
    DataTable.Lastname := 'Dow';
    DataTable.Picture := StringFromFile(aFiles[i]);

    fClient.BatchAdd(DataTable, True);
    ...
  end;

  fClient.BatchSend(ResultIDs);

I have tried also both TSQLRawBlob and RawByteString, but the same result the blobs are not sent.

I have also tried this variant:

  ...
    DataTable.Firstname:= 'John';
    DataTable.Lastname := 'Dow';

    RecID := fClient.BatchAdd(DataTable, True);
    fClient.UpdateBlob(TSQLTable, RecID, 'Picture', StringFromFile(aFiles[i]));
  ...

Nothing works! :-(

What am I doing wrong!?

Thank you for your help!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to save efficiently a bunch of blobs into a MySQL table?

UpdateBlob won't be part of the batch!

You have to specify the field names in BatchAdd(), including the blob field.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: How to save efficiently a bunch of blobs into a MySQL table?

UpdateBlob won't be part of the batch!

You have to specify the field names in BatchAdd(), including the blob field.

Thank you, ab! That's it! It works now like a charm! *feeling happy* :-D

The only one question, is this information how to work with batches in the documentation!? Unfortunately, I was not able to find something in the doc. :-(

But a big thank you for your help!!! I reduced the import time of my test data from about 6 minutes down to less than 1 minute! :-)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to save efficiently a bunch of blobs into a MySQL table?

I've just refined documentation about BATCH process and BLOB fields.
See http://synopse.info/fossil/info/4324d186e7

Happy it works well for you!

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: How to save efficiently a bunch of blobs into a MySQL table?

I've just refined documentation about BATCH process and BLOB fields.
See http://synopse.info/fossil/info/4324d186e7

Happy it works well for you!

Great! Thank you!