CODEWITHSUNDEEP
sqlsql-server
Martin Bergius
Martin Bergius

Reputation: 3

How to improve slow sql query with aggregate functions

I want to show top ten customers,sales,margin where customers is registred during this accounting year. The query takes about 65seconds to run and it is not accepted :-( As you may see i am not good at sql and will be very happy for help to improve the query.

SELECT Top 10 
  AcTr.R3, Actor.Nm, 
  SUM(CASE WHEN AcTr.AcNo<='3999' THEN AcAm*-1 ELSE 0 END) AS Sales , 
  SUM(AcAm*-1) AS TB
FROM AcTr, Actor 
WHERE (Actor.CustNo = AcTr.R3) AND
      (Actor.CustNo <> '0') AND
      (Actor.CreDt >= '20180901') AND 
      (Actor.CreDt <= '20190430') AND 
      AcTr.AcYr = '2018' AND
      AcTr.AcPr <= '8' AND
      AcTr.AcNo>='3000' AND
      AcTr.AcNo <= '4999'
GROUP BY AcTr.R3, Actor.Nm 
ORDER BY Sales DESC

Upvotes: 0

Views: 1362

Answers (1)

DRapp
DRapp

Reputation: 48139

Welcome to the community. You have a good start, but future, it is more helpful if you can provide (as commented), the CREATE table declarations so users know the actual data types. Not always required, but helps.

As for your query layout, it is more common to show the JOIN syntax instead of WHERE showing relations between tables, but that comes in time and practice.

Indexes help and should be based on a combination of both WHERE/JOIN criteria AND Grouping fields. Also, if fields are numeric, then do not 'quote' them, just leave as numbers. For example, your AcYr, AcPr, AcNo. I would think that an account number really would be a string value vs number for accounting purposes.

I would suggest the following indexes on your tables

Table   Index
Actr    ( AcYr, AcPr, AcNo, R3 )
Actor   ( CustNo, CreDt )

The Actr table I have the filtering criteria first and the R3 last to help optimize the GROUP BY. The Actor table by the customer number, then the CreDt (Create date??), and is it really a string, or is it a date field? If so, the date criteria would be something like '2018-09-01' and '2019-04-30'

select TOP 10
        Actor.Nm,
        PreSum.Sales,
        PreSm.TB
    from
        ( select
                R3,
                SUM(CASE WHEN AcTr.AcNo <= '3999' 
                    THEN AcAm * -1 ELSE 0 END) AS Sales,
                SUM( AcAm * -1) AS TB
            from
                Actr
            where
                    AcTr.AcYr = 2018
                AND AcTr.AcPr <= 8
                AND AcTr.AcNo >= '3000' 
                AND AcTr.AcNo <= '4999'
            GROUP BY 
                AcTr.R3 ) PreSum
            JOIN Actor
                on PreSum.R3 = Actor.CustNo
                AND Actor.CustNo <> 0
                AND Actor.CreDt >= '20180901'
                AND Actor.CreDt <= '20190430'
    order by
        Sales DESC

Per latest inquiry / comment, wanting by year comparison and getting rid of the top 10 performers per a given time period.

select 
        Actor.Nm,
        PreSum.Sales2018,
        PreSum.Sales2019,
        PreSum.TB2018,
        PreSum.TB2019
    from
        ( select
            AcTr.R3,
            SUM(CASE WHEN AcTr.AcYr = 2018 
                        AND AcTr.AcNo <= '3999'
                    THEN AcAm * -1 ELSE 0 END) AS Sales2018,
            SUM(CASE WHEN AcTr.AcYr = 2019 AND AcTr.AcNo <= '3999' 
                    THEN AcAm * -1 ELSE 0 END) AS Sales2019,
            SUM( CASE WHEN AcTr.AcYr = 2018 
                    THEN AcAm * -1 else 0 end ) AS TB2018
            SUM( CASE WHEN AcTr.AcYr = 2019 
                    THEN AcAm * -1 else 0 end ) AS TB2019
            from
                Actr
            where
                    AcTr.AcYr IN ( 2018, 2019 )
                AND AcTr.AcPr <= 8
                AND AcTr.AcNo >= '3000' 
                AND AcTr.AcNo <= '4999'
            GROUP BY 
                AcTr.R3 ) PreSum
            JOIN Actor
                on PreSum.R3 = Actor.CustNo
                AND Actor.CustNo <> 0
                AND Actor.CreDt >= '20180901'
                AND Actor.CreDt <= '20190430'
    order by
        Sales DESC

Upvotes: 1

Related Questions