Optimisation of an Aggregate Query on Large Data Set

Question

I have the following tables [here's the SQL Fiddle]

CREATE TABLE TmpB
    ([EpiSer] int, [SINum] int, [VolNum] int, [CTPQty] int, [VolAmt] int, [CTPActivityGroupCode] varchar(6))
;

INSERT INTO TmpB
    ([EpiSer], [SINum], [VolNum], [CTPQty], [VolAmt], [CTPActivityGroupCode])
VALUES
    (104009, 21, 1, 1, 1, 'SUP001'),
    (42698, 21, 1, 1, 1, 'SUP001'),
    (82878, 21, 1, 1, 1, 'SUP001'),
    (94081, 21, 1, 1, 1, 'SUP001'),
    (80234, 21, 1, 1, 1, 'SUP001'),
    (81856, 21, 1, 1, 1, 'SUP001'),
    (19752, 21, 1, 1, 1, 'SUP001'),
    (17830, 21, 1, 1, 1, 'SUP001'),
    (73218, 21, 1, 1, 1, 'SUP001'),
    (32713, 21, 1, 1, 1, 'SUP001'),
    (90162, 21, 1, 1, 1, 'SUP001'),
    (59414, 21, 1, 1, 1, 'SUP001'),
    (63765, 21, 1, 1, 1, 'SUP001'),
    (26554, 21, 1, 1, 1, 'SUP001'),
    (72489, 21, 1, 1, 1, 'SUP001'),
    (94324, 21, 1, 1, 1, 'SUP001'),
    (34453, 21, 1, 1, 1, 'SUP001'),
    (101487, 21, 1, 1, 1, 'SUP001'),
    (21117, 21, 1, 1, 1, 'SUP001'),
    (4169, 21, 1, 1, 1, 'SUP001'),
    (27258, 21, 1, 1, 1, 'SUP001'),
    (85282, 21, 1, 1, 1, 'SUP001'),
    (98243, 21, 1, 1, 1, 'SUP001'),
    (98436, 21, 1, 1, 1, 'SUP001'),
    (15969, 21, 1, 1, 1, 'SUP001'),
    (70324, 21, 1, 1, 1, 'SUP001'),
    (9828, 21, 1, 1, 1, 'SUP001'),
    (61929, 21, 1, 1, 1, 'SUP001'),
    (3508, 21, 1, 1, 1, 'SUP001'),
    (65344, 21, 1, 1, 1, 'SUP001'),
    (19377, 21, 1, 1, 1, 'SUP001'),
    (104502, 21, 1, 1, 1, 'SUP001'),
    (57413, 21, 1, 1, 1, 'SUP001'),
    (66841, 21, 1, 1, 1, 'SUP001'),
    (39590, 21, 1, 1, 1, 'SUP001'),
    (50640, 21, 1, 1, 1, 'SUP001'),
    (66934, 21, 1, 1, 1, 'SUP001'),
    (36675, 21, 1, 1, 1, 'SUP001'),
    (65101, 21, 1, 1, 1, 'SUP001'),
    (84464, 21, 1, 1, 1, 'SUP001'),
    (74118, 21, 1, 1, 1, 'SUP001'),
    (40101, 21, 1, 1, 1, 'SUP001'),
    (18816, 21, 1, 1, 1, 'SUP001'),
    (61936, 21, 1, 1, 1, 'SUP001'),
    (23153, 21, 1, 1, 1, 'SUP001'),
    (50947, 21, 1, 1, 1, 'SUP001'),
    (39776, 21, 1, 1, 1, 'SUP001'),
    (31377, 21, 1, 1, 1, 'SUP001'),
    (106624, 21, 1, 1, 1, 'SUP001'),
    (41255, 21, 2, 1, 1, 'SUP001'),
    (82878, 21, 2, 1, 1, 'SUP001'),
    (94081, 21, 2, 1, 1, 'SUP001'),
    (80234, 21, 2, 1, 1, 'SUP001'),
    (81856, 21, 2, 1, 1, 'SUP001'),
    (19752, 21, 2, 1, 1, 'SUP001'),
    (17830, 21, 2, 1, 1, 'SUP001'),
    (73218, 21, 2, 1, 1, 'SUP001'),
    (32713, 21, 2, 1, 1, 'SUP001'),
    (90162, 21, 2, 1, 1, 'SUP001'),
    (59414, 21, 2, 1, 1, 'SUP001'),
    (63765, 21, 2, 1, 1, 'SUP001'),
    (26554, 21, 2, 1, 1, 'SUP001'),
    (72489, 21, 2, 1, 1, 'SUP001'),
    (94324, 21, 2, 1, 1, 'SUP001'),
    (34453, 21, 2, 1, 1, 'SUP001')
;


CREATE TABLE TmpA
    ([VolNum] int, [CostItem] varchar(15), [OCostUnit] numeric, [FCostUnit] numeric, [ResCstID] varchar(6))
;

INSERT INTO TmpA
    ([VolNum], [CostItem], [OCostUnit], [FCostUnit], [ResCstID])
VALUES
    (1, 'AgencyOtherOH_V', 0.000155982435905091, 0.000155982435905091, 'CSC004'),
    (1, 'CNST_F', 0.0166158829887076, 0.0166158829887076, 'CSC001'),
    (1, 'Depreciation_F', 0.00044046833444276, 0.00338301131105729, 'CSC004'),
    (1, 'GSOH_F', 0.002285123323198, 0.002285123323198, 'CSC004'),
    (1, 'GSOH_S', 0.00291555947552205, 0.00291555947552205, 'CSC004'),
    (1, 'GSOH_V', -7.85518770596873E-05, -7.85518770596873E-05, 'CSC004'),
    (1, 'GSPayrollOH_S', 1.95806910092635E-05, 1.95806910092635E-05, 'CSC004'),
    (1, 'INC001ET005', 0.000124265914489854, 0.000124265914489854, 'REC001'),
    (1, 'PDC_F', 0.000897633427026484, 0.000897633427026484, 'CSC004'),
    (1, 'R&D_F', 0, 0.0911004017527717, 'REC002'),
    (1, 'R&D_S', 0, 0.184788054915322, 'REC002'),
    (1, 'R&D_V', 0, -1.04534066995235, 'REC002'),
    (1, 'R&DSW_S', 0, 1.7173336734415, 'REC002'),
    (1, 'R&DSW_V', 0, 0.159935865317166, 'REC002'),
    (1, 'SWOtherOH_F', 0.000101979332044079, 0.000101979332044079, 'CSC004'),
    (1, 'SWOtherOH_S', 0.00997174733301197, 0.00997174733301197, 'CSC004'),
    (1, 'SWOtherOH_V', 0.000224400291272709, 0.000224400291272709, 'CSC004'),
    (2, 'AgencyOtherOH_V', 0.000155982435905091, 0.000155982435905091, 'CSC004'),
    (2, 'CNST_F', 0.0166158829887076, 0.0166158829887076, 'CSC001'),
    (2, 'Depreciation_F', 0.00044046833444276, 0.00338301131105729, 'CSC004'),
    (2, 'GSOH_F', 0.002285123323198, 0.002285123323198, 'CSC004'),
    (2, 'GSOH_S', 0.00291555947552205, 0.00291555947552205, 'CSC004'),
    (2, 'GSOH_V', -7.85518770596873E-05, -7.85518770596873E-05, 'CSC004'),
    (2, 'GSPayrollOH_S', 1.95806910092635E-05, 1.95806910092635E-05, 'CSC004'),
    (2, 'INC001ET005', 0.000124265914489854, 0.000124265914489854, 'REC001'),
    (2, 'PDC_F', 0.000897633427026484, 0.000897633427026484, 'CSC004'),
    (2, 'R&D_F', 0, 0.0911004017527717, 'REC002'),
    (2, 'R&D_S', 0, 0.184788054915322, 'REC002'),
    (2, 'R&D_V', 0, -1.04534066995235, 'REC002'),
    (2, 'R&DSW_S', 0, 1.7173336734415, 'REC002'),
    (2, 'R&DSW_V', 0, 0.159935865317166, 'REC002'),
    (2, 'SWOtherOH_F', 0.000101979332044079, 0.000101979332044079, 'CSC004'),
    (2, 'SWOtherOH_S', 0.00997174733301197, 0.00997174733301197, 'CSC004'),
    (2, 'SWOtherOH_V', 0.000224400291272709, 0.000224400291272709, 'CSC004')
;

I need to aggregate costs for records in TmpB using TmpA. To do this I use the following query [which has been working fine for "normal" sized tables]

SELECT [s1].[EpiSer] as ActivityRecordID, 
       [s1].[CTPActivityGroupCode] as ActCstID, 
       [t].[ResCstID], 
       [s1].[VolAmt], 
       [s1].[CTPQty] AS ActCnt, 
       SUM([s1].[VolAmt] * [t].[OCostUnit]) AS TotOCst, 
       SUM([s1].[VolAmt] * [t].[FCostUnit]) AS TotFCst  
FROM [TmpB] AS s1 
    INNER JOIN 
        TmpA AS t ON [s1].[VolNum] = [t].[VolNum] 
GROUP BY [s1].[EpiSer], 
         [s1].[CTPActivityGroupCode], 
         [t].[ResCstID], 
         [s1].[VolAmt], 
         [s1].[CTPQty];

Now, the problem is that for our current case, table TmpB is a massive 26M records and TmpA is 140,000. This is unusual for us, but there is nothing we can do about it. The above query, due to the aggregation and the use of joins on non-unique columns, is causing tempdb to bloat to a point where the query stops with [entry from application log file]:

2017-09-26 06:11:07.027 ERROR:: r.d(): The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ...

How can we amend/optimize the query to avoid this problem - I have tried indexing, but is this all I can do?

CREATE NONCLUSTERED INDEX [IX_TmpB] 
ON [TmpB] ([EpiSer], [VolNum]);  

CREATE NONCLUSTERED INDEX [IX_TmpA] 
ON [TmpA] ([VolNum]);

Attempt to Resolve #1:

I have trie using the index

CREATE NONCLUSTERED INDEX [IX_ServDataCtp_Stage1] 
ON [ServDataCtp_Stage1] ([VolNum])
INCLUDE ([EpiSer], [CTPQty], [VolAmt], [CTPActivityGroupCode]);
GO

I need to undertake a full attempt at execution, to see if this remedies the problem.

Attempt to Resolve #2:

I have then tried to use Alan's suggestion of INDEXED VIEWS

SET NUMERIC_ROUNDABORT OFF;  
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, 
     ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;  
GO  

CREATE VIEW vB   
WITH SCHEMABINDING  
AS  
    SELECT [ServDataCtp_Stage1].[EpiSer] as ActivityRecordID, 
           [ServDataCtp_Stage1].[CTPActivityGroupCode] as ActCstID, 
           [TmpA].[ResCstID], 
           [ServDataCtp_Stage1].[VolAmt], 
           [ServDataCtp_Stage1].[CTPQty] AS ActCnt, 
           SUM([ServDataCtp_Stage1].[VolAmt] * [TmpA].[OCostUnit]) AS TotOCst, 
           SUM([ServDataCtp_Stage1].[VolAmt] * [TmpA].[FCostUnit]) AS TotFCst, 
           COUNT_BIG(*) AS _COUNT 
    FROM [ServDataCtp_Stage1] INNER JOIN 
            [TmpA] ON [ServDataCtp_Stage1].[VolNum] = [TmpA].[VolNum] 
    GROUP BY [ServDataCtp_Stage1].[EpiSer], 
             [ServDataCtp_Stage1].[CTPActivityGroupCode], 
             [TmpA].[ResCstID], 
             [ServDataCtp_Stage1].[VolAmt], 
             [ServDataCtp_Stage1].[CTPQty];
GO

But the second query give the following error:

Msg 4512, Level 16, State 3, Procedure vB, Line 4 [Batch Start Line 360] Cannot schema bind view 'vB' because name 'ServDataCtp_Stage1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Can you help resolve this?

user_2647342 · Answer

That ResCstId is making things a little more complicated. GROUP BY causes a sort. Grouping on colums from more than one table makes it harder to benefit from any index. I bet theres one or more really big sort in your execution plan.

If you did not need that resCstId you could throw a nonclustered index on EpiSer, CTPActivityGroupCode,VolAmt, CTPQty which would speed things up dramatically.

If ResCstId is needed then you could turn your query into an indexed view; they are designed for this kind of thing. You will need to include a count_big column (its a weird SQL Server indexed view thing). See this link for more details: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views.

----- UPDATE -----

Creating an indexed view was a little trickier than I fist thought but I did it. side-note: This is why posting DDL and sample data is so helpful.

First let's look at the problem we're trying to solve. Using the DDL provided, the query creates this execution plan: That sort is the culprit. If you hover your mouse over it you get more insight:

The problem with creating an indexed view is that we need a unique set of columns which you don't have (based on the DDL you posted). To get around this I added a surrogate key column to each table and named it "someId" in each table. This might be a deal breaker but, at least what I'm demonstrating here is how this solution would work.

Here's the updated DDL:

if object_id('dbo.tmpA') is not null drop table tmpA;
if object_id('dbo.tmpB') is not null drop table tmpB;

CREATE TABLE TmpB
    (someid int identity, [EpiSer] int, [SINum] int, [VolNum] int, [CTPQty] int, [VolAmt] int, [CTPActivityGroupCode] varchar(6))
;
INSERT INTO TmpB
    ([EpiSer], [SINum], [VolNum], [CTPQty], [VolAmt], [CTPActivityGroupCode])
VALUES
    (104009, 21, 1, 1, 1, 'SUP001'),
    (42698, 21, 1, 1, 1, 'SUP001'),
    (82878, 21, 1, 1, 1, 'SUP001'),
    (94081, 21, 1, 1, 1, 'SUP001'),
    (80234, 21, 1, 1, 1, 'SUP001'),
    (81856, 21, 1, 1, 1, 'SUP001'),
    (19752, 21, 1, 1, 1, 'SUP001'),
    (17830, 21, 1, 1, 1, 'SUP001'),
    (73218, 21, 1, 1, 1, 'SUP001'),
    (32713, 21, 1, 1, 1, 'SUP001'),
    (90162, 21, 1, 1, 1, 'SUP001'),
    (59414, 21, 1, 1, 1, 'SUP001'),
    (63765, 21, 1, 1, 1, 'SUP001'),
    (26554, 21, 1, 1, 1, 'SUP001'),
    (72489, 21, 1, 1, 1, 'SUP001'),
    (94324, 21, 1, 1, 1, 'SUP001'),
    (34453, 21, 1, 1, 1, 'SUP001'),
    (101487, 21, 1, 1, 1, 'SUP001'),
    (21117, 21, 1, 1, 1, 'SUP001'),
    (4169, 21, 1, 1, 1, 'SUP001'),
    (27258, 21, 1, 1, 1, 'SUP001'),
    (85282, 21, 1, 1, 1, 'SUP001'),
    (98243, 21, 1, 1, 1, 'SUP001'),
    (98436, 21, 1, 1, 1, 'SUP001'),
    (15969, 21, 1, 1, 1, 'SUP001'),
    (70324, 21, 1, 1, 1, 'SUP001'),
    (9828, 21, 1, 1, 1, 'SUP001'),
    (61929, 21, 1, 1, 1, 'SUP001'),
    (3508, 21, 1, 1, 1, 'SUP001'),
    (65344, 21, 1, 1, 1, 'SUP001'),
    (19377, 21, 1, 1, 1, 'SUP001'),
    (104502, 21, 1, 1, 1, 'SUP001'),
    (57413, 21, 1, 1, 1, 'SUP001'),
    (66841, 21, 1, 1, 1, 'SUP001'),
    (39590, 21, 1, 1, 1, 'SUP001'),
    (50640, 21, 1, 1, 1, 'SUP001'),
    (66934, 21, 1, 1, 1, 'SUP001'),
    (36675, 21, 1, 1, 1, 'SUP001'),
    (65101, 21, 1, 1, 1, 'SUP001'),
    (84464, 21, 1, 1, 1, 'SUP001'),
    (74118, 21, 1, 1, 1, 'SUP001'),
    (40101, 21, 1, 1, 1, 'SUP001'),
    (18816, 21, 1, 1, 1, 'SUP001'),
    (61936, 21, 1, 1, 1, 'SUP001'),
    (23153, 21, 1, 1, 1, 'SUP001'),
    (50947, 21, 1, 1, 1, 'SUP001'),
    (39776, 21, 1, 1, 1, 'SUP001'),
    (31377, 21, 1, 1, 1, 'SUP001'),
    (106624, 21, 1, 1, 1, 'SUP001'),
    (41255, 21, 2, 1, 1, 'SUP001'),
    (82878, 21, 2, 1, 1, 'SUP001'),
    (94081, 21, 2, 1, 1, 'SUP001'),
    (80234, 21, 2, 1, 1, 'SUP001'),
    (81856, 21, 2, 1, 1, 'SUP001'),
    (19752, 21, 2, 1, 1, 'SUP001'),
    (17830, 21, 2, 1, 1, 'SUP001'),
    (73218, 21, 2, 1, 1, 'SUP001'),
    (32713, 21, 2, 1, 1, 'SUP001'),
    (90162, 21, 2, 1, 1, 'SUP001'),
    (59414, 21, 2, 1, 1, 'SUP001'),
    (63765, 21, 2, 1, 1, 'SUP001'),
    (26554, 21, 2, 1, 1, 'SUP001'),
    (72489, 21, 2, 1, 1, 'SUP001'),
    (94324, 21, 2, 1, 1, 'SUP001'),
    (34453, 21, 2, 1, 1, 'SUP001')
;
CREATE TABLE TmpA
    (someid int identity, [VolNum] int, [CostItem] varchar(15), [OCostUnit] numeric, [FCostUnit] numeric, [ResCstID] varchar(6))
;
INSERT INTO TmpA
    ([VolNum], [CostItem], [OCostUnit], [FCostUnit], [ResCstID])
VALUES
    (1, 'AgencyOtherOH_V', 0.000155982435905091, 0.000155982435905091, 'CSC004'),
    (1, 'CNST_F', 0.0166158829887076, 0.0166158829887076, 'CSC001'),
    (1, 'Depreciation_F', 0.00044046833444276, 0.00338301131105729, 'CSC004'),
    (1, 'GSOH_F', 0.002285123323198, 0.002285123323198, 'CSC004'),
    (1, 'GSOH_S', 0.00291555947552205, 0.00291555947552205, 'CSC004'),
    (1, 'GSOH_V', -7.85518770596873E-05, -7.85518770596873E-05, 'CSC004'),
    (1, 'GSPayrollOH_S', 1.95806910092635E-05, 1.95806910092635E-05, 'CSC004'),
    (1, 'INC001ET005', 0.000124265914489854, 0.000124265914489854, 'REC001'),
    (1, 'PDC_F', 0.000897633427026484, 0.000897633427026484, 'CSC004'),
    (1, 'R&D_F', 0, 0.0911004017527717, 'REC002'),
    (1, 'R&D_S', 0, 0.184788054915322, 'REC002'),
    (1, 'R&D_V', 0, -1.04534066995235, 'REC002'),
    (1, 'R&DSW_S', 0, 1.7173336734415, 'REC002'),
    (1, 'R&DSW_V', 0, 0.159935865317166, 'REC002'),
    (1, 'SWOtherOH_F', 0.000101979332044079, 0.000101979332044079, 'CSC004'),
    (1, 'SWOtherOH_S', 0.00997174733301197, 0.00997174733301197, 'CSC004'),
    (1, 'SWOtherOH_V', 0.000224400291272709, 0.000224400291272709, 'CSC004'),
    (2, 'AgencyOtherOH_V', 0.000155982435905091, 0.000155982435905091, 'CSC004'),
    (2, 'CNST_F', 0.0166158829887076, 0.0166158829887076, 'CSC001'),
    (2, 'Depreciation_F', 0.00044046833444276, 0.00338301131105729, 'CSC004'),
    (2, 'GSOH_F', 0.002285123323198, 0.002285123323198, 'CSC004'),
    (2, 'GSOH_S', 0.00291555947552205, 0.00291555947552205, 'CSC004'),
    (2, 'GSOH_V', -7.85518770596873E-05, -7.85518770596873E-05, 'CSC004'),
    (2, 'GSPayrollOH_S', 1.95806910092635E-05, 1.95806910092635E-05, 'CSC004'),
    (2, 'INC001ET005', 0.000124265914489854, 0.000124265914489854, 'REC001'),
    (2, 'PDC_F', 0.000897633427026484, 0.000897633427026484, 'CSC004'),
    (2, 'R&D_F', 0, 0.0911004017527717, 'REC002'),
    (2, 'R&D_S', 0, 0.184788054915322, 'REC002'),
    (2, 'R&D_V', 0, -1.04534066995235, 'REC002'),
    (2, 'R&DSW_S', 0, 1.7173336734415, 'REC002'),
    (2, 'R&DSW_V', 0, 0.159935865317166, 'REC002'),
    (2, 'SWOtherOH_F', 0.000101979332044079, 0.000101979332044079, 'CSC004'),
    (2, 'SWOtherOH_S', 0.00997174733301197, 0.00997174733301197, 'CSC004'),
    (2, 'SWOtherOH_V', 0.000224400291272709, 0.000224400291272709, 'CSC004')
;
GO

Next the view and index:

CREATE VIEW dbo.vwTmpAB
WITH SCHEMABINDING AS
SELECT id1 = s1.someid,
       id2 = t.someid,
       s1.[EpiSer] as ActivityRecordID, 
       s1.[CTPActivityGroupCode] as ActCstID, 
       t.[ResCstID], 
       s1.[VolAmt],
       s1.[CTPQty] AS ActCnt,
       t.OCostUnit,
       t.FCostUnit
FROM dbo.TmpB AS s1
INNER JOIN dbo.TmpA AS t ON [s1].[VolNum] = [t].[VolNum];
GO
CREATE UNIQUE CLUSTERED INDEX uq_cl_vwTempAB 
  ON dbo.vwTmpAB(ActivityRecordID, ActCstID, ResCstID, VolAmt, ActCnt, id1, id2);
GO

Now we do the aggregation against the view as shown below. The real magic here is that, when I run your original query, the optimizer is smart enough to use the index on the view even though it's never referenced:

SELECT
  ActivityRecordID, 
  ActCstID, 
  ResCstID, 
  VolAmt, 
  ActCnt,
  SUM(VolAmt * OCostUnit) AS TotOCst, 
  SUM(VolAmt * FCostUnit) AS TotFCst  
FROM dbo.vwTmpAB
GROUP BY ActivityRecordID, ActCstID, ResCstID, VolAmt, ActCnt;

SELECT [s1].[EpiSer] as ActivityRecordID, 
       [s1].[CTPActivityGroupCode] as ActCstID, 
       [t].[ResCstID], 
       [s1].[VolAmt], 
       [s1].[CTPQty] AS ActCnt, 
       SUM([s1].[VolAmt] * [t].[OCostUnit]) AS TotOCst, 
       SUM([s1].[VolAmt] * [t].[FCostUnit]) AS TotFCst  
FROM [TmpB] AS s1 
    INNER JOIN 
        TmpA AS t ON [s1].[VolNum] = [t].[VolNum] 
GROUP BY [s1].[EpiSer], 
         [s1].[CTPActivityGroupCode], 
         [t].[ResCstID], 
         [s1].[VolAmt], 
         [s1].[CTPQty];

Both queries return the same result and do so with a much better query plan. The Sort is gone. I included your original query to demonstrate how, in some cases, you don't even need to reference the view to make take advantage of the index.

Let me know if this helps.

Optimisation of an Aggregate Query on Large Data Set

1 Answers