perfornance problem with joined table inheritance

I'm having a performance problem when I add an entity that uses inheritance of tables joined with xdata because xdata does a refresh after saving. I saw that it is because of the plan that the firebird 3.0 is building because the where is using the id of the parent entity (Entidade1) instead of the child (Entidade2). test done with 400.00 records in the table.

interface

uses
Aurelius.Mapping.Attributes
;

type

[Entity, Automapping]
[Inheritance(TInheritanceStrategy.JoinedTables)]
TEntidade1 = class
private
FId: int64;
FCampo1: integer;
public
property Id: int64 read FId write FId;
property Campo1: integer read FCampo1 write FCampo1;
end;

[Entity, Automapping]
[PrimaryJoinColumn('id')]
TEntidade2 = class(TEntidade1)
private
FCampo20: integer;
FCampo21: integer;
FCampo22: integer;
FCampo23: integer;
FCampo24: integer;
FCampo25: integer;
FCampo26: integer;
FCampo27: integer;
FCampo28: integer;
FCampo29: integer;

[Column('campo30', [TColumnProp.Required], 60)]
FCampo30: string;
[Column('campo31', [TColumnProp.Required], 60)]
FCampo31: string;
[Column('campo32', [TColumnProp.Required], 60)]
FCampo32: string;
[Column('campo33', [TColumnProp.Required], 60)]
FCampo33: string;
[Column('campo34', [TColumnProp.Required], 60)]
FCampo34: string;
[Column('campo35', [TColumnProp.Required], 60)]
FCampo35: string;
[Column('campo36', [TColumnProp.Required], 60)]
FCampo36: string;
[Column('campo37', [TColumnProp.Required], 60)]
FCampo37: string;
[Column('campo38', [TColumnProp.Required], 60)]
FCampo38: string;
[Column('campo39', [TColumnProp.Required], 60)]
FCampo39: string;

FCampo40: currency;
FCampo41: currency;
FCampo42: currency;
FCampo43: currency;
FCampo44: currency;
FCampo45: currency;
FCampo46: currency;
FCampo47: currency;
FCampo48: currency;
FCampo49: currency;

public
property Campo20: integer read FCampo20 write FCampo20;
property Campo21: integer read FCampo21 write FCampo21;
property Campo22: integer read FCampo22 write FCampo22;
property Campo23: integer read FCampo23 write FCampo23;
property Campo24: integer read FCampo24 write FCampo24;
property Campo25: integer read FCampo25 write FCampo25;
property Campo26: integer read FCampo26 write FCampo26;
property Campo27: integer read FCampo27 write FCampo27;
property Campo28: integer read FCampo28 write FCampo28;
property Campo29: integer read FCampo29 write FCampo29;
property Campo30: string read FCampo30 write FCampo30;
property Campo31: string read FCampo31 write FCampo31;
property Campo32: string read FCampo32 write FCampo32;
property Campo33: string read FCampo33 write FCampo33;
property Campo34: string read FCampo34 write FCampo34;
property Campo35: string read FCampo35 write FCampo35;
property Campo36: string read FCampo36 write FCampo36;
property Campo37: string read FCampo37 write FCampo37;
property Campo38: string read FCampo38 write FCampo38;
property Campo39: string read FCampo39 write FCampo39;
property Campo40: currency read FCampo40 write FCampo40;
property Campo41: currency read FCampo41 write FCampo41;
property Campo42: currency read FCampo42 write FCampo42;
property Campo43: currency read FCampo43 write FCampo43;
property Campo44: currency read FCampo44 write FCampo44;
property Campo45: currency read FCampo45 write FCampo45;
property Campo46: currency read FCampo46 write FCampo46;
property Campo47: currency read FCampo47 write FCampo47;
property Campo48: currency read FCampo48 write FCampo48;
property Campo49: currency read FCampo49 write FCampo49;
end;

SELECT A.id AS A_id, A.CAMPO_20 AS A_CAMPO_20, A.CAMPO_21 AS A_CAMPO_21, A.CAMPO_22 AS A_CAMPO_22, A.CAMPO_23 AS A_CAMPO_23, A.CAMPO_24 AS A_CAMPO_24, A.CAMPO_25 AS A_CAMPO_25, A.CAMPO_26 AS A_CAMPO_26, A.CAMPO_27 AS A_CAMPO_27, A.CAMPO_28 AS A_CAMPO_28, A.CAMPO_29 AS A_CAMPO_29, A.campo30 AS A_campo30, A.campo31 AS A_campo31, A.campo32 AS A_campo32, A.campo33 AS A_campo33, A.campo34 AS A_campo34, A.campo35 AS A_campo35, A.campo36 AS A_campo36, A.campo37 AS A_campo37, A.campo38 AS A_campo38, A.campo39 AS A_campo39, A.CAMPO_40 AS A_CAMPO_40, A.CAMPO_41 AS A_CAMPO_41, A.CAMPO_42 AS A_CAMPO_42, A.CAMPO_43 AS A_CAMPO_43, A.CAMPO_44 AS A_CAMPO_44, A.CAMPO_45 AS A_CAMPO_45, A.CAMPO_46 AS A_CAMPO_46, A.CAMPO_47 AS A_CAMPO_47, A.CAMPO_48 AS A_CAMPO_48, A.CAMPO_49 AS A_CAMPO_49, B.ID AS B_ID, B.CAMPO_1 AS B_CAMPO_1
FROM ENTIDADE_2 A
LEFT JOIN ENTIDADE_1 B ON (B.ID = A.id)
WHERE B.ID = :p_0

Plan
PLAN JOIN (A NATURAL, B INDEX (PK_ENTIDADE_1)

Execute time = 4s 953ms

SELECT A.id AS A_id, A.CAMPO_20 AS A_CAMPO_20, A.CAMPO_21 AS A_CAMPO_21, A.CAMPO_22 AS A_CAMPO_22, A.CAMPO_23 AS A_CAMPO_23, A.CAMPO_24 AS A_CAMPO_24, A.CAMPO_25 AS A_CAMPO_25, A.CAMPO_26 AS A_CAMPO_26, A.CAMPO_27 AS A_CAMPO_27, A.CAMPO_28 AS A_CAMPO_28, A.CAMPO_29 AS A_CAMPO_29, A.campo30 AS A_campo30, A.campo31 AS A_campo31, A.campo32 AS A_campo32, A.campo33 AS A_campo33, A.campo34 AS A_campo34, A.campo35 AS A_campo35, A.campo36 AS A_campo36, A.campo37 AS A_campo37, A.campo38 AS A_campo38, A.campo39 AS A_campo39, A.CAMPO_40 AS A_CAMPO_40, A.CAMPO_41 AS A_CAMPO_41, A.CAMPO_42 AS A_CAMPO_42, A.CAMPO_43 AS A_CAMPO_43, A.CAMPO_44 AS A_CAMPO_44, A.CAMPO_45 AS A_CAMPO_45, A.CAMPO_46 AS A_CAMPO_46, A.CAMPO_47 AS A_CAMPO_47, A.CAMPO_48 AS A_CAMPO_48, A.CAMPO_49 AS A_CAMPO_49, B.ID AS B_ID, B.CAMPO_1 AS B_CAMPO_1
FROM ENTIDADE_2 A
LEFT JOIN ENTIDADE_1 B ON (B.ID = A.id)
WHERE A.ID = :p_0

Plan
PLAN JOIN (A INDEX (PK_ENTIDADE_2), B INDEX (PK_ENTIDADE_1))

Execute time = 16ms

It looks like a limitation in Firebird. I've seen such issue before, for some reason Firebird doesn't optimize LEFT JOIN operations very well. This doesn't seem to happen in other RDBMS.

That's currently how Aurelius does, as the "original" primary key ID belongs to the base table, thus it searches for it. Unfortunately there is no way to do it differently, for now.

wouldn't you be able to use RIGHT JOIN in this specific case?

SELECT A.id AS A_id, A.CAMPO_20 AS A_CAMPO_20, A.CAMPO_21 AS A_CAMPO_21, A.CAMPO_22 AS A_CAMPO_22, A.CAMPO_23 AS A_CAMPO_23, A.CAMPO_24 AS A_CAMPO_24, A.CAMPO_25 AS A_CAMPO_25, A.CAMPO_26 AS A_CAMPO_26, A.CAMPO_27 AS A_CAMPO_27, A.CAMPO_28 AS A_CAMPO_28, A.CAMPO_29 AS A_CAMPO_29, A.campo30 AS A_campo30, A.campo31 AS A_campo31, A.campo32 AS A_campo32, A.campo33 AS A_campo33, A.campo34 AS A_campo34, A.campo35 AS A_campo35, A.campo36 AS A_campo36, A.campo37 AS A_campo37, A.campo38 AS A_campo38, A.campo39 AS A_campo39, A.CAMPO_40 AS A_CAMPO_40, A.CAMPO_41 AS A_CAMPO_41, A.CAMPO_42 AS A_CAMPO_42, A.CAMPO_43 AS A_CAMPO_43, A.CAMPO_44 AS A_CAMPO_44, A.CAMPO_45 AS A_CAMPO_45, A.CAMPO_46 AS A_CAMPO_46, A.CAMPO_47 AS A_CAMPO_47, A.CAMPO_48 AS A_CAMPO_48, A.CAMPO_49 AS A_CAMPO_49, B.ID AS B_ID, B.CAMPO_1 AS B_CAMPO_1
FROM ENTIDADE_2 A
RIGHT JOIN ENTIDADE_1 B ON (B.ID = A.id)
WHERE B.ID = :p_0

Plan
PLAN JOIN (B INDEX (PK_ENTIDADE_1), A INDEX (PK_ENTIDADE_2))

Of course there are many SQL statements that could be created, but currently it's not possible. It would require Aurelius modification and it's not a trivial one.

Hi there,

First, I don't get it why is filtering on the second table ID instead of the primary (base) table? I think it make more sense to get the record by the base table ID (or I'm missing something?)

And seconde, on doing this way, why is it a LEFT join intead of a FULL join (since the base table record is required)? A full join whould resolve that

Regards,

The query starts from the most specialized table. The LEFT join is for now used because of the complexity of the logic in joining several tables.