T-SQL流程设计和执行计划(UDF参数嗅探?)

发布于 07-19 14:07 字数 25753 浏览 8 评论 0原文

在 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 中重复了如此多的逻辑,当然还有基于 ALLOCNSUMRY详细地说,我最终使用内联表值函数来实现,它似乎表现得相当好(并且它们与回归测试中现有系统的行为相匹配,这是一个优点!)。 (现有系统是一个巨大的 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

柳絮泡泡2024-07-26 14:07:28

是的,重新编译应该扩展到 TV UDFS。

但是,我会使用参数屏蔽而不是重新编译。

  1. 对于这样的查询,编译的成本将会很高。
  2. 当 UDF 未嵌套时,参数屏蔽也将适用。 TV UDF 没有这样的计划:它们是调用查询的一部分,因为它们是非嵌套的。

您可以将一些 UDF 调用分解到临时表中,然后加入临时表吗?
我敢打赌,当 UDF 未嵌套时,查询会过于复杂而无法高效运行。 对于如此复杂的事情,优化者可能需要一周的时间才能找到理想的计划。 使用临时表(不是表变量),我想您会得到可观的改进。

我自己在一些较大的查询中使用了这种技术(为金融工具生成定价树),

我认为 150,000 行的事实被纯粹的复杂性所掩盖。

编辑

TVF 不需要参数屏蔽,因为它们只是宏。 您实际上可以将其替换为 CTE 或派生表。

在这里查看我的答案:查询计划优化器是否可以很好地与连接/过滤的表值函数配合使用
托尼·罗杰森的观点

Yes, the recompile should extend to the TV UDFS.

However, I'd use parameter masking not RECOMPILE.

  1. With queries like this, complation will be expensive
  2. When the UDFs are unnested, the parameter masking will apply too. TV UDFs do not have a plan as such: they are part of the calling query because they are unnested.

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

手心的海2024-07-26 14:07:28

您能发布实际的 T-SQL 而不是伪 SQL 吗?
您所描述的内容听起来像是随着 @LVL_NUM 的增加而对越来越大的结果集进行表扫描,并且 RECOMPILE 可能对此无济于事。 但是,基于伪 SQL,除了伪猜测之外,真的很难给出任何东西......

Can you post the actual T-SQL rather that pseudo-SQL?
What you describe sounds like tables scans on larger and larger results sets as @LVL_NUM increases and probably RECOMPILE won't help there. But, based on pseudo-SQL, is really hard to give anything more than a pseudo-guess...

又怨2024-07-26 14:07:28

使用表值函数(尤其是多语句表值函数)需要注意的事情之一是,结果表就像表变量一样,没有列统计信息,也没有索引。

我倾向于谨慎使用 TVF。

One of the things to watch out for with table-valued functions (especially the multi-statement table valued functions) is that the resultant table, just like a table variable, has no column statistics and no indexes.

I tend to use TVFs with caution.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文