I have a query that is used to populate an aggregate table for reporting purposes. The query comes from another developer here at the company I work for but is my job to make it run fast. So far all of my early attempts failed. I've tried several things with this query and this is where I am at the moment. I have shaved about a half an hour of it's load time already but am stuck and thinking I will probably just have to re-do the whole thing. I'm hoping someone on here can see what if I'm missing anything and give me some pointers on how to fix this query.
SELECT P.CompanyID,
P.CompanyName,
P.StoreID,
P.StoreName,
P.ReportDate,
Isnull((SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS PullNet
FROM FactSalesTransaction AS FT
LEFT JOIN (SELECT TransactionID,
DimStoreID,
DimBusinessDateID,
Sum(PaymentAmount) AS PaymentAmount
FROM FactSalesPayment
WHERE DimPaymentTypeID <> 2
AND ModStatusFlg <> 'D'
GROUP BY TransactionID,
DimStoreID,
DimBusinessDateID) AS FP
ON FP.TransactionID = FT.TransactionID
AND FP.DimStoreID = FT.DimStoreID
INNER JOIN DimCalendar AS C
ON FT.DimBusinessDateID = C.DimCalendarID
AND FP.DimBusinessDateID = C.DimCalendarID AND C.CalendarDate >= '12/4/2012'
WHERE FT.DimStoreID = P.DimStoreID
AND FT.DimBusinessDateID = P.DimBusinessDateID
AND FT.ModStatusFlg <> 'D'), 0) AS StoreCash,
SR.CashDeposit AS StoreResp,
SN.StoreNet,
P.DimEmployeeID AS EmpID,
P.EmpName,
P.RegisterID,
P.PullNumber,
Isnull((SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS PullNet
FROM FactSalesTransaction AS FT
LEFT JOIN (SELECT TransactionID,
DimStoreID,
DimBusinessDateID,
Sum(PaymentAmount) AS PaymentAmount
FROM FactSalesPayment
WHERE DimPaymentTypeID <> 2
AND ModStatusFlg <> 'D'
GROUP BY TransactionID,
DimStoreID,
DimBusinessDateID) AS FP
ON FP.TransactionID = FT.TransactionID
AND FP.DimStoreID = FT.DimStoreID
INNER JOIN DimCalendar AS C
ON FT.DimBusinessDateID = C.DimCalendarID
AND FP.DimBusinessDateID = C.DimCalendarID AND C.CalendarDate >= '12/4/2012'
WHERE FT.DimStoreID = P.DimStoreID
AND FT.DimRegisterID = P.DimRegisterID
AND FT.TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime
AND FT.ModStatusFlg <> 'D'), 0) AS PullCash,
P.PullResp + Isnull((SELECT Sum(SkimAmount)
FROM FactSkims
WHERE DimStoreID = P.DimStoreID
AND DimRegisterID = P.DimRegisterID
AND SkimDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime), 0) AS PullResp,
Isnull((SELECT Sum(NetSales) AS PullNet
FROM FactSalesTransaction AS FT
INNER JOIN DimCalendar AS C
ON FT.DimBusinessDateID = C.DimCalendarID AND C.CalendarDate >= '12/4/2012'
WHERE DimStoreID = P.DimStoreID
AND DimRegisterID = P.DimRegisterID
AND TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime
AND ModStatusFlg <> 'D'), 0) AS PullNet
FROM (SELECT C.CompanyID,
C.CompanyName,
S.StoreID,
S.StoreName,
F.DimEmployeeID,
E.FirstName + ' ' + E.LastName AS EmpName,
CASE
WHEN F.PullDrawerStartTime <> '1900-01-01' THEN F.PullDrawerStartTime
ELSE Isnull(Cast((SELECT TOP 1 Dateadd(SECOND, 1, PullDrawerEndTime)
FROM FactPullDrawer
WHERE PullDrawerEndTime < F.PullDrawerEndTime
AND DimStoreID = F.DimStoreID
AND DimRegisterID = F.DimRegisterID
AND DimBusinessDateID = F.DimBusinessDateID
ORDER BY PullDrawerEndTime DESC) AS DATETIME), BD.CalendarDate + Isnull(Cast(Cast(ST.SiteSettingValue AS TIME) AS DATETIME), Cast('4:00:00 AM' AS DATETIME)))
END AS PullDrawerStartTime,
F.PullDrawerEndTime,
BD.CalendarDate AS ReportDate,
R.RegisterID,
R.DimRegisterID,
(SELECT Count(PullDrawerEndTime)
FROM FactPullDrawer
WHERE PullDrawerEndTime < F.PullDrawerEndTime
AND DimStoreID = F.DimStoreID
AND DimRegisterID = F.DimRegisterID
AND DimBusinessDateID = F.DimBusinessDateID) + 1 AS PullNumber,
Isnull(F.Amount, 0) AS PullResp,
F.DimStoreID,
F.DimBusinessDateID
FROM FactPullDrawer AS F
INNER JOIN DimCompany AS C
ON C.DimCompanyID = F.DimCompanyID
INNER JOIN DimStore AS S
ON S.DimStoreID = F.DimStoreID
INNER JOIN DimCalendar AS BD
ON BD.DimCalendarID = F.DimBusinessDateID
AND BD.CalendarDate >= '12/4/2012'
INNER JOIN DimEmployee AS E
ON F.DimEmployeeID = E.DimEmployeeID
INNER JOIN DimRegister AS R
ON R.DimRegisterID = F.DimRegisterID
LEFT JOIN DimSiteSettings AS ST
ON S.StoreID = ST.StoreID
AND C.CompanyID = ST.CompanyID
AND ST.SiteSettingFieldID = 1412) AS P
INNER JOIN (SELECT DimStoreID,
DimBusinessDateID,
Sum(NetSales) AS StoreNet
FROM FactSalesTransaction
WHERE ModStatusFlg <> 'D'
GROUP BY DimStoreID,
DimBusinessDateID) AS SN
ON SN.DimStoreID = P.DimStoreID
AND SN.DimBusinessDateID = P.DimBusinessDateID
INNER JOIN (SELECT CompanyID,
StoreID,
ReportDate,
Sum(ValTotal) AS CashDeposit
FROM AgtAccountingReport
WHERE ReportCatOrder = 7
AND ReportElementOrder < 100
AND ReportElementOrder NOT IN ( 7, 9, 10, 16,17, 18, 19, 20, 21 )
AND ReportDate >= '10/28/2012'
GROUP BY CompanyID,
StoreID,
ReportDate) AS SR
ON SR.CompanyID = P.CompanyID
AND SR.StoreID = P.StoreID
AND SR.ReportDate = P.ReportDate
I'm thinking all of the nested SELECT's which is why I was thinking I would just start from scratch. Any help would be appreciated.
