Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
6  
The sub-queries do look like they might not optimise well. Converting them into aggregates in the main query is probably a good place to start. You might also look at pulling the nested subqueries out into temporary tables. You can also index temp tables after populating them. –  ConcernedOfTunbridgeWells Dec 5 '12 at 20:37
    
I have certainly noticed that those sub queries seem to kill the execution plan. –  Zane Dec 5 '12 at 20:55
1  
could you run this and give the actual execution plan? –  swasheck Dec 5 '12 at 21:06
    
Zane, I agree with @ConcernedOfTunbridgeWells that the inline subqueries should be converted. You could also start by removing them one by one as a test to find if there are other bottlenecks, besides these. –  ypercube Dec 5 '12 at 21:20
    
Zane, I'd begin this one with populating a temporary table with the SR result set - and then join your nested statements to that SR result set. I haven't gone through the entire statement in detail but at first blush it would return the correct data - to the point of improving performance, it would have a marked impacted I believe. –  Jeremy Lowell Dec 5 '12 at 23:02
show 1 more comment

3 Answers 3

up vote 5 down vote accepted

Okay so this is what I actually did to make this query that used to run in about an hour and a half run in under a minute. First I did a little more digging to see exactly what this was doing. There are a few major sub selects in the outer most portion of the query. They look like this.

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

These select statements are joining two of my largest tables. FactSalesTransaction(105 Million Records) to FactSalesPayment(102 Million records) and it is doing so in a select within a select. Which essentially means that for every row returned it is executing this query. Well this query is usually run for about seven days worth of data and therefore returns about 19,000 Records. That Mean's the 3 sub selects to these massive tables need to be executed 19,000 times. Bingo I think I've found where my performance loss was. So I switched those queries to a left join. Nothing complicated just made it so they only had to join once. The left join to replace looks like this.

LEFT JOIN (SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS StoreCash, FT.DimStoreID, FT.DimBusinessDateID
               FROM   FactSalesTransaction AS FT
                      LEFT JOIN (SELECT TransactionID,
                                        DimStoreID,
                                        Sum(PaymentAmount) AS PaymentAmount
                                 FROM   FactSalesPayment
                                 WHERE  DimPaymentTypeID <> 2
                                        AND ModStatusFlg <> 'D'
                                 GROUP  BY TransactionID,
                                           DimStoreID) AS FP
                        ON FP.TransactionID = FT.TransactionID
                           AND FP.DimStoreID = FT.DimStoreID
               WHERE   FT.ModStatusFlg <> 'D'
               GROUP BY FT.DimStoreID, FT.DimBusinessDateID)  AS FP
                       ON FP.DimStoreID = P.DimStoreID
                      AND FP.DimBusinessDateID = P.DimBusinessDateID

As you can see I really didn't change much to the select itself just altered it to not run 19,000 times. The next thing I did was altered the query to a stored procedure where a take the date range the user or in this case the ETL process gives(usually 7 days back) Select from DimCalendar the DimCalendarID for that day so the query is using integers instead of date times and overall has less records to join. Making the final query look like this.

CREATE PROCEDURE [dbo].[NoneOfYourBusinessWhatINamedIt] 
@ETLLoadDate DATETIME 
AS
BEGIN
DECLARE @DimCal Int = (SELECT DimCalendarID  FROM DimCalendar WHERE CalendarDate = @ETLLoadDate)
END
SELECT P.CompanyID,
       P.CompanyName,
       P.StoreID,
       P.StoreName,
       P.ReportDate,
       FP.StoreCash,
       SR.CashDeposit AS StoreResp,
       SN.StoreNet,
       P.DimEmployeeID AS EmpID,
       P.EmpName,
       P.RegisterID,
       P.PullNumber,
       ISNULL(SUM(PC.PullCash), 0) AS PullCash,
       P.PullResp  AS PullResp,
       ISNULL(SUM(PN.PullNet),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
                                          --AND DimBusinessDateID = @DimCal
                                   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
               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
          WHERE F.DimBusinessDateID = @DimCal) AS P
       INNER JOIN (SELECT DimStoreID,
                          DimBusinessDateID,
                          Sum(NetSales) AS StoreNet
                   FROM   FactSalesTransaction
                   WHERE  ModStatusFlg <> 'D' AND DimBusinessDateID = @DimCal
                   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 )
                   GROUP  BY CompanyID,
                             StoreID,
                             ReportDate) AS SR
         ON SR.CompanyID = P.CompanyID
            AND SR.StoreID = P.StoreID
            AND SR.ReportDate = P.ReportDate 
        LEFT JOIN (SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS StoreCash, FT.DimStoreID, FT.DimBusinessDateID
               FROM   FactSalesTransaction AS FT
                      LEFT JOIN (SELECT TransactionID,
                                        DimStoreID,
                                        Sum(PaymentAmount) AS PaymentAmount
                                 FROM   FactSalesPayment
                                 WHERE  DimPaymentTypeID <> 2
                                        AND ModStatusFlg <> 'D'
                                        AND DimBusinessDateID = @DimCal
                                 GROUP  BY TransactionID,
                                           DimStoreID) AS FP
                        ON FP.TransactionID = FT.TransactionID
                           AND FP.DimStoreID = FT.DimStoreID
               WHERE   FT.ModStatusFlg <> 'D' AND DimBusinessDateID = @DimCal
               GROUP BY FT.DimStoreID, FT.DimBusinessDateID)  AS FP
                       ON FP.DimStoreID = P.DimStoreID
                      AND FP.DimBusinessDateID = P.DimBusinessDateID


         LEFT JOIN(SELECT Sum(FT.GrossSales - Isnull(FP.PaymentAmount, 0)) AS PullCash, FT.DimStoreID, FT.DimRegisterID, FT.TransactionDateTime
               FROM   FactSalesTransaction AS FT
                      LEFT JOIN (SELECT TransactionID,
                                        DimStoreID,
                                        Sum(PaymentAmount) AS PaymentAmount
                                 FROM   FactSalesPayment
                                 WHERE  DimPaymentTypeID <> 2
                                        AND ModStatusFlg <> 'D'
                                        AND DimBusinessDateID = @DimCal
                                 GROUP  BY TransactionID,
                                           DimStoreID) AS FP
                        ON FP.TransactionID = FT.TransactionID
                           AND FP.DimStoreID = FT.DimStoreID
               WHERE  FT.ModStatusFlg <> 'D' AND DimBusinessDateID = @DimCal
               GROUP BY FT.DimStoreID, FT.DimRegisterID, FT.TransactionDateTime) AS PC
               ON PC.DimStoreID = P.DimStoreID
                      AND PC.DimRegisterID = P.DimRegisterID
                      AND PC.TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime     
         LEFT JOIN (SELECT Sum(SkimAmount) as SkimAmount, DimStoreID, DimRegisterID, SkimDateTime
                            FROM   FactSkims
                            GROUP BY DimStoreID, DimRegisterID, SkimDateTime) AS FS
                            ON  FS.DimStoreID = P.DimStoreID
                                   AND FS.DimRegisterID = P.DimRegisterID
                                   AND FS.SkimDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime     
         LEFT JOIN (SELECT Sum(NetSales) AS PullNet, DimStoreID, DimRegisterID, TransactionDateTime
               FROM   FactSalesTransaction
               WHERE  ModStatusFlg <> 'D' AND DimBusinessDateID = @DimCal
               GROUP BY DimStoreID, DimRegisterID, TransactionDateTime) AS PN                              
               ON PN.DimStoreID = P.DimStoreID
                      AND PN.DimRegisterID = P.DimRegisterID
                      AND PN.TransactionDateTime BETWEEN P.PullDrawerStartTime AND P.PullDrawerEndTime
                      AND PC.TransactionDateTime = PN.TransactionDateTime
       group by P.CompanyID, P.CompanyName, P.StoreID,P.StoreName,P.ReportDate,FP.StoreCash,SR.CashDeposit,SN.StoreNet,P.DimEmployeeID,P.EmpName,P.RegisterID,P.PullNumber,P.PullResp

After doing a test for the speed increase to see if it was worth the index space. I added two Non-Clustered indexes on ModStatusFlag and DimBusinessDateID that included the other columns requested by this query. On both FactSalesTransaction and FactSalesPayment. I could probably do a little more to clean this up and make it run faster however the performance gain would be minimal and there are much bigger fish to fry at the moment. Long story short be careful with your sub-select Statements.

share|improve this answer
add comment

I would strongly recommend temporary tables and indexes. I would basically recommend the following.

Use temporary table

Temporary table extremely optimize your query performance. Use int in place where you are using WHERE clause on a large table or in palce of subquery.

Create Indexes

Esp on columns that you are using. Will make things fast fast.

Run Individual Sub queries

Find which one is relatively slower

Join Sub Queries and check performance as you JOIN

Find out which JOIN creates the problem. Is usually come down to one JOIN that take all the time. Also when you use AND with JOIN, that may take very long in larger tables. For example you may need to re-write this part of your query as follows.

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

I would rewrite this as

INNER JOIN DimCalendar AS C
ON FT.DimBusinessDateID = C.DimCalendarID
AND FP.DimBusinessDateID = C.DimCalendarID  
WHERE  FT.DimStoreID = P.DimStoreID AND C.CalendarDate >= '12/4/2012'

Once you find the JOIN that slows your peformance

Use temporary table there to optimize. Temporary tables work best if you are using lets say 3k records in a 100k table.

These are just some hints that can help you out.

share|improve this answer
2  
Good effort but marginally -1. WHERE/JOIN clauses are transferrable with negligible effect. Correlated subqueries also cannot be run "individually". "fast fast"?! Use that on SO, but not on dba.se. –  孔夫子 Dec 5 '12 at 21:40
    
@RichardTheKiwi I answered this from my personnel experience working with slow queries where I made a 5 min query run within second. I am by no means an expert as my nick suggests but I had issues with using AND and OR in JOIN. At least putting them in where clause improves readability. –  Jackofall Dec 5 '12 at 21:53
2  
It depends on the type of JOIN. For INNER joins, there is no difference. Interesting you mention OR. Moving an OR in conjunction with AND can change semantics. –  孔夫子 Dec 5 '12 at 22:03
3  
"Temporary table extremely optimize your query performance" - I don't really agree with this, they're not a panacea for slow performance :-). –  Marian Dec 6 '12 at 9:21
    
I will be taking all of the suggestions I got from chat and comments and using them to rewrite this beast. I'll post the new code once I've tested it out. Hopefully I can actually work on that today. –  Zane Dec 6 '12 at 13:17
show 1 more comment

Why not consider a 2 ETL tiered staging phase, design a schema that considers the various nested tables and then a separate ETL phase to populate the aggregated table/s or the allow for the aggregation to be completed within a dedicated reporting tool such as SAP Business Objects. A reporting layer such as universe could handle schema associated with the nested layer db implementation and the aggregation could be designed with a reporting layer! This would allow multiple users to access the information at relevant times of the day rather running the query each time for each user requesting the information from the large query! It is just a thought and I am not too sure what systems you have access to at your place of work!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.