Slower than IBexpress with Devexpress Grid ?

[Fabrice]

Hello,

You are right completely, that was the problem !
I haven't change my TIBCQuery.server property which was always set to 192.168.1.100 (my local ip) for all my tests. Now in my "bench program" in my code before change TibCquery.database I set TibCquery.server:='';

That change all the result :
IBDac is the fastest in 7 on 13 of my queries test (but only 1-2% quicker than UIB and FIBPLUs).
In average it is 17 % quick than IBX.
In average IBDAC use 24 % less of memory than IBX.
But UIB, FIB and IBDAC are very closed in speed and memory used.

Correct new results :
UIB
192.168.1.10:D:\prog\travail\Mybase.FDB
Time(1) 11,6570002399385 s
Memory(1) 148983808 Mb
Time(2) 8,32199959550053 s
Memory(2) 89194496 Mb
Time(3) 1,79699978325516 s
Memory(3) 43290624 Mb
Time(4) 1,06799989007413 s
Memory(4) 38363136 Mb
Time(5) 2,57400032132864 s
Memory(5) 53243904 Mb
Time(6) 1,21599999256432 s
Memory(6) 55943168 Mb
Time(7) 0,0719997100532055 s
Memory(7) 35721216 Mb
Time(8 ) 0,0419996213167906 s
Memory(8 ) 35823616 Mb
Time(9) 9,4070004997775 s
Memory(9) 146227200 Mb
Time(10) 4,97299970593303 s
Memory(10) 118603776 Mb
Time(11) 1,55100056435913 s
Memory(11) 44896256 Mb
Time(12) 40,1820001658052 s
Memory(12) 636477440 Mb
Time(13) 89,6729998290539 s
Memory(13) 781205504 Mb

IBDAC
server=192.168.1.10 | 192.168.1.10:D:\prog\travail\Mybase.FDB\INFOCOB2009.FDB
Time(1) 11,3989996025339 s
Memory(1) 128823296 Mb
Time(2) 8,69600053410977 s
Memory(2) 82116608 Mb
Time(3) 1,78699996322393 s
Memory(3) 49696768 Mb
Time(4) 0,952999759465456 s
Memory(4) 42622976 Mb
Time(5) 2,2850000532344 s
Memory(5) 43470848 Mb
Time(6) 1,32700006943196 s
Memory(6) 54927360 Mb
Time(7) 0,0659999437630177 s
Memory(7) 37867520 Mb
Time(8 ) 0,0389997381716967 s
Memory(8 ) 37875712 Mb
Time(9) 9,34200009796768 s
Memory(9) 123928576 Mb
Time(10) 5,36799982655793 s
Memory(10) 129036288 Mb
Time(11) 1,5480000525713 s
Memory(11) 39301120 Mb
Time(12) 41,6459997184575 s
Memory(12) 437071872 Mb
Time(13) 91,1229995079339 s
Memory(13) 714772480 Mb

IBX
192.168.1.10:D:\prog\travail\Mybase.FDB
Time(1) 16,189999692142 s
Memory(1) 209076224 Mb
Time(2) 11,6919999243692 s
Memory(2) 130551808 Mb
Time(3) 2,02999992761761 s
Memory(3) 52338688 Mb
Time(4) 1,01699999067932 s
Memory(4) 41553920 Mb
Time(5) 2,71000026259571 s
Memory(5) 71307264 Mb
Time(6) 1,19799943640828 s
Memory(6) 54476800 Mb
Time(7) 0,0710001680999994 s
Memory(7) 38522880 Mb
Time(8 ) 0,0390003668144345 s
Memory(8 ) 38182912 Mb
Time(9) 10,4269997449592 s
Memory(9) 264617984 Mb
Time(10) 5,4670003708452 s
Memory(10) 176234496 Mb
Time(11) 1,80499989073724 s
Memory(11) 55500800 Mb
Time(12) 101,718000252731 s
Memory(12) 843812864 Mb


FIB
192.168.1.10:D:\prog\travail\Mybase.FDB
Time(1) 11,7839999031276 s
Memory(1) 123371520 Mb
Time(2) 9,09399990923703 s
Memory(2) 76468224 Mb
Time(3) 1,85699996072799 s
Memory(3) 44077056 Mb
Time(4) 0,959000154398382 s
Memory(4) 42917888 Mb
Time(5) 2,52500013448298 s
Memory(5) 41803776 Mb
Time(6) 1,16999968886375 s
Memory(6) 54820864 Mb
Time(7) 0,0679996563121676 s
Memory(7) 44335104 Mb
Time(8 ) 0,0380001962184906 s
Memory(8 ) 44335104 Mb
Time(9) 9,56799967680126 s
Memory(9) 102219776 Mb
Time(10) 4,97200016397983 s
Memory(10) 132935680 Mb
Time(11) 1,57900031190366 s
Memory(11) 52887552 Mb
Time(12) 39,8139996221289 s
Memory(12) 384315392 Mb
Time(13) 92,971000447869

Same kind of result using local database. But of course it is fastest.

So FIBplus, UIB and Ibdac are equal for speed and memory use.
Advantage to IBdac :
- Support is quick and efficient. Very important for me. You have helped me even if I'am not a customers.
- Migration from IBX seems to be very easy because it use same kind of component

These will be important in the next months to leave IBX. Just try with a full Unicode (UTF8) database.

Once again thank you.

Best regards,
-
Fabrice

[Fabrice]

Strange with my database converted to UTF8 :
I must set IBCConnection1.Options.UseUnicode:= true; If I let it to false I have a problem with accent like é à ç or euro symbole (€)
But when I set UseUnicode:=True Ibdac queries become 10-15% slower than UIB and FIBplus.

Any suggestions ?

[Dimon]

The point is that when the UseUnicode option is set to True IBDAC creates TWideStingField instead of TStringField and convert all char symbols to widechar on opening table. This takes much time.
To solve the problem you should set UseUnicode to False. Also as a workaround you can create the OnGetText and OnSetText event handlers for string fields and encode/decode values to UTF8 format. This functionality will be the same as in FIBPlus.

Code: Select all

procedure TForm1.IBCQuery1TXT1GetText(Sender: TField; var Text: String; DisplayText: Boolean);
begin
  Text := Utf8Decode(Sender.AsString);
end;

procedure TForm1.IBCQuery1TXT1SetText(Sender: TField; const Text: String);
begin
  Sender.AsString := Utf8Encode(Text);
end;

[Fabrice]

Hello,

1) I don't understand why I must do :

Code: Select all

Text := Utf8Decode(Sender.AsString); 

My Delphi is Unicode (Delphi 2010). So string=unicodestring ?
My database is UTF8.

Why to force Utf8Decode ?

2)
To do this I must use persistent fields ! That say I need to do a lot of changes. And not very easy for dynamic queries :

Code: Select all

procedure TForm1.IBCQuery1TXT1GetText(Sender: TField; var Text: String; DisplayText: Boolean); 

3) Same query on an UTF8 database / Delphi 2010. All handle correctly UTF8 charset (no problem with é à and € so useunicode:=true for Ibdac) :
FiBplus : 5,6 s / 151 mb
Fibdac : 8.13 s / 141 mb
UIB : 4,43 s / 229 mb
IBX : 8,2 s / 365 mb

FibDac take less memory but is as slow as IBX !
If Fibplus do translation to widestring it's seems to be more efficient.



Must I test version 3.50 ? Currently I use 3.10...

[Dimon]

1. The point is that InterBase server sends values of string fields as a set of bytes in the UTF8 encoding. To show them as a Unicode string you should convert them to the appropriate format. For this you can use the Utf8Decode procedure or set the UseUnicode option to True.

2. You should not use persistent fields. You can assign event handlers in runtime in the AfterOpen event handler of TIBCQuery, like this:

Code: Select all

procedure TForm1.IBCQueryAfterOpen(DataSet: TDataSet);
var
  i: Integer;
begin
  for i := 0 to DataSet.FieldCount - 1 do
    if DataSet.Fields[i] is TStringField then begin
      DataSet.Fields[i].OnGetText := OnGetText;
      DataSet.Fields[i].OnSetText := OnSetText;
    end;
end;

procedure TForm1.OnGetText(Sender: TField; var Text: String;
  DisplayText: Boolean);
begin
  Text := Utf8Decode(Sender.AsString);
end;

procedure TForm1.OnSetText(Sender: TField; const Text: String);
begin
  Sender.AsString := Utf8Encode(Text);
end;

3. When the UseUnicode option is set to True IBDAC creates TWideStingField instead of TStringField and converts all char symbols to widechar on opening table, and this requires time.

[Fabrice]

Hello,

Thanks again to the Devart Team for his efficient Support because I'am not (yet) a customer.

I have tested your code :

Code: Select all

procedure TForm1.IBCQueryAfterOpen(DataSet: TDataSet);
...

And the speed is like Fibplus :
IBDAC :
Time : 4,93299979716539 s
Memory: 157 937 664 Mb
(6,8 s with useunicode = 38 % slower)
But is not faster in all kind of queries (with a lot of fields ?)...

FIBPLUS :
Time : 4,90600022021681 s
Memory: 151 142 400 Mb


So I have two questions :

1) Why there is no Property options.UseUTF8Decode in TIBCConnection because, in some cases, it is more efficient than useUnicode ?

2) How to handle this kind of event in FastReport or other third party components which create TibcQuery dynamically ?

Best regards,
-
Fabrice

[Dimon]

1. This functionality is very specific and it is not necessary for most of users. In your case you need it only because you are using Devexpress Grid. In most cases it is better to set UseUnicode to True.

2. You can add this functionality in the frxIBDACComponents unit without any problems.