This project has moved. For the latest updates, please go here.
0

Closed

Left Semi Join should be pushed under Concat

description

This query is very slow:

SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT MAX(OrderId)
            FROM Orders o_1
            HAVING MAX(OrderId) = o_0.OrderID
            UNION ALL
            SELECT  AVG(OrderId)
            FROM    [Order Details] od_0
            HAVING  YEAR(OrderDate) > 1998
            AND     o_0.OrderID >= AVG(OrderId)
        )

The reason for this is that it the LSJ should be pushed under the CONCAT node if the LSJ has outer references. This could help to decorrelate the query using IJ or LSJ. Therefore the result should be something like this:

SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT MAX(OrderId)
            FROM Orders o_1
            HAVING MAX(OrderId) = o_0.OrderID
        )
UNION
SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT  AVG(OrderId)
            FROM    [Order Details] od_0
            HAVING  YEAR(OrderDate) > 1998
            AND     o_0.OrderID >= AVG(OrderId)
)

Plese note that the CONCAT node is then transformed in UNION (meaning that a DISTINCT SORT should be inserted).
Closed Oct 31, 2014 at 6:28 AM by terrajobst

comments