Performance Comparison: Full outer join vs Union, Union All

Question

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)

Can you give us the query you have so far and some sample what you are trying to achieve? I do not fully understand what you want the result to be. — Oliver Hanappi, Jun 17 '11 at 07:39

Cyril Gandon · Answer 1 · 2011-06-17T10:24:19.453

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

putting them in subqueries just makes to easy to comprehend for me. would doing it without subquery increase performance? — Muhammad Adeel Zahid, Jun 17 '11 at 09:21
Well, it depends, your dbms could be smart enough to doing appropriate join with your query, but it may be not. You do not let him the chance to choose the best plan by giving him subquery, whereas in one query, you let the dbms to choose the best plan. The best way is to run the query... Or to look at the execution plan ! — Cyril Gandon, Jun 17 '11 at 09:22
PeriodID and PositionID are present in mktActualSales an mktSalesTargets where as SKUID is present in mktSaleTargetItems and mktAcutalSaleItems. can you update this query accordingly — Muhammad Adeel Zahid, Jun 17 '11 at 10:15

score 1 · Answer 2 · answered Jun 17 '11 at 09:01

1

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.

answered Jun 17 '11 at 09:01

Neville Kuyt

29,247
1
37
52

EmployeeID has one to one relation to designationID i only have a little denormalization here and it does not give incorrect results. Since i m taking full outer join so there are good chances that columns from one table may be null – Muhammad Adeel Zahid Jun 17 '11 at 09:24
I can't see the designationID in the query at all - you're joining SALE and SALETARGET on the columns period, position and skuid. Therefore, you are getting a cross join - one record for each employee in both sale and saletarget. Is that not correct? Could you try running the query returning both sale.EmployeeID and saleTarget.EmployeeID to check? – Neville Kuyt Jun 17 '11 at 10:11
Ah - so PositionID is also a unique identifier for the employee? I'd assumed it linked to a table with job titles or some such - "sales manager", "customer service rep". Have you looked at the query plan? – Neville Kuyt Jun 17 '11 at 10:41
yep exactly to avoid another join – Muhammad Adeel Zahid Jun 17 '11 at 11:41

Performance Comparison: Full outer join vs Union, Union All

2 Answers2