[patch_done] Very slowly post data (BLOB)

Forum related to SQLite

Moderators: gto, cipto_kh, EgonHugeist

_TwoZZ_
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 18.06.2010, 11:14

[patch_done] Very slowly post data (BLOB)

Postby _TwoZZ_ » 22.06.2010, 07:37

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

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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 29.08.2010, 21:10

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
Image

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 30.08.2010, 08:52

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 01.09.2010, 23:17

klchin,

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

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 02.09.2010, 03:24

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 02.09.2010, 06:42

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
Image

guidoaerts
Senior Boarder
Senior Boarder
Posts: 93
Joined: 01.07.2009, 16:07

Postby guidoaerts » 03.09.2010, 10:40

would this work?

for I := 0 to SrcLength -1 do
begin
shx := IntToHex( ord(SrcBuffer[i]),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
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 04.09.2010, 05:15

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 );

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 04.09.2010, 21:38

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
Image

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 06.09.2010, 03:55

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 08.09.2010, 21:38

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
Image

klchin
Senior Boarder
Senior Boarder
Posts: 65
Joined: 02.09.2005, 06:27

Postby klchin » 09.09.2010, 04:38

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

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 19.09.2010, 21:04

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
Image


Return to “SQLite”

Who is online

Users browsing this forum: No registered users and 1 guest