#1 2015-08-17 20:08:37

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)

Offline

#2 2015-08-18 06:50:09

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,048
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.

Offline

#3 2015-08-19 06:10:41

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

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

ab wrote:

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!

Offline

#4 2015-08-19 10:24:25

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,048
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.

Offline

#5 2015-08-19 11:35:39

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

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

ab wrote:

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! :-)

Offline

#6 2015-08-19 11:54:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,048
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!

Offline

#7 2015-08-19 12:06:38

cypriotcalm
Member
Registered: 2015-02-18
Posts: 122

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

ab wrote:

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!

Offline

Board footer

Powered by FluxBB