[patch_done] Very slowly post data (BLOB)

[_TwoZZ_]

Hi,
I'm having trouble on D2010 + ZEOSLIB_TESTING_REV800 + SQLITE-3.6.23.1.:

Very slowly post data in BLOB field (I write down images - 2 minutes write 1000Kb ):

Code: Select all

	zq.SQL.Text := 'UPDATE pages SET name='''+LabeledEdit1.Text+''',';
	ZQuery1.SQL.Add(pic = :pic ');
	ZQuery1.SQL.Add(' WHERE id = ''' + PageID'''');
	Stream := TFileStream.Create(FileName,fmOpenRead);
	ZQuery1.ParamByName('model').LoadFromStream(Stream, ftGraphic);
	ZQuery1.ExecSQL;
	Stream.free;

Has found out, that the reason of it is encoding of data ("ZDbcSqLiteUtils.pas"):

Code: Select all

function NewEncodeString(Value: ansistring): ansistring;
var
  I: Integer;
  SrcLength, DestLength: Integer;
  SrcBuffer, DestBuffer: PAnsiChar;
  IH : integer;
begin
  SrcLength := Length(Value);
  SrcBuffer := PAnsiChar(Value);
  DestLength := 2+ 2*SrcLength;  // Hex-value double

  SrcBuffer := PAnsiChar(Value);
  result := '';
  for I := 1 to SrcLength do
  begin
    IH := ord(SrcBuffer^);
    result := result + IntToHex(IH,2);
    Inc(SrcBuffer,1);
  end;
  result := 'x'+QuotedStr(result);
end;

There is a decision of the given problem?

Thx.

Evgeny.

[mdaems]

Hi,

Do you know a better way to encode a string into a hexadecimal representation ofd the BLOB?
You could try to replace the addition of small strings at the end of 'result' by more efficient string operations on a temporary string instead of using the special 'result' variable.

Maybe you should contact 'fst001' on this forum, as it was he who did sent the patch that changed ths behaviour.

Mark

[klchin]

Hi,

I think the slow was due to

result := result + IntToHex(IH,2); <-- slow process

Why not try to convert to a small string and do a mem copy ie.

SetLength( Result,Length(SrcData) * 2 );
nchr := 1;
...
tmp := IntToHex(IH,2);
Result[nchr] := tmp[1];
Result[nchr + 1] := tmp[2];
nchr := nchr + 2;
...

Regards,
KL Chin

[mdaems]

klchin,

Would you care to write real code I can commit to the SVN repository?

[klchin]

Hi Mark,

Here the code, I had not tested. Pls. veify it.

function NewEncodeString( Value : ansistring ) : ansistring;
var
I : Integer;
SrcLength : Integer; // ,DestLength
SrcBuffer : PAnsiChar; // , DestBuffer
ihx : integer; // IH,
shx : ansistring;
begin

SrcLength := Length(Value);
// SrcBuffer := PAnsiChar(Value);
// DestLength := 2+ 2*SrcLength; // Hex-value double

SrcBuffer := PAnsiChar(Value);
// result := '';
SetLength( Result,1 + SrcLength * 2 );
Result[1] := 'x';
ihx := 2;
for I := 1 to SrcLength do
begin
// IH := ord(SrcBuffer^);
// result := result + IntToHex(IH,2);
shx := IntToHex( ord(SrcBuffer^),2 );
result[ihx] := shx[1]; Inc( ihx,1 );
result[ihx] := shx[2]; Inc( ihx,1 );
Inc(SrcBuffer,1);
end;
// result := 'x'+QuotedStr(result); // since result is Hex string
end;

Regards,
KL Chin

[mdaems]

klchin,

At first look I'm missing the quotes in the resultstring. Also 'Inc(SrcBuffer,1) seems dangerous, certainly with the new compilers.
Didn't try compiling yet. Going to work now.

Mark

[guidoaerts]

would this work?

for I := 0 to SrcLength -1 do
begin
shx := IntToHex( ord(SrcBuffer),2 );
result[1+i*2] := shx[1];
result[1+i*2] := shx[2];
end;
// result := 'x'+QuotedStr(result); // since result is Hex string
end;

[klchin]

Hi Mark,

Unless the PAsniChar was not "1 BYTE", otherwise should be ok, i think.
or use SrcBuffer : PChar or PByte instead;


Hi guidoaerts,

because i started from 0

result[1] := 'x';


result[2+i*2] := shx[1];
result[3+i*2] := shx[2];

Btw, not using I*2 for performance, unless compiler full optimized for speed.

maybe code below will be faster

pdst : PChar;

pdst := @Result[2];

pdst^ := shx[1]; Inc( pdst,1 );
pdst^ := shx[2]; Inc( pdst,1 );

[mdaems]

Please, did anyone of you test his correction?

It seems to me this code can't work as long as this line is commented???

Code: Select all

// result := 'x'+QuotedStr(result); // since result is Hex string 

Mark

[klchin]

Hi Mark,

I think I make a mistake, the code should be

result[ihx] := shx[2]; Inc( ihx,1 ); // Hi Byte
result[ihx] := shx[1]; Inc( ihx,1 ); // Lo Byte

BTW, what is QuotedStr do? Convert ABC to 'ABC' or none?

Regards,
KL Chin

[mdaems]

See http://delphi.about.com/library/rtl/blrtlQuotedStr.htm

As you are converting the BLOB into the hexadecimal representation, it's probably best when you just add the quotes without using QuotedStr.
I think the final string should be x'A26C....'

Mark

[klchin]

Then the code will be

SetLength( Result,3 + SrcLength * 2 );
Result[1] := 'x'; // set x
Result[2] := ''''; // set Open Quote
ihx := 3; // set 1st hex location
for I := 1 to SrcLength do
begin
shx := IntToHex( ord(SrcBuffer^),2 ); // '3E'
result[ihx] := shx[1]; Inc( ihx,1 ); // copy '3'
result[ihx] := shx[2]; Inc( ihx,1 ); // copy 'E'
Inc( SrcBuffer,1 ); // next byte source location
end;
result[ihx] := ''''; // set Close Quote


KL Chin

[mdaems]

Thanks klchin,

I did test and commit the change a few days ago.
SVN Rev. 813.
A very quick speed test showed your code was about 3 times faster than the original version in the case I tested on Lazarus.

Mark