So far, NQuery already optimized "trivial case" joins very nicely, such as this one:
SELECT *
FROM Employees e
<type> JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
Regardless of <type>, these queries used a HashMatchIterator. Unfortunately, even simple changes makes it fall back to nested loops:
SELECT *
FROM Employees e
<type> JOIN EmployeeTerritories et ON e.EmployeeID = TO_INT32(et.EmployeeID)
Bummer, as adding a simple conversion kills performance of big queries.
The proposed additional optimization step extracts computations in join conditions into dedicated value slots (if beneficial) to enable the already existing other optimizations. It even works with contrived examples such as this one:
SELECT *
FROM Employees e
<type> JOIN EmployeeTerritories et ON e.EmployeeID + e.ReportsTo = TO_INT32(et.TerritoryID)
Not modified are conjunctions with any operator other than = as well as sides that refer to both input relations (e.g. e.EmployeeID - et.EmployeeID = 0).
So far, NQuery already optimized "trivial case" joins very nicely, such as this one:
Regardless of
<type>, these queries used aHashMatchIterator. Unfortunately, even simple changes makes it fall back to nested loops:Bummer, as adding a simple conversion kills performance of big queries.
The proposed additional optimization step extracts computations in join conditions into dedicated value slots (if beneficial) to enable the already existing other optimizations. It even works with contrived examples such as this one:
Not modified are conjunctions with any operator other than
=as well as sides that refer to both input relations (e.g.e.EmployeeID - et.EmployeeID = 0).