TUniQuery VERY slow

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
kneighbour
Posts: 70
Joined: Wed 08 Oct 2008 04:55

TUniQuery VERY slow

Post by kneighbour » Wed 04 Sep 2019 02:14

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
Posts: 168
Joined: Wed 13 Jan 2016 16:00

Re: TUniQuery VERY slow

Post by ertank » Wed 04 Sep 2019 06:01

Hello,

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

Thanks & regards,
Ertan

ViktorV
Devart Team
Posts: 2838
Joined: Wed 30 Jul 2014 07:16

Re: TUniQuery VERY slow

Post by ViktorV » Thu 05 Sep 2019 14:08

kneighbour wrote:
Wed 04 Sep 2019 02:14
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
Posts: 70
Joined: Wed 08 Oct 2008 04:55

Re: TUniQuery VERY slow

Post by kneighbour » Thu 05 Sep 2019 21:46

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
Devart Team
Posts: 2838
Joined: Wed 30 Jul 2014 07:16

Re: TUniQuery VERY slow

Post by ViktorV » Mon 09 Sep 2019 12:43

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.

Post Reply