On 19-11-2011 13:28, Reinier Olislagers wrote:
> 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)?
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
program firebirdsegment;
{ Tests blob writing performance on Firebird/Interbase databases;
try varying the BlobSegmentSize below }
{
CREATE TABLE TESTTABLE
(
BLOBCOL BLOB SUB_TYPE 1,
ID INTEGER NOT NULL,
CONSTRAINT PK_TESTTABLE_0 PRIMARY KEY (ID)
);
}
{$mode objfpc}{$H+}
{$APPTYPE CONSOLE}
uses {$IFDEF UNIX} {$IFDEF UseCThreads}
cthreads, {$ENDIF} {$ENDIF}
Classes,
SysUtils,
sqldb,
ibconnection {Firebird access},
dateutils;
var
FConnection: TIBConnection;
FQuery: TSQLQuery;
FTransaction: TSQLTransaction;
RecordCounter: integer;
TextCounter: integer;
Recordtext: string='';
Currenttime: Tdatetime;
Endtime: Tdatetime;
begin
FConnection:=TIBConnection.Create(nil);
FConnection.CharSet := 'UTF-8';
FConnection.DatabaseName := 'test.fdb';
FConnection.HostName := paramstr(1); //first command line argument
FConnection.UserName := 'SYSDBA';
FConnection.Password := 'masterkey';
FConnection.Dialect := 3;
//FConnection.BlobSegmentSize:=80;
FConnection.BlobSegmentSize:=65535;
FConnection.Connected:=true;
FTransaction := TSQLTransaction.Create(nil);
FConnection.Transaction := FTransaction;
FQuery := TSQLQuery.Create(nil);
FQuery.Database := FConnection;
FQuery.Close;
FQuery.SQL.Text := 'SELECT ID, BLOBCOL FROM TESTTABLE;';
//10 meg
for TextCounter := 1 to 10485760 do
begin
RecordText := RecordText + '1';
end;
writeln('BlobSegmentSize = ' + IntToStr(FConnection.BlobSegmentSize));
CurrentTime:=now();
for RecordCounter := 0 to 500 do
begin
if (RecordCounter mod 10)=0 then
begin
// Free up space
writeln('Cleanup at record ' + inttostr(recordcounter));
FTransaction.Active:=True;
FConnection.ExecuteDirect('DELETE FROM TESTTABLE');
FTransaction.Commit;
FTransaction.Active := True;
end;
Ftransaction.Active:=true;
FQuery.Open;
FQuery.Insert;
FQuery.FieldByName('ID').AsInteger:=RecordCounter;
FQuery.FieldByName('BLOBCOL').Clear;
FQuery.FieldByName('BLOBCOL').AsString := RecordText;
FQuery.Post;
FQuery.ApplyUpdates;
FTransaction.Commit;
end;
EndTime:=now();
writeln('Time spent: '+ IntToStr(SecondsBetween(CurrentTime, EndTime)) + '
seconds.');
FQuery.Close;
FTransaction.Active:=false;
FConnection.Close;
FQuery.Free;
FTransaction.Free;
FConnection.Free;
end.Index: packages/fcl-db/src/sqldb/interbase/ibconnection.pp
===================================================================
--- packages/fcl-db/src/sqldb/interbase/ibconnection.pp (revision 19658)
+++ packages/fcl-db/src/sqldb/interbase/ibconnection.pp (working copy)
@@ -16,6 +16,7 @@
const
DEFDIALECT = 3;
+ MAXBLOBSEGMENTSIZE = 65535; //Maximum number of bytes that fit in a blob
segment.
type
@@ -51,7 +52,7 @@
FStatus : array [0..19] of ISC_STATUS;
FDialect : integer;
FDBDialect : integer;
- FBLobSegmentSize : word;
+ FBLobSegmentSize : word; //required for backward compatibilty; not used
procedure ConnectFB;
function GetDialect: integer;
@@ -99,7 +100,8 @@
constructor Create(AOwner : TComponent); override;
procedure CreateDB; override;
procedure DropDB; override;
- property BlobSegmentSize : word read FBlobSegmentSize write
FBlobSegmentSize;
+ //Segment size is not used in the code; property kept for backward
compatibility
+ property BlobSegmentSize : word read FBlobSegmentSize write
FBlobSegmentSize; deprecated;
function GetDBDialect: integer;
published
property DatabaseName;
@@ -165,7 +167,7 @@
inherited;
FConnOptions := FConnOptions + [sqSupportParams] + [sqEscapeRepeat];
FieldNameQuoteChars:=DoubleQuotes;
- FBLobSegmentSize := 80;
+ FBLobSegmentSize := 65535; //Shows we're using the maximum segment size
FDialect := -1;
FDBDialect := -1;
end;
@@ -743,7 +745,7 @@
BlobBytesWritten : longint;
procedure SetBlobParam;
-
+
begin
{$R-}
with cursor as TIBCursor do
@@ -759,14 +761,16 @@
BlobBytesWritten := 0;
i := 0;
- while BlobBytesWritten < (BlobSize-BlobSegmentSize) do
+ // Write in segments of MAXBLOBSEGMENTSIZE, as that is the fastest.
+ // We ignore BlobSegmentSize property.
+ while BlobBytesWritten < (BlobSize-MAXBLOBSEGMENTSIZE) do
begin
- isc_put_segment(@FStatus[0], @blobHandle, BlobSegmentSize,
@s[(i*BlobSegmentSize)+1]);
- inc(BlobBytesWritten,BlobSegmentSize);
+ isc_put_segment(@FStatus[0], @blobHandle, MAXBLOBSEGMENTSIZE,
@s[(i*MAXBLOBSEGMENTSIZE)+1]);
+ inc(BlobBytesWritten,MAXBLOBSEGMENTSIZE);
inc(i);
end;
if BlobBytesWritten <> BlobSize then
- isc_put_segment(@FStatus[0], @blobHandle, BlobSize-BlobBytesWritten,
@s[(i*BlobSegmentSize)+1]);
+ isc_put_segment(@FStatus[0], @blobHandle, BlobSize-BlobBytesWritten,
@s[(i*MAXBLOBSEGMENTSIZE)+1]);
if isc_close_blob(@FStatus[0], @blobHandle) <> 0 then
CheckError('TIBConnection.CreateBlobStream isc_close_blob', FStatus);
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
