Performance Comparison: Full outer join vs Union, Union All

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)

---

I think you have a bug in the query - your join between SALE and SALETARGET doesn't include the employeeID column. The COALESCE hides that, but I think you're getting far more rows than you need...

However, the way to speed up queries is to work out why they're slow in the first place; the execution plan is the way to go. Use Query Analyzer to tell you what's going on - are you hitting indices for all joins? Only rewrite the query to use union statements if you've exhausted all the options on the current implementation.

As a matter of style, I don't think it should be necessary to use COALESCE on columns you join on - they can never be null in the first place, and it tends to hide bugs.

---

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 !!