T-SQL流程设计和执行计划(UDF参数嗅探?)
在 SQL Server 2005 上,我有一个复杂的多级分配过程,如下所示(伪 SQL):
FOR EACH @LVL_NUM < @MAX_LVL:
INSERT INTO ALLOCS
SELECT 'OUT', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
INSERT INTO ALLOCS
SELECT 'IN', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCNS(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
其中 ALLOCS
是直接分配的种子,然后是 BALANCES(@LVL_NUM)
code> 基于 @LVL_NUM
处的 ALLOCS
(可能是一些直接分配加上上一级别的一些 IN 分配)和 ALOCNS(@LVL_NUM)< /code> 基于
BALANCES(@LVL_NUM)
,而 ALOCN_SUMRY(@LVL_NUM)
仅基于 ALOCNS(@LVL_NUM)
- 具有许多配置表指示驱动分配的驱动程序。
这是简化的,但实际上循环中有四到五对这样的,因为有多种逻辑不可能一起处理(而有些情况可以一起处理)。
基本逻辑是取特定成本中心/产品线/等中的总金额(即BALANCES
),然后根据其份额将其分配给另一个成本中心/产品线/等(即ALLOCNS /特定指标的 ALLOCN_SUMRY
百分比份额)。
在 OUT
记录保存和 IN
中重复了如此多的逻辑,当然还有基于 ALLOCN
的 SUMRY
详细地说,我最终使用内联表值函数来实现,它似乎表现得相当好(并且它们与回归测试中现有系统的行为相匹配,这是一个优点!)。 (现有系统是一个巨大的 C/C++/MFC/ODBC 程序,它将所有数据读取到大量数组和其他数据结构中,并且编写得非常糟糕。)
问题似乎是,当在循环中运行时,我似乎得到了当我随着 ALLOCS
表开始变化而向上提升时,执行计划出现问题(一切都在变化,因为各个级别具有不同的成本中心,因此用于驱动 ALLOCNS
正在改变)。 我认为最多有 99 个级别,但最低级别从 2、4、6 开始。看来在 UDF 之外单独运行 @LVL_NUM = 6
表现良好,但 UDF 执行很差 - 可能是因为 UDF 有一个缓存计划,或者由于 @LVL_NUM IN (2, 4)
处的早期步骤添加了 ALLOCS
,因此整个计划已经很糟糕。
在开发早期,我设法在 30 分钟内运行 30 个关卡,但现在我无法让它在 2 小时内完成前 3 个关卡。
我正在考虑在另一个 SP 中运行这两个插入并将其称为“WITH RECOMPILE”,但很好奇此 RECOMPILE 是否正确级联到 TVF UDF 中? 任何其他建议也将不胜感激。
真实代码:
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_BALANCES_STAT_UNI] Script Date: 05/14/2009 22:16:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_BALANCES_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT AB.YYMM_ID
,AB.BUS_UNIT_ID
,AB.BUS_UNIT_PROD_LINE_CD
-- ,AB.ALOCN_SRC_CD
,AB.ALOCN_SRC_PROD_LINE_CD
,CASE WHEN ORIG_ALSRC.ALOCN_TYPE_CD = 'C'
AND ORIG_ALSRC.RETN_IND = 'Y' THEN AB.ORIG_ALOCN_SRC_CD
ELSE AB.BUS_UNIT_ID
END AS ORIG_ALOCN_SRC_CD
,CASE WHEN BUPALSRC.COLLAPSE_IND = 'Y'
THEN BUPLNTM.ALOCN_LINE_ITEM_NUM
ELSE AB.LINE_ITEM_NUM
END AS ALOCN_LINE_ITEM_NUM
,SUM(BUPLNTM.ALOCN_SIGN_IND * AB.ANULZD_ACTL_BAL) AS ANULZD_ACTL_BAL
FROM MISWork.vwMR_BALANCES AS AB
INNER JOIN MISProcess.LKP_BUPLNTM AS BUPLNTM
ON BUPLNTM.DATA_DT_ID = @DATA_DT_ID
AND BUPLNTM.LINE_ITEM_NUM = AB.LINE_ITEM_NUM
AND BUPLNTM.ALOCN_LINE_ITEM_NUM <> 0
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, @LVL_NUM) AS BUPALSRC
ON BUPALSRC.ALOCN_SRC_CD = AB.BUS_UNIT_ID
INNER JOIN [MISProcess].LKP_BUPALSRC AS ORIG_ALSRC
ON ORIG_ALSRC.DATA_DT_ID = @DATA_DT_ID
AND ORIG_ALSRC.ALOCN_SRC_CD = AB.ORIG_ALOCN_SRC_CD
GROUP BY AB.YYMM_ID
,AB.BUS_UNIT_ID
,AB.BUS_UNIT_PROD_LINE_CD
-- ,AB.ALOCN_SRC_CD
,AB.ALOCN_SRC_PROD_LINE_CD
,CASE WHEN ORIG_ALSRC.ALOCN_TYPE_CD = 'C'
AND ORIG_ALSRC.RETN_IND = 'Y' THEN AB.ORIG_ALOCN_SRC_CD
ELSE AB.BUS_UNIT_ID
END
,CASE WHEN BUPALSRC.COLLAPSE_IND = 'Y'
THEN BUPLNTM.ALOCN_LINE_ITEM_NUM
ELSE AB.LINE_ITEM_NUM
END
)
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_ALOCNS_STAT_UNI] Script Date: 05/14/2009 22:16:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_ALOCNS_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT BALANCES.YYMM_ID
,BS.ALOCN_SRC_CD AS BUS_UNIT_ID
,BS.PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,BALANCES.BUS_UNIT_ID AS ALOCN_SRC_CD
,BALANCES.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM
,SUM(BS.ACCT_STATS_CNT) AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_BALANCES_STAT_UNI](@DATA_DT_ID, @LVL_NUM) AS BALANCES
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, @LVL_NUM) AS BUPALSRC
ON BUPALSRC.ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
INNER JOIN MISProcess.LKP_PRODLINE AS PRODLINE
ON PRODLINE.DATA_DT_ID = @DATA_DT_ID
AND PRODLINE.PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
INNER JOIN PUASFIN.FocusResults.BS AS BS
ON BS.YYMM_ID = BALANCES.YYMM_ID
AND BS.ALOCN_BASE_CD = BUPALSRC.ALOCN_BASE_CD
AND BS.ALOCN_SRC_CD <> BALANCES.BUS_UNIT_ID
AND (
PRODLINE.GENRC_PROD_LINE_IND = 'Y'
OR BS.PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
)
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, 0) AS DEST_BUP_ALSRC
ON DEST_BUP_ALSRC.ALOCN_SRC_CD = BS.ALOCN_SRC_CD
AND DEST_BUP_ALSRC.ALOCN_LVL_NUM > @LVL_NUM
LEFT JOIN [MISProcess].[udf_MR_BLOCK_STD_COST_PCT](@DATA_DT_ID) AS BLOCK_STD_COST_PCT
ON BLOCK_STD_COST_PCT.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
LEFT JOIN [MISProcess].[udf_MR_BLOCK_NOT](@DATA_DT_ID) AS BLOCK_NOT
ON BLOCK_NOT.ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
LEFT JOIN [MISProcess].[udf_MR_BLOCK](@DATA_DT_ID) AS BLOCK
ON BLOCK_NOT.ALOCN_SRC_CD IS NULL
AND BLOCK.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
AND (
BLOCK.FROM_PROD_LINE_CD IS NULL
OR BLOCK.FROM_PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
)
LEFT JOIN [MISProcess].[udf_MR_BLOCK_ALOCN_PAIRS](@DATA_DT_ID, @LVL_NUM)
AS BLOCK_ALOCN_PAIRS
ON BLOCK_NOT.ALOCN_SRC_CD IS NOT NULL
AND BLOCK_ALOCN_PAIRS.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
AND BLOCK_ALOCN_PAIRS.TO_ALOCN_SRC_CD = BS.ALOCN_SRC_CD
WHERE BLOCK_ALOCN_PAIRS.TO_ALOCN_SRC_CD IS NULL
AND BLOCK_STD_COST_PCT.FROM_ALOCN_SRC_CD IS NULL
AND (
BLOCK.TO_ALOCN_SRC_CD IS NULL
OR BLOCK.TO_ALOCN_SRC_CD = BS.ALOCN_SRC_CD
)
AND (
BLOCK.TO_PROD_LINE_CD IS NULL
OR BLOCK.TO_PROD_LINE_CD = BS.PROD_LINE_CD
)
AND (
BLOCK.YEAR_NUM IS NULL
OR BLOCK.YEAR_NUM = BALANCES.YYMM_ID / 10000
)
AND (
BLOCK.MTH_NUM IS NULL
OR BLOCK.MTH_NUM = (BALANCES.YYMM_ID / 100) % 100
)
AND (
BLOCK.TO_DIV_NUM IS NULL
OR BLOCK.TO_DIV_NUM = DEST_BUP_ALSRC.DIV_NUM
)
AND (
BLOCK.TO_GRP_NUM IS NULL
OR BLOCK.TO_GRP_NUM = DEST_BUP_ALSRC.DIV_GRP
)
AND (
BLOCK.TO_REGN_GRP_NM IS NULL
OR BLOCK.TO_REGN_GRP_NM = DEST_BUP_ALSRC.REGN_GRP_NM
)
AND (
BLOCK.TO_REGN_NM IS NULL
OR BLOCK.TO_REGN_NM = DEST_BUP_ALSRC.REGN_NM
)
AND (
BLOCK.TO_ARENA_NM IS NULL
OR BLOCK.TO_ARENA_NM = DEST_BUP_ALSRC.ARENA_NM
)
AND (
BLOCK.TO_SUB_REGN_NM IS NULL
OR BLOCK.TO_SUB_REGN_NM = DEST_BUP_ALSRC.SUB_REGN_NM
)
AND (
BLOCK.TO_SUB_ARENA_NM IS NULL
OR BLOCK.TO_SUB_ARENA_NM = DEST_BUP_ALSRC.SUB_ARENA_NM
)
GROUP BY BALANCES.YYMM_ID
,BS.ALOCN_SRC_CD
,BS.PROD_LINE_CD
,BALANCES.BUS_UNIT_ID
,BALANCES.BUS_UNIT_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM
)
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_ALOCN_SUMRY_STAT_UNI] Script Date: 05/14/2009 22:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_ALOCN_SUMRY_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT YYMM_ID
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,ALOCN_LINE_ITEM_NUM
,SUM(ACCT_STATS_CNT) AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_ALOCNS_STAT_UNI](@DATA_DT_ID, @LVL_NUM) AS ALOCNS
GROUP BY YYMM_ID
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,ALOCN_LINE_ITEM_NUM
)
这是我的测试批次,最终将在单个 SP 中运行整个过程。 您可以从注释掉的部分看到我也一直在使用临时表和表变量:
USE PCAPFIN
DECLARE @DATA_DT_ID_use AS int
DECLARE @MinLevel AS int
DECLARE @MaxLevel AS int
DECLARE @TestEveryLevel AS bit
DECLARE @TestFinal AS bit
SET @DATA_DT_ID_use = 20090331
SET @MinLevel = 6
SET @MaxLevel = 6
SET @TestEveryLevel = 0
SET @TestFinal = 1
--DECLARE @BALANCES TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ANULZD_ACTL_BAL money
-- )
--
--DECLARE @ALOCNS TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- )
--
--DECLARE @ALOCN_SUMRY TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- )
--IF OBJECT_ID('tempdb..#BALANCES') IS NOT NULL
-- DROP TABLE #BALANCES
--
--CREATE TABLE #BALANCES (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ANULZD_ACTL_BAL money
-- ,CONSTRAINT [PK_BALANCES] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, [YYMM_ID] ASC, [BUS_UNIT_ID] ASC, [BUS_UNIT_PROD_LINE_CD] ASC, [ALOCN_SRC_PROD_LINE_CD] ASC, [ORIG_ALOCN_SRC_CD] ASC, [ALOCN_LINE_ITEM_NUM] ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
--
--IF OBJECT_ID('tempdb..#ALOCN_SUMRY') IS NOT NULL
-- DROP TABLE #ALOCNS
--
--CREATE TABLE #ALOCNS (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- ,CONSTRAINT [PK_ALOCNS] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, YYMM_ID ASC, BUS_UNIT_ID ASC, BUS_UNIT_PROD_LINE_CD ASC, ALOCN_SRC_CD ASC, ALOCN_SRC_PROD_LINE_CD ASC, ORIG_ALOCN_SRC_CD ASC, ALOCN_LINE_ITEM_NUM ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
--
--IF OBJECT_ID('tempdb..#ALOCN_SUMRY') IS NOT NULL
-- DROP TABLE #ALOCN_SUMRY
--CREATE TABLE #ALOCN_SUMRY (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- ,CONSTRAINT [PK_ALOCN_SUMRY] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, YYMM_ID ASC, ALOCN_SRC_CD ASC, ALOCN_SRC_PROD_LINE_CD ASC, ORIG_ALOCN_SRC_CD ASC, ALOCN_LINE_ITEM_NUM ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
SET @MinLevel = (
SELECT MIN(BUPALSRC.ALOCN_LVL_NUM)
FROM MISProcess.LKP_BUPALSRC AS BUPALSRC
WHERE BUPALSRC.DATA_DT_ID = @DATA_DT_ID_use
AND BUPALSRC.ALOCN_LVL_NUM >= @MinLevel
)
DECLARE @Restart AS bit
IF @MinLevel > (
SELECT MIN(BUPALSRC.ALOCN_LVL_NUM)
FROM MISProcess.LKP_BUPALSRC AS BUPALSRC
WHERE BUPALSRC.DATA_DT_ID = @DATA_DT_ID_use
)
SET @Restart = 0
ELSE
SET @Restart = 1
DECLARE @subset_criteria AS varchar(max)
SET NOCOUNT ON
IF @Restart = 1
BEGIN
RAISERROR ('Restarting process', 10, 1) WITH NOWAIT
-- TRUNCATE TABLE MISWork.AB
DELETE FROM MISWork.AB
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT YYMM_ID
,ALOCN_SRC_CD AS BUS_UNIT_ID
,PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCN_SRC_CD AS ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,'D' AS BAL_ORIGTN_IND
,FIN_ALOCN_AMT AS ANULZD_ACTL_BAL
,0.0 AS ACCT_STATS_CNT
,0 AS LVL_NUM
,'D-INIT' AS METHOD_TXT
-- FROM MISProcess.FIN_FTP
FROM PUASFIN.FocusResults.BUPALLGE
END
ELSE
BEGIN
DELETE FROM MISWork.AB
WHERE LVL_NUM >= @MinLevel
END
DECLARE @LVL_NUM AS int
SET @LVL_NUM = @MinLevel
WHILE @LVL_NUM <= @MaxLevel
BEGIN
DECLARE @LevelStart AS varchar(50)
SET @LevelStart = 'Level:' + CONVERT(varchar, @LVL_NUM)
RAISERROR (@LevelStart, 10, 1) WITH NOWAIT
RAISERROR ('STD_COST_PCT allocations - No D - B records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - No D - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.BUS_UNIT_ID AS ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.BUS_UNIT_ID AS ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * ROUND(ALOCNS.ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'NO-D-B' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT_NO_D](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
RAISERROR ('STD_COST_PCT allocations - No D - A records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - No D - A records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,BLOCK.TO_ALOCN_SRC_CD AS BUS_UNIT_ID
,ALOCNS.ALOCN_SRC_PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'A' AS BAL_ORIGTN_IND
,ROUND(ALOCNS.ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'NO-D-A' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT_NO_D](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN MISProcess.LKP_BLOCK AS BLOCK
-- TODO: Can this be moved into the udf above?
ON BLOCK.DATA_DT_ID = @DATA_DT_ID_use
AND BLOCK.FROM_ALOCN_SRC_CD = ALOCNS.BUS_UNIT_ID
RAISERROR ('STD_COST_PCT allocations - B records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * ROUND(ALOCNS.ANULZD_ACTL_BAL * RATIOS.RATIO, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'STD-B' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN [MISProcess].[udf_MR_RATIOS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS RATIOS
ON RATIOS.YYMM_ID = ALOCNS.YYMM_ID
AND RATIOS.BUS_UNIT_ID = ALOCNS.BUS_UNIT_ID
AND RATIOS.LINE_ITEM_NUM = ALOCNS.LINE_ITEM_NUM
RAISERROR ('STD_COST_PCT allocations - A records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - A records
;
WITH CORRECTED_ALOCNS
AS (
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,ALOCNS.ANULZD_ACTL_BAL * RATIOS.RATIO AS ANULZD_ACTL_BAL
,CASE WHEN RATIOS.RATIO <> 1.0
THEN RATIOS.RATIO
ELSE ALOCNS.ACCT_STATS_CNT
END AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_CORR_ALOCNS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN [MISProcess].[udf_MR_RATIOS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS RATIOS
ON RATIOS.YYMM_ID = ALOCNS.YYMM_ID
AND RATIOS.BUS_UNIT_ID = ALOCNS.ALOCN_SRC_CD
AND RATIOS.LINE_ITEM_NUM = ALOCNS.LINE_ITEM_NUM
),
ROUNDED_ALOCNS
AS (
SELECT YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,CASE WHEN ABS(ANULZD_ACTL_BAL) < 0.05 THEN 0.0
WHEN ABS(ANULZD_ACTL_BAL) > 0.05
AND ABS(ANULZD_ACTL_BAL) < 0.10
THEN 0.10 * SIGN(ANULZD_ACTL_BAL)
ELSE ANULZD_ACTL_BAL
END AS ANULZD_ACTL_BAL
,ACCT_STATS_CNT
FROM CORRECTED_ALOCNS
)
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,'A' AS BAL_ORIGTN_IND
,ROUND(ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'STD-A' AS METHOD_TXT
FROM ROUNDED_ALOCNS
WHERE ANULZD_ACTL_BAL <> 0.0
OR ACCT_STATS_CNT <> 0.0
RAISERROR ('COLLAPSE, BLOCK 100 ALOCN_PCT - B records', 10, 1) WITH NOWAIT
-- COLLAPSE, BLOCK 100% ALOCN_PCT - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT BALANCES.YYMM_ID
,BALANCES.BUS_UNIT_ID
,BALANCES.BUS_UNIT_PROD_LINE_CD
,BALANCES.BUS_UNIT_ID AS ALOCN_SRC_CD
,BALANCES.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM AS LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * BALANCES.ANULZD_ACTL_BAL
,ALOCN_SUMRY.ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'BLOCK-100' AS METHOD_TXT
FROM [MISProcess].[udf_MR_BALANCES_BLOCK_100_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS BALANCES
INNER JOIN [MISProcess].[udf_MR_ALOCN_SUMRY_BLOCK_100_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCN_SUMRY
ON ALOCN_SUMRY.YYMM_ID = BALANCES.YYMM_ID
AND ALOCN_SUMRY.BUS_UNIT_ID = BALANCES.BUS_UNIT_ID
AND ALOCN_SUMRY.BUS_UNIT_PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
AND ALOCN_SUMRY.ALOCN_SRC_CD = BALANCES.ALOCN_SRC_CD
AND ALOCN_SUMRY.ALOCN_SRC_PROD_LINE_CD = BALANCES.ALOCN_SRC_PROD_LINE_CD
AND ALOCN_SUMRY.ORIG_ALOCN_SRC_CD = BALANCES.ORIG_ALOCN_SRC_CD
RAISERROR ('COLLAPSE, BLOCK 100 ALOCN_PCT - A records', 10, 1) WITH NOWAIT
-- COLLAPSE, BLOCK 100% ALOCN_PCT - A records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL):
FOR EACH @LVL_NUM < @MAX_LVL:
INSERT INTO ALLOCS
SELECT 'OUT', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
INSERT INTO ALLOCS
SELECT 'IN', *
FROM BALANCES(@LVL_NUM)
INNER JOIN ALLOCNS(@LVL_NUM)
INNER JOIN ALLOCN_SUMRY(@LVL_NUM)
Where ALLOCS
is seeded with direct allocations and then BALANCES(@LVL_NUM)
is based on ALLOCS
at the @LVL_NUM
(which might be some direct allocations plus some IN allocations from a previous level) and ALOCNS(@LVL_NUM)
is based on BALANCES(@LVL_NUM)
and ALOCN_SUMRY(@LVL_NUM)
is simply based on ALOCNS(@LVL_NUM)
- with a lot of configuration tables which indicate the drivers which drive the allocations out.
This is simplified, but there are actually four or five pairs like this within the loop because there are a variety of logics which are not possible to handle together (and some cases which are possible to handle together.)
The basic logic is to take the total amount in a particular cost center/product line/etc (i.e. the BALANCES
) and then allocate it out to another cost center/product line/etc based on its share (i.e. the ALLOCNS / ALLOCN_SUMRY
percentage share) of a particular metric.
With so much logic repeated in the OUT
recordkeeping and the IN
, and of course the SUMRY
based on the ALLOCN
detail, I ended up implementing using inline table value functions, which seem to perform fairly well (and they match the existing system's behaviour in the regression tests, which is a plus!). (The existing system is a monster C/C++/MFC/ODBC program that reads all the data into massive arrays and other data structures and is pretty atrociously written.)
The problem appears to be that when run in the loop I appear to be getting execution plan issues as I work my way up the levels as the ALLOCS
table starts to change (and everything is changing, because the levels have different cost centers, so the configuration being used to drive the ALLOCNS
is changing). I have up to 99 levels, I think, but the lowest levels start 2, 4, 6. It appears that running @LVL_NUM = 6
by itself outside of the UDF performs fine, but that the UDF performs poorly - presumably because the UDF has a cached plan or that the overall plan is already bad because of the ALLOCS
added from earlier steps at @LVL_NUM IN (2, 4)
.
Earlier in development, I managed to get 30 levels run in 30 minutes, but now I can't get it to complete the first 3 levels in 2 hours.
I'm considering running the two inserts within another SP and calling it WITH RECOMPILE, but was curious if this RECOMPILE cascades properly into the TVF UDFs? Any other advice would also be appreciated.
Real Code:
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_BALANCES_STAT_UNI] Script Date: 05/14/2009 22:16:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_BALANCES_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT AB.YYMM_ID
,AB.BUS_UNIT_ID
,AB.BUS_UNIT_PROD_LINE_CD
-- ,AB.ALOCN_SRC_CD
,AB.ALOCN_SRC_PROD_LINE_CD
,CASE WHEN ORIG_ALSRC.ALOCN_TYPE_CD = 'C'
AND ORIG_ALSRC.RETN_IND = 'Y' THEN AB.ORIG_ALOCN_SRC_CD
ELSE AB.BUS_UNIT_ID
END AS ORIG_ALOCN_SRC_CD
,CASE WHEN BUPALSRC.COLLAPSE_IND = 'Y'
THEN BUPLNTM.ALOCN_LINE_ITEM_NUM
ELSE AB.LINE_ITEM_NUM
END AS ALOCN_LINE_ITEM_NUM
,SUM(BUPLNTM.ALOCN_SIGN_IND * AB.ANULZD_ACTL_BAL) AS ANULZD_ACTL_BAL
FROM MISWork.vwMR_BALANCES AS AB
INNER JOIN MISProcess.LKP_BUPLNTM AS BUPLNTM
ON BUPLNTM.DATA_DT_ID = @DATA_DT_ID
AND BUPLNTM.LINE_ITEM_NUM = AB.LINE_ITEM_NUM
AND BUPLNTM.ALOCN_LINE_ITEM_NUM <> 0
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, @LVL_NUM) AS BUPALSRC
ON BUPALSRC.ALOCN_SRC_CD = AB.BUS_UNIT_ID
INNER JOIN [MISProcess].LKP_BUPALSRC AS ORIG_ALSRC
ON ORIG_ALSRC.DATA_DT_ID = @DATA_DT_ID
AND ORIG_ALSRC.ALOCN_SRC_CD = AB.ORIG_ALOCN_SRC_CD
GROUP BY AB.YYMM_ID
,AB.BUS_UNIT_ID
,AB.BUS_UNIT_PROD_LINE_CD
-- ,AB.ALOCN_SRC_CD
,AB.ALOCN_SRC_PROD_LINE_CD
,CASE WHEN ORIG_ALSRC.ALOCN_TYPE_CD = 'C'
AND ORIG_ALSRC.RETN_IND = 'Y' THEN AB.ORIG_ALOCN_SRC_CD
ELSE AB.BUS_UNIT_ID
END
,CASE WHEN BUPALSRC.COLLAPSE_IND = 'Y'
THEN BUPLNTM.ALOCN_LINE_ITEM_NUM
ELSE AB.LINE_ITEM_NUM
END
)
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_ALOCNS_STAT_UNI] Script Date: 05/14/2009 22:16:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_ALOCNS_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT BALANCES.YYMM_ID
,BS.ALOCN_SRC_CD AS BUS_UNIT_ID
,BS.PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,BALANCES.BUS_UNIT_ID AS ALOCN_SRC_CD
,BALANCES.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM
,SUM(BS.ACCT_STATS_CNT) AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_BALANCES_STAT_UNI](@DATA_DT_ID, @LVL_NUM) AS BALANCES
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, @LVL_NUM) AS BUPALSRC
ON BUPALSRC.ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
INNER JOIN MISProcess.LKP_PRODLINE AS PRODLINE
ON PRODLINE.DATA_DT_ID = @DATA_DT_ID
AND PRODLINE.PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
INNER JOIN PUASFIN.FocusResults.BS AS BS
ON BS.YYMM_ID = BALANCES.YYMM_ID
AND BS.ALOCN_BASE_CD = BUPALSRC.ALOCN_BASE_CD
AND BS.ALOCN_SRC_CD <> BALANCES.BUS_UNIT_ID
AND (
PRODLINE.GENRC_PROD_LINE_IND = 'Y'
OR BS.PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
)
INNER JOIN [MISProcess].[udf_MR_ALSRC](@DATA_DT_ID, 0) AS DEST_BUP_ALSRC
ON DEST_BUP_ALSRC.ALOCN_SRC_CD = BS.ALOCN_SRC_CD
AND DEST_BUP_ALSRC.ALOCN_LVL_NUM > @LVL_NUM
LEFT JOIN [MISProcess].[udf_MR_BLOCK_STD_COST_PCT](@DATA_DT_ID) AS BLOCK_STD_COST_PCT
ON BLOCK_STD_COST_PCT.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
LEFT JOIN [MISProcess].[udf_MR_BLOCK_NOT](@DATA_DT_ID) AS BLOCK_NOT
ON BLOCK_NOT.ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
LEFT JOIN [MISProcess].[udf_MR_BLOCK](@DATA_DT_ID) AS BLOCK
ON BLOCK_NOT.ALOCN_SRC_CD IS NULL
AND BLOCK.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
AND (
BLOCK.FROM_PROD_LINE_CD IS NULL
OR BLOCK.FROM_PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
)
LEFT JOIN [MISProcess].[udf_MR_BLOCK_ALOCN_PAIRS](@DATA_DT_ID, @LVL_NUM)
AS BLOCK_ALOCN_PAIRS
ON BLOCK_NOT.ALOCN_SRC_CD IS NOT NULL
AND BLOCK_ALOCN_PAIRS.FROM_ALOCN_SRC_CD = BALANCES.BUS_UNIT_ID
AND BLOCK_ALOCN_PAIRS.TO_ALOCN_SRC_CD = BS.ALOCN_SRC_CD
WHERE BLOCK_ALOCN_PAIRS.TO_ALOCN_SRC_CD IS NULL
AND BLOCK_STD_COST_PCT.FROM_ALOCN_SRC_CD IS NULL
AND (
BLOCK.TO_ALOCN_SRC_CD IS NULL
OR BLOCK.TO_ALOCN_SRC_CD = BS.ALOCN_SRC_CD
)
AND (
BLOCK.TO_PROD_LINE_CD IS NULL
OR BLOCK.TO_PROD_LINE_CD = BS.PROD_LINE_CD
)
AND (
BLOCK.YEAR_NUM IS NULL
OR BLOCK.YEAR_NUM = BALANCES.YYMM_ID / 10000
)
AND (
BLOCK.MTH_NUM IS NULL
OR BLOCK.MTH_NUM = (BALANCES.YYMM_ID / 100) % 100
)
AND (
BLOCK.TO_DIV_NUM IS NULL
OR BLOCK.TO_DIV_NUM = DEST_BUP_ALSRC.DIV_NUM
)
AND (
BLOCK.TO_GRP_NUM IS NULL
OR BLOCK.TO_GRP_NUM = DEST_BUP_ALSRC.DIV_GRP
)
AND (
BLOCK.TO_REGN_GRP_NM IS NULL
OR BLOCK.TO_REGN_GRP_NM = DEST_BUP_ALSRC.REGN_GRP_NM
)
AND (
BLOCK.TO_REGN_NM IS NULL
OR BLOCK.TO_REGN_NM = DEST_BUP_ALSRC.REGN_NM
)
AND (
BLOCK.TO_ARENA_NM IS NULL
OR BLOCK.TO_ARENA_NM = DEST_BUP_ALSRC.ARENA_NM
)
AND (
BLOCK.TO_SUB_REGN_NM IS NULL
OR BLOCK.TO_SUB_REGN_NM = DEST_BUP_ALSRC.SUB_REGN_NM
)
AND (
BLOCK.TO_SUB_ARENA_NM IS NULL
OR BLOCK.TO_SUB_ARENA_NM = DEST_BUP_ALSRC.SUB_ARENA_NM
)
GROUP BY BALANCES.YYMM_ID
,BS.ALOCN_SRC_CD
,BS.PROD_LINE_CD
,BALANCES.BUS_UNIT_ID
,BALANCES.BUS_UNIT_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM
)
/****** Object: UserDefinedFunction [MISProcess].[udf_MR_ALOCN_SUMRY_STAT_UNI] Script Date: 05/14/2009 22:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [MISProcess].[udf_MR_ALOCN_SUMRY_STAT_UNI] (
@DATA_DT_ID int
,@LVL_NUM int
)
RETURNS TABLE
-- WITH SCHEMABINDING
AS
RETURN
(
SELECT YYMM_ID
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,ALOCN_LINE_ITEM_NUM
,SUM(ACCT_STATS_CNT) AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_ALOCNS_STAT_UNI](@DATA_DT_ID, @LVL_NUM) AS ALOCNS
GROUP BY YYMM_ID
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,ALOCN_LINE_ITEM_NUM
)
This is my testing batch which will eventually run the entire process in a single SP. You can see from commented out sections that I've been playing with temporary tables and table variables as well:
USE PCAPFIN
DECLARE @DATA_DT_ID_use AS int
DECLARE @MinLevel AS int
DECLARE @MaxLevel AS int
DECLARE @TestEveryLevel AS bit
DECLARE @TestFinal AS bit
SET @DATA_DT_ID_use = 20090331
SET @MinLevel = 6
SET @MaxLevel = 6
SET @TestEveryLevel = 0
SET @TestFinal = 1
--DECLARE @BALANCES TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ANULZD_ACTL_BAL money
-- )
--
--DECLARE @ALOCNS TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- )
--
--DECLARE @ALOCN_SUMRY TABLE (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- )
--IF OBJECT_ID('tempdb..#BALANCES') IS NOT NULL
-- DROP TABLE #BALANCES
--
--CREATE TABLE #BALANCES (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ANULZD_ACTL_BAL money
-- ,CONSTRAINT [PK_BALANCES] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, [YYMM_ID] ASC, [BUS_UNIT_ID] ASC, [BUS_UNIT_PROD_LINE_CD] ASC, [ALOCN_SRC_PROD_LINE_CD] ASC, [ORIG_ALOCN_SRC_CD] ASC, [ALOCN_LINE_ITEM_NUM] ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
--
--IF OBJECT_ID('tempdb..#ALOCN_SUMRY') IS NOT NULL
-- DROP TABLE #ALOCNS
--
--CREATE TABLE #ALOCNS (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,BUS_UNIT_ID varchar(6) NOT NULL
-- ,BUS_UNIT_PROD_LINE_CD varchar(4) NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- ,CONSTRAINT [PK_ALOCNS] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, YYMM_ID ASC, BUS_UNIT_ID ASC, BUS_UNIT_PROD_LINE_CD ASC, ALOCN_SRC_CD ASC, ALOCN_SRC_PROD_LINE_CD ASC, ORIG_ALOCN_SRC_CD ASC, ALOCN_LINE_ITEM_NUM ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
--
--IF OBJECT_ID('tempdb..#ALOCN_SUMRY') IS NOT NULL
-- DROP TABLE #ALOCN_SUMRY
--CREATE TABLE #ALOCN_SUMRY (
-- METHOD_TXT varchar(12) NOT NULL
-- ,YYMM_ID int NOT NULL
-- ,ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_SRC_PROD_LINE_CD varchar(4) NOT NULL
-- ,ORIG_ALOCN_SRC_CD varchar(6) NOT NULL
-- ,ALOCN_LINE_ITEM_NUM int NOT NULL
-- ,ACCT_STATS_CNT money
-- ,CONSTRAINT [PK_ALOCN_SUMRY] PRIMARY KEY CLUSTERED ([METHOD_TXT] ASC, YYMM_ID ASC, ALOCN_SRC_CD ASC, ALOCN_SRC_PROD_LINE_CD ASC, ORIG_ALOCN_SRC_CD ASC, ALOCN_LINE_ITEM_NUM ASC)
-- WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON)
-- )
SET @MinLevel = (
SELECT MIN(BUPALSRC.ALOCN_LVL_NUM)
FROM MISProcess.LKP_BUPALSRC AS BUPALSRC
WHERE BUPALSRC.DATA_DT_ID = @DATA_DT_ID_use
AND BUPALSRC.ALOCN_LVL_NUM >= @MinLevel
)
DECLARE @Restart AS bit
IF @MinLevel > (
SELECT MIN(BUPALSRC.ALOCN_LVL_NUM)
FROM MISProcess.LKP_BUPALSRC AS BUPALSRC
WHERE BUPALSRC.DATA_DT_ID = @DATA_DT_ID_use
)
SET @Restart = 0
ELSE
SET @Restart = 1
DECLARE @subset_criteria AS varchar(max)
SET NOCOUNT ON
IF @Restart = 1
BEGIN
RAISERROR ('Restarting process', 10, 1) WITH NOWAIT
-- TRUNCATE TABLE MISWork.AB
DELETE FROM MISWork.AB
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT YYMM_ID
,ALOCN_SRC_CD AS BUS_UNIT_ID
,PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCN_SRC_CD AS ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,'D' AS BAL_ORIGTN_IND
,FIN_ALOCN_AMT AS ANULZD_ACTL_BAL
,0.0 AS ACCT_STATS_CNT
,0 AS LVL_NUM
,'D-INIT' AS METHOD_TXT
-- FROM MISProcess.FIN_FTP
FROM PUASFIN.FocusResults.BUPALLGE
END
ELSE
BEGIN
DELETE FROM MISWork.AB
WHERE LVL_NUM >= @MinLevel
END
DECLARE @LVL_NUM AS int
SET @LVL_NUM = @MinLevel
WHILE @LVL_NUM <= @MaxLevel
BEGIN
DECLARE @LevelStart AS varchar(50)
SET @LevelStart = 'Level:' + CONVERT(varchar, @LVL_NUM)
RAISERROR (@LevelStart, 10, 1) WITH NOWAIT
RAISERROR ('STD_COST_PCT allocations - No D - B records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - No D - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.BUS_UNIT_ID AS ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.BUS_UNIT_ID AS ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * ROUND(ALOCNS.ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'NO-D-B' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT_NO_D](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
RAISERROR ('STD_COST_PCT allocations - No D - A records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - No D - A records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,BLOCK.TO_ALOCN_SRC_CD AS BUS_UNIT_ID
,ALOCNS.ALOCN_SRC_PROD_LINE_CD AS BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'A' AS BAL_ORIGTN_IND
,ROUND(ALOCNS.ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'NO-D-A' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT_NO_D](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN MISProcess.LKP_BLOCK AS BLOCK
-- TODO: Can this be moved into the udf above?
ON BLOCK.DATA_DT_ID = @DATA_DT_ID_use
AND BLOCK.FROM_ALOCN_SRC_CD = ALOCNS.BUS_UNIT_ID
RAISERROR ('STD_COST_PCT allocations - B records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * ROUND(ALOCNS.ANULZD_ACTL_BAL * RATIOS.RATIO, 2) AS ANULZD_ACTL_BAL
,ROUND(ALOCNS.ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'STD-B' AS METHOD_TXT
FROM [MISProcess].[udf_MR_ALOCNS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN [MISProcess].[udf_MR_RATIOS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS RATIOS
ON RATIOS.YYMM_ID = ALOCNS.YYMM_ID
AND RATIOS.BUS_UNIT_ID = ALOCNS.BUS_UNIT_ID
AND RATIOS.LINE_ITEM_NUM = ALOCNS.LINE_ITEM_NUM
RAISERROR ('STD_COST_PCT allocations - A records', 10, 1) WITH NOWAIT
-- STD_COST_PCT allocations - A records
;
WITH CORRECTED_ALOCNS
AS (
SELECT ALOCNS.YYMM_ID
,ALOCNS.BUS_UNIT_ID
,ALOCNS.BUS_UNIT_PROD_LINE_CD
,ALOCNS.ALOCN_SRC_CD
,ALOCNS.ALOCN_SRC_PROD_LINE_CD
,ALOCNS.ORIG_ALOCN_SRC_CD
,ALOCNS.LINE_ITEM_NUM
,ALOCNS.ANULZD_ACTL_BAL * RATIOS.RATIO AS ANULZD_ACTL_BAL
,CASE WHEN RATIOS.RATIO <> 1.0
THEN RATIOS.RATIO
ELSE ALOCNS.ACCT_STATS_CNT
END AS ACCT_STATS_CNT
FROM [MISProcess].[udf_MR_CORR_ALOCNS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCNS
INNER JOIN [MISProcess].[udf_MR_RATIOS_STD_COST_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS RATIOS
ON RATIOS.YYMM_ID = ALOCNS.YYMM_ID
AND RATIOS.BUS_UNIT_ID = ALOCNS.ALOCN_SRC_CD
AND RATIOS.LINE_ITEM_NUM = ALOCNS.LINE_ITEM_NUM
),
ROUNDED_ALOCNS
AS (
SELECT YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,CASE WHEN ABS(ANULZD_ACTL_BAL) < 0.05 THEN 0.0
WHEN ABS(ANULZD_ACTL_BAL) > 0.05
AND ABS(ANULZD_ACTL_BAL) < 0.10
THEN 0.10 * SIGN(ANULZD_ACTL_BAL)
ELSE ANULZD_ACTL_BAL
END AS ANULZD_ACTL_BAL
,ACCT_STATS_CNT
FROM CORRECTED_ALOCNS
)
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,'A' AS BAL_ORIGTN_IND
,ROUND(ANULZD_ACTL_BAL, 2) AS ANULZD_ACTL_BAL
,ROUND(ACCT_STATS_CNT, 2) AS ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'STD-A' AS METHOD_TXT
FROM ROUNDED_ALOCNS
WHERE ANULZD_ACTL_BAL <> 0.0
OR ACCT_STATS_CNT <> 0.0
RAISERROR ('COLLAPSE, BLOCK 100 ALOCN_PCT - B records', 10, 1) WITH NOWAIT
-- COLLAPSE, BLOCK 100% ALOCN_PCT - B records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
,ORIG_ALOCN_SRC_CD
,LINE_ITEM_NUM
,BAL_ORIGTN_IND
,ANULZD_ACTL_BAL
,ACCT_STATS_CNT
,LVL_NUM
,METHOD_TXT
)
SELECT BALANCES.YYMM_ID
,BALANCES.BUS_UNIT_ID
,BALANCES.BUS_UNIT_PROD_LINE_CD
,BALANCES.BUS_UNIT_ID AS ALOCN_SRC_CD
,BALANCES.BUS_UNIT_PROD_LINE_CD AS ALOCN_SRC_PROD_LINE_CD
,BALANCES.ORIG_ALOCN_SRC_CD
,BALANCES.ALOCN_LINE_ITEM_NUM AS LINE_ITEM_NUM
,'B' AS BAL_ORIGTN_IND
,-1.0 * BALANCES.ANULZD_ACTL_BAL
,ALOCN_SUMRY.ACCT_STATS_CNT
,@LVL_NUM AS LVL_NUM
,'BLOCK-100' AS METHOD_TXT
FROM [MISProcess].[udf_MR_BALANCES_BLOCK_100_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS BALANCES
INNER JOIN [MISProcess].[udf_MR_ALOCN_SUMRY_BLOCK_100_PCT](@DATA_DT_ID_use, @LVL_NUM)
AS ALOCN_SUMRY
ON ALOCN_SUMRY.YYMM_ID = BALANCES.YYMM_ID
AND ALOCN_SUMRY.BUS_UNIT_ID = BALANCES.BUS_UNIT_ID
AND ALOCN_SUMRY.BUS_UNIT_PROD_LINE_CD = BALANCES.BUS_UNIT_PROD_LINE_CD
AND ALOCN_SUMRY.ALOCN_SRC_CD = BALANCES.ALOCN_SRC_CD
AND ALOCN_SUMRY.ALOCN_SRC_PROD_LINE_CD = BALANCES.ALOCN_SRC_PROD_LINE_CD
AND ALOCN_SUMRY.ORIG_ALOCN_SRC_CD = BALANCES.ORIG_ALOCN_SRC_CD
RAISERROR ('COLLAPSE, BLOCK 100 ALOCN_PCT - A records', 10, 1) WITH NOWAIT
-- COLLAPSE, BLOCK 100% ALOCN_PCT - A records
INSERT INTO MISWork.AB (
YYMM_ID
,BUS_UNIT_ID
,BUS_UNIT_PROD_LINE_CD
,ALOCN_SRC_CD
,ALOCN_SRC_PROD_LINE_CD
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
是的,重新编译应该扩展到 TV UDFS。
但是,我会使用参数屏蔽而不是重新编译。
您可以将一些 UDF 调用分解到临时表中,然后加入临时表吗?
我敢打赌,当 UDF 未嵌套时,查询会过于复杂而无法高效运行。 对于如此复杂的事情,优化者可能需要一周的时间才能找到理想的计划。 使用临时表(不是表变量),我想您会得到可观的改进。
我自己在一些较大的查询中使用了这种技术(为金融工具生成定价树),
我认为 150,000 行的事实被纯粹的复杂性所掩盖。
编辑:
TVF 不需要参数屏蔽,因为它们只是宏。 您实际上可以将其替换为 CTE 或派生表。
在这里查看我的答案:查询计划优化器是否可以很好地与连接/过滤的表值函数配合使用
和 托尼·罗杰森的观点
Yes, the recompile should extend to the TV UDFS.
However, I'd use parameter masking not RECOMPILE.
Can you break out some UDF calls into temporary tables and then join on the temp tables?
I bet that when the UDFs are unnested, the query is simply too complex to run efficiently. The optimiser could take a week to find the ideal plan with something so complex. With temp tables (not table variables), I guess you'll get respectable improvements.
I've used this technique myself in some larger queries (generating pricing trees for financial instruments)
The fact you 150,000 rows is overshadowed by the sheer complexity I reckon.
Edit:
TVFs do not need parameter masking because they are only macros. You could literally replace it with a CTE or derived table.
See my answer here: Does query plan optimizer works well with joined/filtered table-valued functions
And Tony Rogerson on Views