Very slow to retrieve rows using FireBird

BIZ TMS Aurelius
1
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
2

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?

3
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
4
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



5

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.

6
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

7
Hi,

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

Thanks in advace,

Omar Zelaya
8

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.

9
Hi, Wagner
  I am waiting for this improvement ;)
10

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.

11

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.

12

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

2 Likes
13

It seems to be a Firebird-only issue.

14

Does any one here could provide a use case that demonstrates performance issues of Aurelius with Firebird in those specific cases? We are working in a refactor that might be able to solve those issues. We appreciate if you could provide a test case so we can measure and make sure performance was effectively improved.

15

Hi,

I'm using the lastes Aurelius version and the query mentioned on the original post hangs. In my particular case I keep changing source code to remove LEFT/RIGHT on the JOIN generated SQL and query runs pretty fast.
DATDECRESP 2,290,000 records
DATDECOP 2,988,000 records
DATDEC 2,289,000 records
DATDCECL 14,977,000 records

Thank in advance,

Omar Zelaya

16

The problem is that Firebird cannot optimize a LEFT JOIN when there's no filter on the left side.

So let's say you have two tables: INVOICE and ITEMS

select * from INVOICE left join ITEMS on ()
   where INVOICE.DATE='2021-11-30'

Will run just GREAT as far as you have an index on Invoice.Date

BUT, if you do:

select * from ITEMS left join INVOICE on ()
   where INVOICE.DATE='2021-11-30'

That will run TERRIBLE because Firebird will go through all ITEMS first and than do a lookup for any invoice associated with it.

Of course in this case the solution is simply to use an INNER JOIN since there is no ITEMS without an INVOICE (no detail without master). OR use a RIGHT JOIN to have the same results as the first query (INVOICES without ITEMS).

Regards,

1 Like
17

AFAIK, joined table inheritance is already using INNER joins so it's not a problem.

Some queries using JOINS (CreateAlias) may be affected by this problem. One possible improvement would be to use INNER joins when the [Association] attribute has the TAssociationProp.Required option set (and of course the Entity --where the association is defined-- is left side).

That alone could solve many (if not most) cases (eg.: master-detail relationships where you're looking to retrieve the detail based on filtering on the master) .

Going further, CreateAlias could have a new param TJoinType (Left, Inner, Right) so one can directly set (as needed) what JOIN option should be used. Maybe not a pretty solution, but flexible.

Regards,

18

Another exemple:

TCustomer = class
private
  FName: string;
  FCountry: string;
end;

TInvoice = class
private
  FNumber: Integer;
  FDate: TDateTime;
  [Association]
  FCustomer: TCustomer;
end;

var
  InvoicesFromUK: TList<TInvoice>

begin
  InvoicesFromUK := Manager.Find<TInvoice>
    .CreateAlias('Customer', 'C')
    .Where(Linq.Eq('C.Country', 'UK'))
    .List;

It will generate

select (..) from INVOICE A left join CUSTOMER C on ()
  where C.COUNTRY='UK'

That won't work for Firebird.

But if all invoices have a customer, you may set TAssociationProp.Required to the association attribute. In that case Aurelius could generate an INNER join instead of LEFT join

Than it will work (perform very fast) in Firebird (as much as other databases)

Regards,

19

Just as a side note,

HQBird (a comercial and improved Firebird from IBSurgeon) just released its v2022 update 1 (for Firebird 3 and 4) with an optmizer improvement that implicitly converts LEFT joins into INNER joins when there is a (where) condition on the right side (table). That's probably what most databases do, and currently lacks in Firebird.

Here is the doc with this info, at page 4 (unfortunatly I was able to find just in portuguese): https://ib-aid.com/download/docs/hqbird2022_br.pdf?v=1

Many HQBird improvements gets merged into Open Source Firebird after some time, so, it's possible Firebird won't have this problem in future versions.

BTW, HQBird is wonderful! :slight_smile:

Regards,

20

For those who uses Firebird and are having performance issues, here some code (i hope) it might help:

  1. Dealing with non-optmized LEFT joins

Open (make a copy) Aurelius.Sql.Firebird and override method GenerateSelect in TFirebirdSQLGenerator class, and create a new method OptimizeLeftJoins

TFirebirdSQLGenerator = class(TAnsiSQLGenerator)
(..)
protected
    function GenerateSelect(Command: TSelectCommand): string; override;
    procedure OptimizeLeftJoins(Command: TSelectCommand); virtual;
(..)

implementation

uses
  Generics.Collections, 
(..)

function TFirebirdSQLGenerator.GenerateSelect(Command: TSelectCommand): string;
begin
  OptimizeLeftJoins(Command);
  Result := inherited GenerateSelect(Command);
end;

procedure TFirebirdSQLGenerator.OptimizeLeftJoins(Command: TSelectCommand);
var
  Join, Next: TSQLJoin;
  Table: TSQLTable;
  Where: TObjectList<TSQLWhereField>;
  Stmt: string;
  Field: TSQLWhereField;
begin
  if Command.From is TSQLTable then
    Exit;

  Where := Command.WhereFields;
  if Command.WhereStatement<>'' then
    Stmt := ' ' + StringReplace(Command.WhereStatement, Char(10), ' ', [rfReplaceAll]);

  Next := nil;
  Join := TSQLJoin(Command.From);
  repeat
    if (Join.JoinType=TJoinType.Left) then
    begin
      if (Join.RightRelation is TSQLJoin) then
      begin
        Next := TSQLJoin(Join.RightRelation);
        Table := TSQLTable(Next.LeftRelation);
      end else
      begin
        Next := nil;
        Table := TSQLTable(Join.RightRelation);
      end;

      if Stmt<>'' then
      begin
        if Pos(Format(' %s.', [Table.Alias]), Stmt)>0 then
          Join.JoinType := TJoinType.Inner;
      end;

      { not tested since Where.Count is aways = 0 }
      if (Join.JoinType=TJoinType.Left) and (Where.Count>0) then
      begin
        for Field in Where do
        begin
          if Field.Table.Alias=Table.Alias then
          begin
            Join.JoinType := TJoinType.Inner;
            Break;
          end;
        end;
      end;
      {/ not tested }

    end;
    Join := Next;
  until (Join=nil);

end;

Use with care. THIS IS NOT A FULLY TESTED CODE FOR ANY AND ALL USE CASES

  1. Another issue I've come across is that Aurelius is building multiple JOINS nested. And Firebird also can't optimize that. So IF you are having problens with that, try the following:

Open (make a copy) Aurelius.Sql.Firebird and override method GenerateJoin in TFirebirdSQLGenerator class. This is not a virtual method in the base class so you will have to open Aurelius.Sql.AnsiSQLGenerator and make it virtual in TAnsiSQLGenerator class.

TFirebirdSQLGenerator = class(TAnsiSQLGenerator)
(..)
protected
  function GenerateJoin(Join: TSQLJoin): string; override;

implementation 

function TFirebirdSQLGenerator.GenerateJoin(Join: TSQLJoin): string;
var
  JoinOperator: string;
  Condition: string;
  J: Integer;
  RightSide: string;
  JoinItem: TSQLJoin;
begin
  Assert(Join.Segments.Count > 0);
  JoinItem := Join;

  Result := GenerateRelation(JoinItem.LeftRelation);
  repeat

    case JoinItem.JoinType of
      TJoinType.Inner: JoinOperator := ' INNER JOIN ';
      TJoinType.Left:  JoinOperator := ' LEFT JOIN ';
    end;

    Condition := '';
    for J := 0 to JoinItem.Segments.Count - 1 do
    begin
      if Condition <> '' then
        Condition := Condition + ' AND ';

      Condition := Condition +
        JoinItem.Segments[J].PKField.Table.Alias + '.' +
        IdName(JoinItem.Segments[J].PKField.Field) + ' = ' +
        JoinItem.Segments[J].FKField.Table.Alias + '.' +
        IdName(JoinItem.Segments[J].FKField.Field);
    end;
    for J := 0 to JoinItem.CustomSegments.Count - 1 do
    begin
      if Condition <> '' then
        Condition := Condition + ' AND ';
      Condition := Condition + '(' + JoinItem.CustomSegments[J].Condition + ')';
    end;

    if JoinItem.RightRelation is TSQLJoin then
    begin
      JoinItem := TSQLJoin(JoinItem.RightRelation);
      RightSide := GenerateRelation(JoinItem.LeftRelation);
    end else
    begin
      RightSide := GenerateRelation(JoinItem.RightRelation);
      JoinItem := nil;
    end;

    Result := Result + JoinOperator + RightSide + ' ON (' + Condition + ')' + sLineBreak;

  until JoinItem=nil;
end;

AGAIN, USE WITH CARE. THIS WAS TESTED FOR A LIMITED NUMBER OF USE CASES

Any improvements or corrections is very wellcome.

HTH

1 Like