All geek questions in one place

Large aggregate query optimization

I have the following tables [here is 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 collect recording costs in TmpB using TmpA. For this, I use the following query [which works fine for regular 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, the TmpB table is a massive 26M record, and the TmpA is 140,000. This is unusual for us, but there is nothing we can do about it. Due to the aggregation and use of joins in unique columns, the above request causes tempdb to inflate to the point where the request stops with [entry from the application log file]:

2017-09-26 06: 11: 07.027 ERROR:: r.d(): "tempdb" - "ACTIVE_TRANSACTION".     System.Data.SqlClient.SqlConnection.OnError( SqlException, breakConnection, `1 wrapCloseInAction)    ...

/ , - , , ?

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

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

# 1:

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

, , .

# 2:

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

:

Msg 4512, 16, 3, vB, 4 [ 360] "vB", "ServDataCtp_Stage1" . , .

?

+4
1

ResCstId . GROUP BY . . , .

resCstId, EpiSer, CTPActivityGroupCode, VolAmt, CTPQty, .

ResCstId, ; . count_big ( SQL Server). . : https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views.

----- UPDATE -----

, , . side-note: DDL .

, . DDL, : enter image description here . , : enter image description here

, , ( DDL, ). , "someId" . , , , , .

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

:

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

, . , , , , :

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];

. . , , , .

enter image description here

, .

+2

Source: https://habr.com/ru/post/1686418/

More articles:


All Articles