Hi list,

The Firebird/Interbase ibconnection.pp code has this code in its
SetParameters procedure to upload blobs in segments (BlobSegmentSize
property is set to 80 on object creation):
      while BlobBytesWritten < (BlobSize-BlobSegmentSize) do
        begin
        isc_put_segment(@FStatus[0], @blobHandle, BlobSegmentSize,
@s[(i*BlobSegmentSize)+1]);
        inc(BlobBytesWritten,BlobSegmentSize);
        inc(i);
        end;
      if BlobBytesWritten  BlobSize then
        isc_put_segment(@FStatus[0], @blobHandle,
BlobSize-BlobBytesWritten, @s[(i*BlobSegmentSize)+1]);

I've asked the Firebird list whether we need to upload in segments and the answer is no, you can upload in chunks of up to 65535 bytes (see below).

Would getting rid of the BlobSegmentSize property and replacing it by a BlobSegmentSize const set at 65535 make sense (e.g. for performance)?

Thanks,
Reinier

http://tech.groups.yahoo.com/group/firebird-support/message/115826 Posted by: "Ann Harrison"
Fri Nov 18, 2011 9:10 am (PST)

Reinier Olislagers wrote

I came across BLOB segment size and understand that, when writing BLOBs, you need to write in chunks smaller than or equal to the segment size.

Your understanding is wrong. The segment size was a suggestion for some higher-level tools that wanted a hint as to the size chunks of blob that would be convenient to handle. To the best of my knowledge, nothing uses it now. Even when it was used, it did not limit the size segment that could be passed in and out.

That interface was designed in 1982 - think about the increase in available RAM since then.

Presumably having a larger segment size might improve performance for reading/writing large BLOBs, but might lead to bigger storage requirements per BLOB?

Pass the largest chunks that are convenient to handle, remembering that at various places there are 16 bit integers that describe the length of things. Passing larger chunks has no effect on the storage size.

Did a small performance test:
- once with 80, once with 65535
- 10MB blobs
- Firebird 2.5 server
- on a virtual machine (just restored from snapshot before each run) The difference seems fairly big: 395 seconds versus 260 seconds

Hope I'm testing correctly.

Test code is attached; I'd appreciate confirmation.

Also attached is a patch for ibconnection.pp against FPC fixes_2_6. I set the BlobSegmentSize property to deprecated and basically ignored the underlying variable when writing blobs.

I wonder whether/what changes are required for reading blobs...

Thanks,
Reinier

Well, if Ann Harrison herself says it's irrelevant, then yes. I suspect it would indeed give a serious performance boost :)

Michael.

I applied the patch, thank you. revision 19659.

I wonder whether/what changes are required for reading blobs...

The following function needs adapting:

function TIBConnection.getMaxBlobSize(blobHandle : TIsc_Blob_Handle) : longInt;
var
   iscInfoBlobMaxSegment : byte = isc_info_blob_max_segment;
   blobInfo : array[0..50] of byte;
begin
   if isc_blob_info(@Fstatus[0], @blobHandle, sizeof(iscInfoBlobMaxSegment), pchar(@iscInfoBlobMaxSegment), sizeof(blobInfo)
     CheckError('isc_blob_info', FStatus);
   if blobInfo[0]  = isc_info_blob_max_segment then
     begin
       result :=  isc_vax_integer(pchar(@blobInfo[3]), isc_vax_integer(pchar(@blobInfo[1]), 2));
     end
   else
      CheckError('isc_blob_info', FStatus);
end;

You should first check what it returns. I suspect the declared blob segment size.

Michael.

getMaxBlobSize does indeed return the segment size used when writing (i.e. 80 by default on pre 2.7.1, 65535 with my patch).

Changed ibconnection.pp:
- remove getMaxBlobSize function
- use MAXBLOBSEGMENTSIZE (65535) when reading

Tested it with existing blobs written in 80 byte and 65535 byte
segments. Works; the returned value matches what was written in the
first place:
  while FQuery.EOF=false do
  begin
    if FQuery.FieldByName('BLOBCOL').AsString  RecordText then writeln
('Error reading record');;
    FQuery.Next;
  end;

Patch attached; it applies against 2.7.1.
Please let me know if you want me to open a bug for this...

Thanks,
Reinier

Hi Graeme & list,

Had a look at tiOPF FBLib FBLDsql.pas. It defines
BLOB_SEGMENT_LEN = 4095;
used in both reading & writing blob data.

... might/could/should that be enlarged to 65535?

Regards,
Reinier

Michael Van Canneyt wrote

Well, if Ann Harrison herself says it's irrelevant, then yes. I suspect it would indeed give a serious performance boost :)

Showing my ignorance by not knowing who Ann Harrison was, I Google'd her. I found this post on the Firebird website. A bit of history about Firebird (or rather Interbase). It was very entertaining to read. A pity one can't start software companies like that any more. :-) It sure does sound like they had fun though.

http://www.firebirdsql.org/en/ann-harrison-s-reminiscences-on-interbase-s-beginnings/

Based on the new information, it should be changed. I'll update FBlib. Thanks for noticing this.

Is there a directly link to Ann's answer to you in the Firebird mailing list, which I can reference in the commit message? Otherwise I'll simply reference your reply with Ann's quote from the FPC-Pascal mailing list.

http://tech.groups.yahoo.com/group/firebird-support/message/115826 Thanks,
Reinier

Uploaded patch to
http://bugs.freepascal.org/view.php?id=20719

Thanks,
Reinier

Reinier Olislagers wrote

... might/could/should that be enlarged to 65535?

The change has been made in the latest FBLib code.

Regards,
- Graeme -

<Discusses BLOB segmentsize for reading; patch at:>

http://bugs.freepascal.org/view.php?id=20719

Ping, nudge, hint ;)

If anybody needs more info regarding the patch, please let me know...

As Michael Van Canneyt implemented the blob writing segment patch (revision 19659) so quickly, I suspect the reading part shouldn't be too much work either - and people might forget the context re this patch, making it more work to implement.

Then again, I actually don't use Firebird blobs right now, so personally I don't really mind when this patch gets in ;)

Regards,
Reinier

Database patches by default get assigned to Joost, and Joost is busy. I'll have a can always directly assign bugs to me if we've discussed them here on the list.

Michael.

Thanks a lot Michael, I understand.

Only trouble is, I don't think I have permissions in Mantis to actually assign bugs to people...

Regards,
Reinier

When entering a new bug, I think you can assign it at once ? (or maybe that field just appears because I'm an admin, could be..)

In that case, you can always "ping" me :-)

Anyway, I applied the patch, thank you very much.

Michael.

Well, since I'm feeling quite optimistic and go-getting this morning: Michael, if you have the time, could you look at these patches: 19937 TSQLExporter does not quote memo fields (or blobs) 19759 [Patch] CSV export doesn't quote memo fields

If one actually uses TSQLExporter to transfer stuff to databases, I'm sure the patch will be welcome...

Thanks,
Reinier