Slower than IBexpress with Devexpress Grid ?

Fabrice · Post by **Fabrice** » Tue 07 Sep 2010 10:32

Hello,

I use Delphi 2010 and I'am looking for an alternative of IBExpress which have a lot of problem with Firebird 2.1.

IBdac seems to be interesting so I'am comparing it with IBX, UIB and FIBplus.
I use a lot of Devexpress Grid (GridMode=false). And my test show me that IBexpress is faster than IBDac ?!

My test is on a table with 126 fields (1 blob) and 42000 records.
It's take 8,42 seconds for IBX and 12,98 seconds for IBdac. 5,7 s for UIB and FIBplus.
It's take 526 780 kb of memory for IBX and 302 276 kb for IBdac. 164 000 kb for Fibplus.

Why IBDac is the slowest ? I have tried DeferredBlobRead:=true without change.
With a TcrDBGrid TIBcquery seems to be faster...
Any tips between Devexpress Grid and IBdac components ?

What I use :
procedure TForm2.ButtonIdacClick(Sender: TObject);
var
TimeBefore:Tdatetime;
begin
TimeBefore:=now;

IBCConnection1.connected:=CheckBoxIbdac.checked;

IBCQuery1.active:=false;

IBCQuery1.SQL.clear;
IBCQuery1.SQL.addstrings(Memo1.Lines);

IBCQuery1.active:= IBCConnection1.connected;

cxGridIBdacDBTableView1.ClearItems;
cxGridIBdacDBTableView1.DataController.CreateAllItems;

if UIBDataSet1.active then
begin
ButtonIdac.caption:= 'Close '+ floattostr(now-TimeBefore);
end
else ButtonIdac.caption:='Open '+ floattostr(now-TimeBefore);
end;

Best regards,

Dimon · Post by **Dimon** » Tue 07 Sep 2010 13:18

Please check that the TIBCQuery.FetchAll property is set to False. Also try to set the TIBCQuery.Options.CacheBlobs property to False, and IBDAC will not allocate local memory buffer to hold a copy of the BLOB content. Also check that you are using the same client library for all tested components.

Fabrice · Post by **Fabrice** » Tue 07 Sep 2010 14:15

Hello Dimon,

That is sure it is that the Devart support is not the slowest !
I try you suggestion without changes.

I use this :
object IBCConnection1: TIBCConnection
Database = 'D:\Mybase.FDB'
Options.Charset = 'WIN1252'
DefaultTransaction = IBCTransaction1
SQLDialect = 1
Username = 'sysdba'
Password = 'masterkey'
Server = 'localhost'
AutoCommit = False
LoginPrompt = False
Left = 408
Top = 664
end
object IBCTransaction1: TIBCTransaction
DefaultConnection = IBCConnection1
Left = 488
Top = 664
end
object DataSourceIBDAC: TDataSource
DataSet = IBCQuery1
Left = 632
Top = 664
end
object IBCQuery1: TIBCQuery
Connection = IBCConnection1
ReadOnly = True
Options.CacheBlobs = False
Left = 560
Top = 664
end

AndreyZ · Post by **AndreyZ** » Wed 08 Sep 2010 08:28

Hello,

I have performed standart tests for comparing IBDAC with the IBX and FIBPlus components. With or without using the TCRDBGRID and TcxGrid components, IBDAC is faster than the other components. Therefore I suggest you to investigate the DevExpress grid options and try to configure them. Or you can send me a complete small sample to andreyz*devart*com to demonstrate the problem, including a script to create and fill a table.

Fabrice · Post by **Fabrice** » Wed 08 Sep 2010 12:13

Hello,

Thank you to help me.
Sample source + exe + database sent by email

Best regards,

AndreyZ · Post by **AndreyZ** » Wed 08 Sep 2010 12:33

We have received your sample and the investigation of the problem is in progress. As soon as we solve the problem we will let you khow.

AndreyZ · Post by **AndreyZ** » Wed 15 Sep 2010 09:14

To do the equivalent tests you should set the following properties:

Code: Select all

  TIBCConnection.Options.Charset := '';
  TIBCQuery.FetchRows := 25;
  TIBCQuery.FetchAll := True;
  TIBCQuery.Options.DeferredBlobRead := True;
  TIBCQuery.Options.CacheArrays := True;
  TIBCQuery.Options.CacheBlobs := True;

After this on testing of dataset without datasource IBDAC takes the lead in most cases.
On connecting Devexpress Grid IBDAC performance is approximately equal to FIBplus, and IBExpress appreciably loses.

Fabrice · Post by **Fabrice** » Wed 15 Sep 2010 10:44

AndreyZ wrote:To do the equivalent tests you should set the following properties:
...

Hello,

Thank you for answer.

I have tested, in the sample I have sent to you, with :

Code: Select all

procedure TForm2.ButtonIdacClick(Sender: TObject);
var
  Avant:Tdatetime;
begin
     Avant:=now;
     if IBCConnection1.connected then
     begin
            IBCQuery1.active:=false;
     end
     else   IBCConnection1.Options.Charset:='';
     IBCConnection1.connected:=not IBCConnection1.connected;

     IBCQuery1.FetchRows := 25;
     IBCQuery1.FetchAll := True;
     IBCQuery1.Options.DeferredBlobRead := True;
     IBCQuery1.Options.CacheArrays := True;
     IBCQuery1.Options.CacheBlobs := True;

     IBCQuery1.active:=false;
     IBCQuery1.SQL.clear;
     IBCQuery1.SQL.addstrings(Memo1.Lines);

     IBCQuery1.active:= IBCConnection1.connected;

     cxGridIBdacDBTableView1.ClearItems;
     cxGridIBdacDBTableView1.DataController.CreateAllItems;

     if UIBDataSet1.active then
     begin
       ButtonIdac.caption:= 'Close '+    floattostr(RetourneTpsSeconde(now-avant));
     end
     else  ButtonIdac.caption:='Open '+    floattostr(RetourneTpsSeconde(now-avant));
end;

All other property of TibcQuery have been set to default.

But my result are :

Fibplus : 1.76 secondes / 45,5 Mb of memory
IbExpress : 4.06 secondes / 70 Mb of memory
UIB : 1.36 secondes / 38,7 Mb of memory
IBDac : 9.00 secondes / 43,7 Mb of memory

I use Delphi 2010 does the problem come from Unicode ?
I have tested on Windows 7 64 bits / Windows 7 32 bits / Windows 2003 and different query and I have the same kind of results.

Dimon · Post by **Dimon** » Thu 16 Sep 2010 07:28

Please try to test performance without executing CreateAllItems. For this delete the following lines from your code:

Code: Select all

  cxGridIBdacDBTableView1.ClearItems; 
  cxGridIBdacDBTableView1.DataController.CreateAllItems;

And execute the FetchAll method after opening the query for all components, like this:

Code: Select all

  IBQuery1.Active:= IBConnection1.Connected; 
  IBQuery1.FetchAll;

Fabrice · Post by **Fabrice** » Thu 16 Sep 2010 09:58

Dimon wrote:Please try to test performance without executing CreateAllItems. For this delete the following lines from your code:
...

Hello,

Once again thank to the Devart Team for quick answers.

With this I have :
- Fibplus : 0.53 s
- Ibexpress : 0.64 s
- UIB : 1.11 s
- IBdac : 0.52 s

But of course my Dexpress Tcxgrid are empty. So does it exist a problem between Tcxgrid and IBdac ?

If I use TCRDBGrid in the place of Tcxgrid with all optionsEX set to true I have :
- Fibplus : 9.37 s
- IbExpress : 9.50 s
- UIB : 9.28 s
- IBdac : 8.65 s

IBdac is the faster but with an TCRBGrid ?

That's a problem for me. Because I need to switch from Ibexpress to another Data Access Library but I use only TcxGrid components !!!

Best regards,

-
Fabrice

Dimon · Post by **Dimon** » Fri 17 Sep 2010 14:33

We are investigating the problem. As soon as we solve this question we will let you know.

Fabrice · Post by **Fabrice** » Fri 17 Sep 2010 16:07

Dimon wrote:We are investigating the problem. As soon as we solve this question we will let you know.

Thank you very much.

I need to leave IBX from another Data access library. IBX are slow, take a lot of memory and have a lot of bug with Firebird 2.X and Delphi 2010 (certainly with Interbase too).
I can choose IBDAC, FIBPlus or UIB. But it must work fine and efficiently with Devexpress library and FastReport.

For the moment :
- IBDAC is the more easy for me to migrate. Support seems to be quick and efficient
- UIB for speed and open source
- FIBplus is the more efficient in speed and memory use (20 % less than UIB)

So I wait your investigations.

-
Fabrice

Dimon · Post by **Dimon** » Wed 22 Sep 2010 07:51

We have studied the performance of these products, and if TIBCQuery.Options.CacheArrays and TIBCQuery.Options.CacheBlobs are set to True, IBDAC performance is approximately equal to FIBplus when connecting with DevExpress Grid.
What are your test results when IDAC is set according to our last recommendation?
Also try setting the FetchRows property to 25, 50, 100, 200, and check the result.

Fabrice · Post by **Fabrice** » Thu 23 Sep 2010 09:25

Hello,

I have tried to change options but it is not better.
IBDAC can be quicker but only with small number of records.

I have tested on 13 queries used in my software (Actually with IBX).
Firebird data base server is swicthed off between each bench because of it cache.
And the result are :

IBDAC
Time(1) 0,279999990016222 s
Memory(1) 18616320 Mb
Time(2) 2,04900002572685 s
Memory(2) 27205632 Mb
Time(3) 1,42300010193139 s
Memory(3) 27910144 Mb
Time(4) 0,418999814428389 s
Memory(4) 25489408 Mb
Time(5) 0,207000109367073 s
Memory(5) 25489408 Mb
Time(6) 0,621999870054424 s
Memory(6) 31748096 Mb
Time(7) 0,0129997031763196 s
Memory(7) 26505216 Mb
Time(8 ) 0,0300000887364149 s
Memory(8 ) 26505216 Mb
Time(9) 0,149999815039337 s
Memory(9) 26505216 Mb
Time(10) 0,410000164993107 s
Memory(10) 26529792 Mb
Time(11) 1,11000014003366 s
Memory(11) 28123136 Mb
Time(12) 2,43199998512864 s
Memory(12) 28549120 Mb
Time(13) 16,0249998327345 s
Memory(13) 109559808 Mb

FIB
Time(1) 0,0969995744526386 s
Memory(1) 18055168 Mb
Time(2) 0,77199968509376 s
Memory(2) 25628672 Mb
Time(3) 0,749000161886215 s
Memory(3) 25968640 Mb
Time(4) 0,434000487439334 s
Memory(4) 25968640 Mb
Time(5) 0,211999705061316 s
Memory(5) 25968640 Mb
Time(6) 0,557999638840556 s
Memory(6) 31744000 Mb
Time(7) 0,0769999343901873 s
Memory(7) 29122560 Mb
Time(8 ) 0,0319998012855649 s
Memory(8 ) 29122560 Mb
Time(9) 0,155000039376318 s
Memory(9) 29175808 Mb
Time(10) 0,426000379957259 s
Memory(10) 29200384 Mb
Time(11) 0,360999978147447 s
Memory(11) 29970432 Mb
Time(12) 0,89200004003942 s
Memory(12) 30060544 Mb
Time(13) 4,27999966777861 s
Memory(13) 101326848 Mb

IBX
Time(1) 0,0649997731670737 s
Memory(1) 17825792 Mb
Time(2) 1,0819997638464 s
Memory(2) 33447936 Mb
Time(3) 0,77900025062263 s
Memory(3) 33230848 Mb
Time(4) 0,442999508231878 s
Memory(4) 23494656 Mb
Time(5) 0,229000090621412 s
Memory(5) 23494656 Mb
Time(6) 0,599999888800085 s
Memory(6) 31367168 Mb
Time(7) 0,0179999275133014 s
Memory(7) 25210880 Mb
Time(8 ) 0,030999630689621 s
Memory(8 ) 25538560 Mb
Time(9) 0,1600002637133 s
Memory(9) 25694208 Mb
Time(10) 0,432000146247447 s
Memory(10) 25309184 Mb
Time(11) 0,494000036269426 s
Memory(11) 35225600 Mb
Time(12) 1,30699980072677 s
Memory(12) 43487232 Mb
Time(13) 8,85799988172948 s
Memory(13) 180568064 Mb

UIB
Time(1) 0,0600001774728298 s
Memory(1) 17633280 Mb
Time(2) 0,683000218123198 s
Memory(2) 26525696 Mb
Time(3) 0,623999582603574 s
Memory(3) 26996736 Mb
Time(4) 0,404999940656126 s
Memory(4) 22499328 Mb
Time(5) 0,200000172480941 s
Memory(5) 22499328 Mb
Time(6) 0,541999423876405 s
Memory(6) 31543296 Mb
Time(7) 0,0179999275133014 s
Memory(7) 22233088 Mb
Time(8 ) 0,027000205591321 s
Memory(8 ) 22249472 Mb
Time(9) 0,157999922521412 s
Memory(9) 22474752 Mb
Time(10) 0,389000354334712 s
Memory(10) 22327296 Mb
Time(11) 0,352999870665371 s
Memory(11) 27021312 Mb
Time(12) 0,850999960675836 s
Memory(12) 32268288 Mb
Time(13) 3,81900023203343 s
Memory(13) 114552832 Mb

I will send you full source of my benchmark project.

Best regards,

Dimon · Post by **Dimon** » Thu 23 Sep 2010 16:17

It seems, that in your test IBDAC connects to database via TCP protocol and others components using local engine. Please set all components to connect via TCP protocol. In order to do this, set database name to 'localhost:' and database file path to something like:

Code: Select all

  pFIBDatabase1.DatabaseName := 'localhost:D:\prog\travail\infocob 2000\data\INFOCOB2009.FDB';

For IBDAC those settings are set by the Server property.
To set IBDAC connection to use local engine clear the TIBCConnection.Server property.