TUniQuery VERY slow

[kneighbour]

I am performing a lot of speed tests on my software, and one thing that seems to have popped up is that SELECT queries run using TUniQuery are a LOT slower than the same query run using other tools.

I use Firebird 3. Delphi XE7 and UniDac 8.0.1. I can run virtually any query, but here is one random example.

Code: Select all

SELECT
 LOCATIONITEMS.ID,
 LOCATIONITEMS.DRAW_FROM,
 LOCATIONS.DRAWSTOCKFROM
FROM
  LOCATIONITEMS
INNER JOIN LOCATIONS ON (LOCATIONITEMS.LOCATIONID = LOCATIONS.ID)
WHERE 
 (LOCATIONS.DRAWSTOCKFROM>0)
 AND ((LOCATIONITEMS.DRAW_FROM IS NULL) OR ((LOCATIONITEMS.DRAW_FROM <> LOCATIONS.DRAWSTOCKFROM)
 AND (LOCATIONITEMS.OVERRIDEGLOBALDRAWFROM<>1)) )

I can run this query in my code in this sort of format

Code: Select all

  qryDrawFrom.disablecontrols;
   qryDrawFrom.Close;
   qryDrawFrom.Connection := Master.MasterUNI;
   qryDrawFrom.SQL.text := 'that SQL text above';
   qryDrawFrom.readonly;
   qryDrawFrom.open;
   
   all other TUniQuery settings are default
  

In my code this takes 1.3 seconds. The tables involved are fairly big - the main table here is 956K records. The Firebird 3 database server is on the machine I am developing on (so it is local). This query actually returns 0 records.

The issue is I can run this exact same SQL on the same DB using any other 3rd party tool. ie Maestro, IBExpert, etc and the query takes around 600mS in all of those tools. DBeaver does it in 20mS! But at least half the time.

And it is not only this one query. It is virtually everything. Stored Procedures are also extremely slow. This was highlighted to me this morning by a customer who was comparing a Stored Procedure run locally on a MSSQL server, and the same Stored Procedure run using TUniStoredPro. It takes roughly double the time to run the Stored Procedure using TUniStoredProc than running it using another tool.

I am wondering if there is something I can do to speed up TUniQuery. Nothing is attached (ie no grids, etc), so all it should be loading is 25 rows. I won't mention TUniTable as that verges on unusable.

UPDATE : I decided to run this particular query using the IBCQuery and IBCConnection. Exactly the same query to the same database gives a "correct" response - it is about 700mS as all the other 3rd party tools show.

So UniQuery certainly has an issue (at least on my system).

[ertank]

Hello,

If it is not confidential, would you share your database for testing on my system?

Thanks & regards,
Ertan

[ViktorV]

I am performing a lot of speed tests on my software, and one thing that seems to have popped up is that SELECT queries run using TUniQuery are a LOT slower than the same query run using other tools.

I use Firebird 3. Delphi XE7 and UniDac 8.0.1. I can run virtually any query, but here is one random example.

Code: Select all

SELECT
 LOCATIONITEMS.ID,
 LOCATIONITEMS.DRAW_FROM,
 LOCATIONS.DRAWSTOCKFROM
FROM
  LOCATIONITEMS
INNER JOIN LOCATIONS ON (LOCATIONITEMS.LOCATIONID = LOCATIONS.ID)
WHERE 
 (LOCATIONS.DRAWSTOCKFROM>0)
 AND ((LOCATIONITEMS.DRAW_FROM IS NULL) OR ((LOCATIONITEMS.DRAW_FROM <> LOCATIONS.DRAWSTOCKFROM)
 AND (LOCATIONITEMS.OVERRIDEGLOBALDRAWFROM<>1)) )

I can run this query in my code in this sort of format

Code: Select all

  qryDrawFrom.disablecontrols;
   qryDrawFrom.Close;
   qryDrawFrom.Connection := Master.MasterUNI;
   qryDrawFrom.SQL.text := 'that SQL text above';
   qryDrawFrom.readonly;
   qryDrawFrom.open;
   
   all other TUniQuery settings are default
  

In my code this takes 1.3 seconds. The tables involved are fairly big - the main table here is 956K records. The Firebird 3 database server is on the machine I am developing on (so it is local). This query actually returns 0 records.

The issue is I can run this exact same SQL on the same DB using any other 3rd party tool. ie Maestro, IBExpert, etc and the query takes around 600mS in all of those tools. DBeaver does it in 20mS! But at least half the time.

And it is not only this one query. It is virtually everything. Stored Procedures are also extremely slow. This was highlighted to me this morning by a customer who was comparing a Stored Procedure run locally on a MSSQL server, and the same Stored Procedure run using TUniStoredPro. It takes roughly double the time to run the Stored Procedure using TUniStoredProc than running it using another tool.

I am wondering if there is something I can do to speed up TUniQuery. Nothing is attached (ie no grids, etc), so all it should be loading is 25 rows. I won't mention TUniTable as that verges on unusable.

UPDATE : I decided to run this particular query using the IBCQuery and IBCConnection. Exactly the same query to the same database gives a "correct" response - it is about 700mS as all the other 3rd party tools show.

So UniQuery certainly has an issue (at least on my system).

Unfortunately, we haven't been able to reproduce the issue.
Please compose and send send to us via form e-support: https://www.devart.com/company/contactform.html, a small sample demonstrating the described behavior and the difference in behavior between UniDAC and the IBDAC, along with scripts for creating and populating database objects.

[kneighbour]

I have found the "problem". It is "options.QueryRecCount:=true". This basically doubles the query time, at least in the few queries that I have been testing.

[ViktorV]

We are glad to know you have found the answer.
If the QueryRecCount is True - TCustomDADataSet performs additional query to get the record count for this SELECT.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.