Excellent contribution, @Farias_Anderson. I wonder if you could please explain what exactly the code in the item 2 does differently than the original one?
1 Like
Thanks @wlandgraf
Sure. Original code (latest version) is building nested joins while this alternative builds sequential joins. Eg, let's say you have:
[Entity, Automapping]
TCustomer = class
private
FId: Integer;
FName: string;
(..)
[Entity, Automapping]
TInvoice = class
private
FId: Integer;
FDate: TDateTime;
[Association]
FCustomer: TCustomer;
(..)
[Entity, Automapping]
TItem = class
private
FId: Integer;
[Association]
FInvoice: TInvoice;
var
AList: TList<TItem>;
AList := Manager.Find<TItem>
.Where(Linq.Eq('Invoice.Date', Date))
.Add(Linq.Eq('Invoice.Customer.Name', 'CUSTOMER A'))
.List;
Original resulting SQL would be (nested)
SELECT (..)
FROM ITEM A
INNER JOIN (INVOICE B
INNER JOIN CUSTOMER C ON (C.ID = B.CUSTOMER)) ON (B.ID = A.INVOICE)
While if applying the alternate code, it becomes (not nested):
SELECT (..)
FROM ITEM A
INNER JOIN INVOICE B ON (B.ID = A.INVOICE)
INNER JOIN CUSTOMER C ON (C.ID = B.CUSTOMER)
This 'not nested' version is a lot better for Firebird optmization.
BTW, I also made some corrections on the proposed OptimizeLeftJoins method.
procedure TFirebirdSQLGenerator.OptimizeLeftJoins(Command: TSelectCommand);
var
Join, Next: TSQLJoin;
Table: TSQLTable;
Where: TObjectList<TSQLWhereField>;
Stmt: string;
Field: TSQLWhereField;
begin
if (Command.From is TSQLTable) or ((Command.WhereFields.Count=0) and (Command.WhereStatement='')) then
Exit;
Where := Command.WhereFields;
if Command.WhereStatement<>'' then
Stmt := ' ' + StringReplace(Command.WhereStatement, sLineBreak, ' ', [rfReplaceAll]);
Join := TSQLJoin(Command.From);
repeat
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 (Join.JoinType=TJoinType.Left) then
begin
if Stmt<>'' then
begin
if Pos(Format(' %s.', [Table.Alias]), Stmt)>0 then
Join.JoinType := TJoinType.Inner;
end;
{ not tested (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;
Regards,
Thank you. We will have to test, but I'm afraid this solution won't work in all cases, especially on joined-tables inheritance where the complexity of the joins is high.
Hi Wagner.
Yes, it needs a lot more testing indeed. But I have used with some complex queries with joined tables and it worked fine, eg:
[Entity, Automapping]
[Id('FId', TIdGenerator.None)]
[Inheritance(TInheritanceStrategy.JoinedTables)]
TBaseClass = class
private
FId: Integer;
FName: string;
end;
[Entity, Automapping]
TClassA = class(TBaseClass)
private
FSomeValue: Currency;
end;
[Entity, Automapping]
TClassB = class(TBaseClass)
private
FSomeOtherValue: Integer;
end;
[Entity, Automapping]
[Id('FId', TIdGenerator.None)]
TMaster = class
private
FId: Integer;
FCode: string;
[Association([TAssociationProp.Lazy])]
[JoinColumn('CLASS_A')]
FClassA: Proxy<TClassA>;
end;
[Entity, Automapping]
[Id('FId', TIdGenerator.None)]
TEvent = class
private
FId: Integer;
FDate: TDateTime;
[Association]
[JoinColumn('CASS_B')]
FClassB: TClassB;
[Association]
[JoinColumn('MASTER_ID')]
FMaster: TMaster;
end;
(** **)
AList := Manager.Find<TEvent>
.Where(Linq['ClassB.SomeOtherValue'] = 4)
.Add(Linq['Master.ClassA.SomeValue'] = 1.23)
.List;
Works great, although in this case there was only a small difference with Firebird optimization compating to original Aurelius nested joins.
And, there was a problem: OptimizeLeftJoins method would not optimize at all so I had to improve it with some little tweeks (need to do some refactoring before posting) and now it does 
Regards,
As @wlandgraf well said the solutions presented before will not work in all cases, and the reason is simple: the 'Join' tree is not been fully transversed.
For the second issue (nested joins) it seens not to be a big problem, just some of the joins will still be nested. So, it can be improved later.
For the first (and to me most important) issue (optmization of the left joins) it's a big deal: It will not optimize everything and depending on query complexity it will even break (raises an access violation).
So I changed a bit to transverse the full tree. Now it should work fine even for the most complex queries.
procedure TFirebirdSQLGenerator.OptimizeLeftJoins(Command: TSelectCommand);
function FindLeftTable(AJoin: TSQLJoin; ANodes: TStack<TSQLJoin>): TSQLTable;
var
JNode: TSQLJoin;
begin
JNode := AJoin;
repeat
ANodes.Push(JNode);
if JNode.LeftRelation is TSQLTable then
begin
Result := TSQLTable(JNode.LeftRelation);
Break;
end else
JNode := TSQLJoin(JNode.LeftRelation);
until False;
end;
var
Join, Prev: TSQLJoin;
Table: TSQLTable;
Where: TObjectList<TSQLWhereField>;
Field: TSQLWhereField;
Stmt: string;
Nodes: TStack<TSQLJoin>;
JType: TJoinType;
begin
if (Command.From is TSQLTable)
or ((Command.WhereFields.Count=0) and (Command.WhereStatement='')) then
begin
Exit;
end;
Where := Command.WhereFields;
if Command.WhereStatement<>'' then
Stmt := ' ' + StringReplace(Command.WhereStatement, sLineBreak, ' ', [rfReplaceAll]);
Stmt := StringReplace(Stmt, '(', '( ', [rfReplaceAll]);
Nodes := TStack<TSQLJoin>.Create;
try
Join := nil;
Table := FindLeftTable(TSQLJoin(Command.From), Nodes);
while Nodes.Count>0 do
begin
Prev := Join;
Join := Nodes.Pop;
if Join.RightRelation is TSQLTable then
Table := TSQLTable(Join.RightRelation)
else
Table := FindLeftTable(TSQLJoin(Join.RightRelation), Nodes);
JType := TJoinType.Left;
if Pos(Format(' %s.', [Table.Alias]), Stmt)>0 then
JType := TJoinType.Inner;
if (JType=TJoinType.Left) and (Where.Count>0) then
begin
for Field in Where do
begin
if Field.Table.Alias=Table.Alias then
begin
JType := TJoinType.Inner;
Break;
end;
end;
end;
if (JType=TJoinType.Inner) and Assigned(Prev) then
Prev.JoinType := TJoinType.Inner;
Join.JoinType := JType;
end;
finally
Nodes.Free;
end;
end;
HTH
Hi all,
To make things easier, I just created a github repository with a 'final' working version for these codes, sho that anyone interested can check it out.
HTH
1 Like
Thank you @Farias_Anderson, we will include this change and check it in all our existing test scenarios, as soon as we can.
1 Like