very slow with D2009 and remote firebird 2.1 DB

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for InterBase & Firebird in Delphi and C++Builder
Post Reply
vz
Posts: 3
Joined: Sun 09 Oct 2011 12:57

very slow with D2009 and remote firebird 2.1 DB

Post by vz » Sun 09 Oct 2011 13:23

Hi I just tested your last dbexpress driver (3.0) with D2009 and remote Firebird 2.1. I created few TSQLConnection and TSQLQuery objects with standard Firebird driver (from D2009), with Upscene and with Devart.

And execute something like this:

SQLQuery1.Open;
I := 0;
while not SQLQuery1.Eof do begin
cxMemo2.Lines.Append(IntToStr(I)
+ ' ' + SQLQuery1.FieldByName('ID').AsString
+ ' ' + SQLQuery1.FieldByName('PAVADINIMAS').AsString
);
SQLQuery1.Next;
I := I + 1;
end;

And results in seconds was:

(DevartInterBase): Open: 5.221 Last: 33.981 Total: 39.202
(InterXpress for Firebird): Open: 0.227 Last: 0.634 Total: 0.861
(InterBase): Open: 0.036 Last: 0.661 Total: 0.697

Table has only 265 rows. Very long takes not only retrieve but open operation also. Wrong my devart driver settings or what?

My test project: http://e-servisas.lt/x/9.zip
Remote FB server is on slow network ~2MBits.

p.s. As i can see on my test project output - devart driver reads records in chunks of 25 and it takes few seconds. Why takes so long?

AndreyZ

Post by AndreyZ » Tue 11 Oct 2011 08:50

Please try setting the OptimizedNumerics connection option to False and check if the problem persists. If the problem persists, please send a script to create and fill the firmos table to andreyz*devart*com.

vz
Posts: 3
Joined: Sun 09 Oct 2011 12:57

Post by vz » Tue 11 Oct 2011 09:48

Nothing changed. So I send DDL sript to andreyz.

AndreyZ

Post by AndreyZ » Wed 12 Oct 2011 12:14

I cannot reproduce such problem. Please try setting the UseUnicode connection parameter to False. Here is an example:

Code: Select all

SQLConnection.Params.Values['UseUnicode'] := 'False';

vz
Posts: 3
Joined: Sun 09 Oct 2011 12:57

Post by vz » Wed 12 Oct 2011 12:55

If you give me your ip address I can open connection to test database for you. My first test was through ssh tunnel. With this direct connection the time for your driver is much better but still not good as others:

DevartInterBase: Open: 0.657 Last: 3.999 Total: 4.656
InterXpress for Firebird: Open: 0.104 Last: 0.356 Total: 0.460

This is very strange why ssh tunnel influence in such big scale only to your driver.

AndreyZ

Post by AndreyZ » Fri 14 Oct 2011 09:52

We have reproduced the problem and the investigation of the problem is in progress. We will notify you when we have any results.

AndreyZ

Post by AndreyZ » Mon 24 Oct 2011 14:37

We have investigated this question. This problem was caused by reading all BLOB data at the dataset opening. We have added the DeferredBlobRead and DeferredArrayRead connection options which help to avoid performance problem. DeferredBlobRead and DeferredArrayRead are used for fetching all BLOB and array values correspondingly when they are explicitly requested. To solve performance problem, you will have to set both DeferredBlobRead and DeferredArrayRead to True. These options will be available in the next dbExpress driver for InterBase & Firebird build.

jerduval
Posts: 23
Joined: Mon 13 Sep 2010 08:33
Location: Colombes, France

Post by jerduval » Wed 02 Nov 2011 10:02

Can you tell us when the next version will be available ?

Regards,
Jerome DUVAL

AndreyZ

Post by AndreyZ » Wed 02 Nov 2011 12:23

As we are working on MacOS support now, we cannot provide the exact time when the new version of dbExpress driver for InterBase & Firebird will be available. We plan to release all dbExpress drivers approximately in a month.

jerduval
Posts: 23
Joined: Mon 13 Sep 2010 08:33
Location: Colombes, France

Post by jerduval » Tue 31 Jan 2012 09:24

Hello,
is the 3.1.2 version contains this correction ?
I've not found it in history.

Regards,

Jerome DUVAJ
Holy-Dis

AndreyZ

Post by AndreyZ » Wed 01 Feb 2012 09:32

We added the DeferredBlobRead and DeferredArrayRead connection options in dbExpress driver for InterBase & Firebird version 3.1.2. For more information, please read the Readme.html file.

Post Reply