Very slow to retrieve rows using FireBird

Hi,
I have the followin relation ships
DATDECRESP->DATDECOP->DATDEC->DATDCECL 

Now I want to retrieve a set of records using Aureluis with the following code(Also have tested using SubCriteria) with same results:

AManager.Find<TXDATDECL>
                       .CreateAlias('IDDATDEC', 'D')
                       .CreateAlias('D.IDDATDECOP', 'O')
                       .CreateAlias('O.Respuesta', 'R')
                       .Add(Linq['D.ID'] = Linq['IDDATDEC'])
                       .Add(Linq['O.ID'] = Linq['D.IDDATDECOP'])
                       .Add(Linq['R.IDDATDECOP'] = Linq['O.ID'])
                       .Add(Linq['R.IDDECLARACIONREGISTRADA'] = ARegistro)
                       .List;        
It generates the following SQL:

SELECT A.ID AS A_ID, ... FROM DATDECL A
  LEFT JOIN DATDEC B ON (B.ID = A.IDDATDEC)
  LEFT JOIN DATDECOP C ON (C.ID = B.IDDATDECOP)
  LEFT JOIN DATDECRESP D ON (D.IDDATDECOP = C.ID)
WHERE  B.ID = A.IDDATDEC AND
C.ID = B.IDDATDECOP AND
D.IDDATDECOP = C.ID AND
D.IDDECLARACIONREGISTRADA = :p_0
The query takes 2 minutes to complete, checking the firebird plan shows the following: 
PLAN JOIN (JOIN (JOIN (A NATURAL, B INDEX (PK_DATDEC)), C INDEX (PK_DATDECOP)), D INDEX (DATDECOP_DATDECRESP))
I will search for records on DATDECL in storage order making it ineficcient.

Now I have modified the above query with classic sql with no joins to the following:
SELECT A.ID AS A_ID, ... FROM DATDECL A, DATDEC B, DATDECOP C, DATDECRESP D
WHERE  B.ID = A.IDDATDEC AND
C.ID = B.IDDATDECOP AND
D.IDDATDECOP = C.ID AND
D.IDDECLARACIONREGISTRADA = :p_0
the query take less than one second to complete and firebird now uses the following plan:
PLAN JOIN (D INDEX (U_DATDECRESP_1), C INDEX (PK_DATDECOP), B INDEX (DATDECOP_DATDEC), A INDEX (DATDEC_DATDECL)). using index on all tables.

how I can make Aurelius genetares more simple and efficent queryes like the above or should I have to change the Auelius search to diffetent aproach.

Thanks in advance,

Omar Zelaya

The thing here is that Aliases are used to join tables, and those joins are made using LEFT JOIN. That's how it goes to associated tables and query by fields on them.

Do you have created indexes in all fields involved in the conditions and joins?

Hi,
I have tested using Subcriteria with same results, same query is generated:
SELECT A.ID AS A_ID, ... FROM DATDECL A
  LEFT JOIN DATDEC B ON (B.ID = A.IDDATDEC)
  LEFT JOIN DATDECOP C ON (C.ID = B.IDDATDECOP)
  LEFT JOIN DATDECRESP D ON (D.IDDATDECOP = C.ID)
WHERE  D.IDDECLARACIONREGISTRADA = :p_0

And yes all related fields are indexed, thats why the modfied query plan show that indexs are used on all tables and thats why the speed diference.

Thanks in advance

Omar Zelaya
Hi,

Changing the SQL to use JOIN instead of LEFT JOIN Firebird Plan uses the index on all tables and works as fast as using just WHERE.

Thanks in advance,

Omar Zelaya



Yes, that's something I noticed specifically with Firebird. It seems other databases don't have that much difference between LEFT and INNER joins. Aurelius for now uses LEFT JOIN for the association joins.

Hi,

Why Aurelius always use LEFT JOIN?  Could be other implications later If I change the Aurelius source code to use INNER JOINS?

Thanks in advance,

Omar Zelaya

Hi,

Or change code to use JOIN, and let the database manager chose?

Thanks in advace,

Omar Zelaya

Aurelius for now use LEFT JOIN because the queries can become very complex, with several associations involved, at multiple levels, and also inheritance. Using INNER JOIN plain and simple might break SQL. A more complex algorithm needs to be implemented to support both LEFT and INNER joins when needed. We have it almost done but it's not concluded yet.

Hi, Wagner
  I am waiting for this improvement ;)

One of our customers has been having performance too issues and I was looking at this article https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/ and wondered if the point made here may help, however I suspect that would be hard to do in Aurelius

27. Use LEFT JOIN in the correct way

If you are using LEFT OUTER joins, explicitly put tables in the join from the smallest one to the largest one.

That's indeed very complex to accomplish. Using INNER JOIN makes it much faster in many situations, I guess that would be enough to improve performance overall, when it's implemented.

Hello,

FirebirdSQL does not use any index to filter records in the fields in the tables related to LEFT OUTER JOIN.
I don't know if other databases use it or not.

LEFT OUTER JOIN is not equivalent to INNER JOIN and cannot simply be replaced.
It could introduce some problems.

One option would be that only for the tables for which we have conditions in where to replace LEFT OUTER JOIN with INNER JOIN

1 Like

It seems to be a Firebird-only issue.