Slow sort in query

Question

So I have this query

SELECT
    dbo.TQMNCR.NCRID,
    dbo.TQMPlantTable.PlantName AS 'Division',
    RTRIM(LTRIM(dbo.INVENTTABLE.ITEMGROUPID)) AS 'Item Process/Group',
    ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) AS 'Defective Qty',
    CASE CATYPE
        WHEN 0 THEN 
            (CASE WHEN dbo.SALESLINE.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTEXT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0)
                ELSE ISNULL((SALESPRICE * (PERCENTEXT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) END)
        WHEN 2 THEN 
            (CASE WHEN dbo.TQMNCR.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTINT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0)
                ELSE ISNULL((SALESPRICE * (PERCENTINT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) END)
        ELSE 0 END AS 'Total Defective $',
    dbo.PRODTABLE.PRODPOOLID
    ,SCHED.qty,
    SUM(salesDollars.sales$) as 'sales Dollars',
    fc.YearMonth,
    HG.HighLevelItemGroupingCodeName
FROM
    dbo.TQMNCR 
    LEFT OUTER JOIN dbo.TQMDISPOSITION ON dbo.TQMNCR.DISPOSITIONID = dbo.TQMDISPOSITION.DISPOSITIONID 
    LEFT OUTER JOIN dbo.TQMCA_TABLE ON dbo.TQMCA_TABLE.NCRID = dbo.TQMNCR.NCRID 
    LEFT OUTER JOIN dbo.TQMNCRDEFECTTYPECODES ON dbo.TQMNCR.NCRID = dbo.TQMNCRDEFECTTYPECODES.NCRID
    LEFT OUTER JOIN dbo.TQMPlantTable ON TQMPlantTable.PlantID  = dbo.TQMNCR.PlantID
    LEFT OUTER JOIN dbo.INVENTTABLE ON dbo.TQMNCR.ITEMID = dbo.INVENTTABLE.ITEMID 
    LEFT OUTER JOIN dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID AND MODULETYPE = 2 
    LEFT OUTER JOIN dbo.SALESLINE ON dbo.SALESLINE.SALESID = dbo.TQMNCR.SALESID AND dbo.SALESLINE.ITEMID = dbo.TQMNCR.ITEMID 
    LEFT OUTER JOIN dbo.PRODTABLE ON dbo.TQMNCR.PRODID = dbo.PRODTABLE.PRODID
    inner join sched on sched.itemGroup = INVENTTABLE.itemgroupid
    inner join salesQty on salesQty.itemGroup = INVENTTABLE.itemgroupid
    inner join salesDollars on  salesDollars.itemgroup = INVENTTABLE.itemgroupid
    LEFT JOIN [MiscReportTables].[dbo].[FiscalCalendar] fc on SCHEDDATE between fc.StartDate and fc.EndDate
    left JOIN INVENTITEMGROUP IG on dbo.INVENTTABLE.ITEMGROUPID = IG.ITEMGROUPID
    LEFT JOIN Pmf_HighLevelItemGrouping HG on IG.HIGHLEVELITEM = HG.HighLevelItemGroupingCode
WHERE
    SCHEDDATE between @start1 and @end1
    AND
    dbo.TQMNCR.PlantID IN (SELECT [PLANTID]
                            FROM [Dynamics].[dbo].[TQMPLANTTABLE])
Group By TQMNCR.NCRID,TQMPlantTable.PLANTNAME,INVENTTABLE.ITEMGROUPID,TQMNCRDEFECTTYPECODES.QTY,TQMNCR.CATYPE,SALESLINE.SALESID,INVENTTABLEMODULE.PRICE,
TQMDISPOSITION.PERCENTEXT,INVENTTABLEMODULE.PRICEUNIT,INVENTTABLEMODULE.PRICEUNIT,SALESLINE.SALESPRICE,SALESLINE.PRICEUNIT,TQMNCR.SALESID,TQMDISPOSITION.PERCENTINT,PRODTABLE.PRODPOOLID,
sched.qty,salesQty.salesQTY,fc.YearMonth,HG.HighLevelItemGroupingCodeName`

It runs slow and when I look at the query analyzer I see this

So it appears that the sort (needed because of the SUM and corresponding group by ) is causing this query to run slow. Is there anyway I can speed this up? The group by is pretty large, would I need to add indexes on all these tables?

edit: Here is the link to the plan: Explain Plan

Answer 1

You can try to avoid that HUGE GROUP BY on billions of rows, with a subquery (or with a CTE)

Try something like that:

SELECT
    dbo.TQMNCR.NCRID,
    dbo.TQMPlantTable.PlantName AS 'Division',
    RTRIM(LTRIM(dbo.INVENTTABLE.ITEMGROUPID)) AS 'Item Process/Group',
    ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) AS 'Defective Qty',
    CASE CATYPE
        WHEN 0 THEN 
            (CASE WHEN dbo.SALESLINE.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTEXT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0)
                ELSE ISNULL((SALESPRICE * (PERCENTEXT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) END)
        WHEN 2 THEN 
            (CASE WHEN dbo.TQMNCR.SALESID = ''
                THEN ISNULL((PRICE * (PERCENTINT / 100))  / NULLIF(dbo.INVENTTABLEMODULE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0)
                ELSE ISNULL((SALESPRICE * (PERCENTINT / 100))  / NULLIF(dbo.SALESLINE.PRICEUNIT, 0), 0) * ISNULL(dbo.TQMNCRDEFECTTYPECODES.QTY,0) END)
        ELSE 0 END AS 'Total Defective $',
    dbo.PRODTABLE.PRODPOOLID,
    SCHED.qty,
    salesDollars.[sales Dollars],
    fc.YearMonth,
    HG.HighLevelItemGroupingCodeName
FROM
    dbo.TQMNCR 
    LEFT OUTER JOIN dbo.TQMDISPOSITION ON dbo.TQMNCR.DISPOSITIONID = dbo.TQMDISPOSITION.DISPOSITIONID 
    LEFT OUTER JOIN dbo.TQMCA_TABLE ON dbo.TQMCA_TABLE.NCRID = dbo.TQMNCR.NCRID 
    LEFT OUTER JOIN dbo.TQMNCRDEFECTTYPECODES ON dbo.TQMNCR.NCRID = dbo.TQMNCRDEFECTTYPECODES.NCRID
    LEFT OUTER JOIN dbo.TQMPlantTable ON TQMPlantTable.PlantID  = dbo.TQMNCR.PlantID
    LEFT OUTER JOIN dbo.INVENTTABLE ON dbo.TQMNCR.ITEMID = dbo.INVENTTABLE.ITEMID 
    LEFT OUTER JOIN dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID AND MODULETYPE = 2 
    LEFT OUTER JOIN dbo.SALESLINE ON dbo.SALESLINE.SALESID = dbo.TQMNCR.SALESID AND dbo.SALESLINE.ITEMID = dbo.TQMNCR.ITEMID 
    LEFT OUTER JOIN dbo.PRODTABLE ON dbo.TQMNCR.PRODID = dbo.PRODTABLE.PRODID
    inner join sched on sched.itemGroup = INVENTTABLE.itemgroupid
    inner join salesQty on salesQty.itemGroup = INVENTTABLE.itemgroupid
    inner join (
        select itemgroup, SUM(sales$) as 'sales Dollars'
        from salesDollars
    ) salesDollars on  salesDollars.itemgroup = INVENTTABLE.itemgroupid
    LEFT JOIN [MiscReportTables].[dbo].[FiscalCalendar] fc on SCHEDDATE between fc.StartDate and fc.EndDate
    left JOIN INVENTITEMGROUP IG on dbo.INVENTTABLE.ITEMGROUPID = IG.ITEMGROUPID
    LEFT JOIN Pmf_HighLevelItemGrouping HG on IG.HIGHLEVELITEM = HG.HighLevelItemGroupingCode
WHERE
    SCHEDDATE between @start1 and @end1
    AND dbo.TQMNCR.PlantID IN (
        SELECT [PLANTID]
        FROM [Dynamics].[dbo].[TQMPLANTTABLE]
    )