Problem with Blob fields

[zd]

I'm using Delphi7 with IBDAC. I have a query like this:

SELECT XY, YZ, List(XZ, ', ') AS Something FROM ExampleTable GROUP BY XY, YZ;

There are about 2.000 rows in ExampleTable. I'm testing this query on a high speed VPN connection.

FlameRobin needs about 5 secs to fetch all rows. IBDAC takes about 2 minutes.

If I set DeferredBlobRead to true and do not get the value of the field 'Something' then IBDAC takes just a few seconds too. If I ask for the value of 'Something' then IBDAC becomes super slow.

For some reason it seems that IBDAC is treating 'Something' as a BLOB field - which it is not.

How can I get around this problem?

Thank you!

[ZEuS]

This problem is not an IBDAC error, this is Firebird behaviour.
As the official Firebird documentation says, the result of the LIST() function is a text BLOB ( http://www.firebirdsql.org/refdocs/lang ... -list.html ).
So, in order to avoid the problem, you should either set DeferredBlobRead to True or use the Data Type Mapping feature and map the "Something" field to the String type. See http://www.devart.com/ibdac/docs/data_type_mapping.htm for more information about Data Type Mapping.

[zd]

ZEuS, this doesn't help.

I've tried the following:
DBQuery.DataTypeMap.AddFieldNameRule('Something', ftWideString, false);

The result:
EDataMappingError 'String value is too long: ...'

The string in question is 71 characters long (DB encoding = UTF8)
If I set IgnoreErrors to true in AddFieldNameRule, then the string is truncated to 20 characters and execution speed is similarly slow!

I've managed to find one workaround, but I can't use this in my program in all places. If I call:

SELECT XY, YZ, CAST(LEFT(List(XZ, ', '), 5000) AS VARCHAR(5000) AS Something FROM ExampleTable GROUP BY XY, YZ;

then even without using Data Type Mapping all rows will be fetched in 3 seconds.

Note that if I only call CAST without LEFT or LEFT without CAST the speed is going to be similarly slow like before.

I'm pretty sure that either some setting is wrong, or this is an error in IBDAC.

The reason I'm sure again is that FlameRobin on the same connection will take only 4 seconds to load the entire dataset without using any workaround (CAST/LEFT). And FlameRobin will load the BLOB fields of LIST without problems, within those 4 seconds.

What's your suggestion?

[ZEuS]

To avoid the "String value is too long: ..." error, you should specify the field length when setting the data type mapping rule, for example:

Code: Select all

DBQuery.DataTypeMap.AddFieldNameRule('Something', ftWideString, 5000, false);

[zd]

The speed problem is still there.

The "String value is too long:" error is gone, but even with datatype mapping IBDAC loads the resultset in 2 minutes.

Even if I specify 100 instead of 5000 in AddFieldNameRule, it's the same so it's not related to the length.

Either one setting is wrong or IBDAC doesn't handle text blobs the way it should.

What can we do?

[zd]

Are you investigating this problem?

[ZEuS]

We are investigating the problem. We will inform you when we have any results.

[zd]

ZEuS, here is a step-by-step guide to reproduce the problem.

1. Use:

• Delphi 7
  Latest version of IBDAC
  And set up a VPN connection with the latest version of the free OpenVPN software (http://openvpn.net/index.php/open-source/downloads.html)

2. Drop a TIBCConnection and TIBCQuery.
The following settings are different from default:

Code: Select all

TIBCConnection:
Charset = UTF8
EnabledMemos = true
UseUnicode = true

TIBCQuery:
DefferedBlobRead = true

In the initialization section of the main form set:
  UnicodeMemoParameters := true;
  ThreadSafetyClientLibrary := true;

3. Have a Firebird DB with charset UTF8 and two tables:

Code: Select all

Table A
AID AS INTEGER
testID AS INTEGER

3.000 sample records with multiple instaces of the same AID with different testIDs like:
AID = 3, testID = 5
AID = 3, testID = 7
etc.

Table B
testID AS INTEGER
testName AS VARCHAR(1000)

1.500 sample records
one testName for each testID

4. We'll call the query this way:

Code: Select all

DBQuery.SQL.Text := 'SELECT tableA.AID, List(tableB.testName, '', '') AS SampleName FROM A tableA LEFT JOIN B tableB ON tableB.testID = tableA.testID GROUP BY tableA.AID';
DBQuery.Open;

while not DBQuery.Eof do
begin
  Data.SampleName := DBQuery.FieldByName('SampleName').AsString;
  DBQuery.Next;
end;

For me, this will take about 2 minutes to execute through the VPN. With FlameRobin, same query, same results, same VPN - only 4 seconds.

If I add:
DBQuery.FieldDefs.Add('SampleName', 500, ftWideString);
before the DBQuery.Open line, the speed is the same 4 mins.

Let me know if you find something please!

[zd]

ZEuS, have you managed to reproduce this problem?

I'm losing face in front of my clients and they are constantly bugging me for a fix in my program - but I'm entirely dependent on you with this.

Please let me know as soon as you have any news on when this may be sorted out.

Thank you!

[ZEuS]

We investigated the issue you have reported and found no errors in IBDAC, that could lead to such performance loss.
Possibly, the problem is specific to the VPN usage. But, unfortunately, we can not reproduce the error in our test environment. If you have such a possibility,
you can provide the remote access to the VPN you are using. If so, please send an email to eugeniyz*devart*com, in which specify the VPN connection parameters, database connection parameters and the VPN client you are using.

[zd]

I've sent you a complete sample package with VPN details.

Please let me know if you've got it.

Thank you!

[ZEuS]

We have tested the sample project with the VPN settings you have provided. The problem with performance loss is not due to IBDAC and is fully related to the VPN speed.
Comparison to FlameRobin that you have reported is not applicable in this case, because the test project and FlameRobin do quite different things.
The following code from the test project

Code: Select all

  while not IBCQuery1.Eof do
  begin
    Str := IBCQuery1.FieldByName('Listed').AsString;
    IBCQuery1.Next;
  end;

fetches the complete recordset from the server, whereas FlameRobin does not fetch all rows. When executing a query, it retrieves the first 100 rows and fetches the other rows as needed when navigating through the result grid. You can easily check this if you set the cursor in the grid and press the "Down" key at the keyboard. The time when the cursor reaches the end of the recordset will be very long.
So, you can try to adjust the TIBCQuery.Options.DeferredBlobRead and TIBCQuery.FetchRows properties to minimize client-server traffic. In addition, take a look at the SmartFetch option that we have implemented in the latest IBDAC version ( http://www.devart.com/ibdac/docs/devart ... tfetch.htm ). It can be useful when navigating through large recordsets.

[zd]

ZEus,

FlameRobin has an option to "Fetch all rows". If you navigate down with the cursor keys it may seem slow.

But either set in preferences to Fetch all rows by default or press the Fetch all rows button under Grid menu / Fetch all rows and you'll see that it's blazingly fast.

It loads whole resultset under 4 seconds.
With IBDAC it's 2 minutes!!!

The VPN is blazingly fast, the problem is in IBDAC.

Please redo the test and tell me what you find...

[zd]

And apart from the above, one more thing:
As I stated earlier, if you execute:
"SELECT XY, YZ, CAST(LEFT(List(XZ, ', '), 5000) AS VARCHAR(5000) AS Something FROM ExampleTable GROUP BY XY, YZ;"

IBDAC speed will be the same as FlameRobin speed! Just by using "CAST(LEFT ...)" So I'm sure that the problem is in the way that IBDAC handles text BLOB fields! Please look into that part...

[ZEuS]

The "Fetch all rows" option in FlameRobin does not lead to real reading of all data from the server. Particularly, setting this option for the SQL query used in the sample project does not fetch all "Listed" BLOB fields. FlameRobin fetches BLOBs as needed only when scrolling down the result grid, i.e. it does actually the same that TIBCQuery does with DeferredBlobRead = True. You can ensure this using any network traffic sniffer.
Using "CAST(LEFT ...)" returns the "Listed" field as VARCHAR and does not require sending an additional request to the server to retrieve the BLOB data. So, in this case, fetching of all data is faster.