Performance Comparison: Full outer join vs Union, Union All

Muhammad Adeel Zahid picture Muhammad Adeel Zahid · Jun 17, 2011 · Viewed 11.3k times · Source

I have tables named:

mktActualsales (SaleID, EmployeeID, PeriodID,PositionID)
 mktActualSalesItems(SaleItemID, saleID, Item, Quantity)
 mktSalesTargets(TargetID, EmployeeID, PeriodID,PositionID)
 mktSalesTargetItems(TargetITemID, TargetID,ItemID, Quantity )
 sysPeriods(PeriodID, StartDate, EndDate) 

The relationships are obvious among above tables sale and saleDetails are related through SaleID and Target and TargetDetail are related through TargetID. What I want to display is Employees sales and targets in all periods. There are periods where sale is missing and in other periods targets might be missing.

My plan of doing it (so far) is to take inner join of sale and saleDetail in one subquery, target and targetDetail in another subquery and then full outer join on both subqueries get me the data I need. But the result is awfully slow. what i can do to speed up performance. I have Googled around for performance comparison between full outer join and union all but did not have any luck. At the moment I have no idea if result I want can be achieved using Union/Union All.

Edit

This is the query that I have so far. Edited the tables as well

SELECT  sale.ActualSaleID, 
        COALESCE (sale.EmployeeID, saleTarget.EmployeeID) AS EmployeeID, 
        COALESCE (sale.PositionID, saleTarget.PositionID) AS PositionID, 
        COALESCE (sale.PeriodID, saleTarget.PeriodID) AS PeriodID, 
        COALESCE (sale.SKUID, saleTarget.SKUID) AS SKUID, 
        COALESCE (sale.SalesQuantity, 0) AS SalesQuantity, 
        saleTarget.SalesTargetID, 
        COALESCE (saleTarget.TargetQuantity, 0) AS TargetQuantity, 
        saleTarget.SalesTargetItemID, 
        sale.ActualSaleItemID, 
        p.StartDate, 
        p.EndDate
FROM (
        SELECT  s.ActualSaleID, 
                s.EmployeeID, 
                s.PeriodID, 
                s.PositionID, 
                si.ActualSaleItemID, 
                si.SKUID, 
                si.SalesQuantity
        FROM dbo.mktActualSaleItems AS si 
            INNER JOIN dbo.mktActualSales AS s 
                ON si.ActualSaleID = s.ActualSaleID
    ) AS sale 
    FULL OUTER JOIN
    (
        SELECT  t.EmployeeID, 
                t.PeriodID, 
                t.PositionID, 
                t.SalesTargetID, 
                ti.SKUID, 
                ti.TargetQuantity, 
                ti.SalesTargetItemID
        FROM dbo.mktSalesTargetItems AS ti 
            INNER JOIN dbo.mktSalesTargets AS t 
                ON t.SalesTargetID = ti.SalesTargetID
    ) AS saleTarget 
        ON sale.PeriodID = saleTarget.PeriodID 
        AND sale.PositionID = saleTarget.PositionID 
        AND sale.SKUID = saleTarget.SKUID 
    INNER JOIN dbo.sysPeriods AS p 
        ON p.PeriodID = COALESCE (sale.PeriodID, saleTarget.PeriodID)

Answer

Cyril Gandon picture Cyril Gandon · Jun 17, 2011

I don't think you need subquery, you can do the same thing in one query

SELECT  s.ActualSaleID, 
        COALESCE (s.EmployeeID, t.EmployeeID) AS EmployeeID, 
        COALESCE (s.PositionID, t.PositionID) AS PositionID, 
        COALESCE (s.PeriodID, t.PeriodID) AS PeriodID, 
        COALESCE (si.SKUID, ti.SKUID) AS SKUID, 
        COALESCE (si.Quantity, 0) AS SalesQuantity, 
        t.TargetID, 
        COALESCE (ti.Quantity, 0) AS TargetQuantity, 
        ti.TargetITemID, 
        si.SaleItemID, 
        p.StartDate, 
        p.EndDate
FROM dbo.mktActualSales AS s 
        INNER JOIN dbo.mktActualSaleItems AS si 
            ON si.ActualSaleID = s.ActualSaleID
    FULL OUTER JOIN dbo.mktSalesTargets AS t 
        ON s.PeriodID = t.PeriodID 
        AND s.PositionID = t.PositionID 
        AND si.SKUID = ti.SKUID 
            INNER JOIN dbo.mktSalesTargetItems AS ti 
                ON t.SalesTargetID = ti.SalesTargetID
    INNER JOIN dbo.sysPeriods AS p 
        ON p.PeriodID = COALESCE (s.PeriodID, t.PeriodID)

I'm not sure about naming fields, but you get the idea.

This may speed up the query.

Don't forget to check indexes too !!