Devart Forums

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).