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.

Hi Wagner,
I just ran into the exact same performance issue with Firebird. Fortunately, the involved queries appear very isolated and I was able to workaround by using direct SQL.

Possibly, a "hint attribute", declaring, which join type (default(left), inner or right) to use might be helpful ...

2 Likes

Hi, same performance issue problem, i have added:
...
.Where(Linq.Sql('A.Id = ' + IntToStr(Id)+' '))
...
not very nice :(

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.

Hi Wagner,

Unfortunatly, Firebird is not able to optimize queries when you 'filter' on fields of a left joined table (and nothing on the primary/first table).

Either you use an INNER JOIN so the orther os tables do not affect optimzation, or you change the orders of the tables -- being the least especialized table (base/main table) first (or change LEFT to RIGTH join)

The thing is, in Firebird, when you have a LEFT JOIN it will always first get all records from the first table, than, look for the relation records on the left joined tables.

HTH

I know, that's what we are going to refactor. But it would be good to have a test case to confirm that results are correct, and how much they are more performant.

Understand. Are you looking on examples only from joined Inheritance aproach? Or there are other scenarios?

Unfortunatly I don't have an example at hand, what I can say from experience is that If you have a small hierachy like

Table PERSON and table EMPLOYEE that complements (especializes) table PERSON, as long as PERSON has not a great deal of records, performance will not surfer (but it will always fetch all records from PERSON even when looking for just 1 record from EMPLOYEE.

BUT, if you take a relation between a table like SALES (that will have many records) and EMPLOYEE and is looking for all sales from any salesman with name starting 'SARA', than it will have great impact if using left join and misplace the order of the tables.

Regards,

I can think of a scenario of joined inheritance where this can be a major problem, if you have a ORDERS table with other 'especialized' joined tables. Since this will grow with time it will probably become a no go.

Regards,

Thanks @Farias_Anderson. All this is known. But since we have reports that some users are experiencing performance issues, then I'd like to have that happening and see the results. Performance can only be verified and solved by measuring.

1 Like

Hi again Wagner, I've got a simple example. With 2 small tables but still possible to mesure performance diferences (at least with Firebird trace manager). Here is the model:

  [Entity]
  [Table('OSO')]
  [Inheritance(TInheritanceStrategy.JoinedTables)]
  [Id('FId', TIdGenerator.None)]
  TServiceOrder = class
  strict private
    [Column('ID')]
    FId: Integer;
    [Column('CODIGO', [], 10)]
    FCode: string;
    [Column('ROTA')]
    FRoute: Integer;
  public
    property Id: Integer read FId write FId;
    property Code: string read FCode write FCode;
    property Route: Integer read FRoute write FRoute;
  end;

  [Entity]
  [Table('OSO_EX')]
  [PrimaryJoinColumn('OSO')]
  TExServiceOrder = class(TServiceOrder)
  strict private
    [Column('CARGA_NORMAL')]
    FNormalWorkHours: Integer;
    [Column('CARGA_MAXIMA')]
    FMaxWorkHours: Integer;
  public
    property NormalWorkHours: Integer read FNormalWorkHours write FNormalWorkHours;
    property MaxWorkHours: Integer read FMaxWorkHours write FMaxWorkHours;
  end;

When you retrieve a single TServiceOrder by code:

Firebird is able to use an index and the query takes less than 1 ms.

(BTW, even querying the base class Aurelius build a JOIN with especialized tables. It seems it should not)

When you do the same againts TExServiceOrder, query a single one by code:

Than the order of the tables have changed and now Firebird cannot use an index any more. Now it takes 15 ms and you can see it had to do A LOT of fetches.

It's very simple so I'm not sure if it helps. I'm attaching a code and a small Firebird 3.0 database.

Regards,
AURELIUS-JOIN.zip (346.7 KB)
Project1.zip (22.2 KB)

1 Like

Thank you, @Farias_Anderson. Will take a look at it.

OK @wlandgraf ! From the top of my head I think usually best option would be using INNER joins (for the inheritance relation specificaly) since it would make possible to use indexes on both (all) tables.

But as other have sugested, the possibility to add a param on [PrimaryJoinColumn] to choose between left/inner would be cool and make possible for using the best option for each particular scenario.

BTW, with Firebird at least, when mixing INNER and LEFT joins, INNER joins should always come first (before LEFT or RIGHT joins).

Regards,