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