代码之家  ›  专栏  ›  技术社区  ›  Cade Roux

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

  •  1
  • Cade Roux  · 技术社区  · 15 年前

    在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) 基于 分配器 @LVL_NUM (可能是一些直接分配加上一些上一级别的分配)和 ALOCNS(@LVL_NUM) 基于 余额(@lvl_num) ALOCN_SUMRY(@LVL_NUM) 只是基于 alocns(@lvl_num) -有很多配置表指示驱动分配的驱动程序。

    这是简化的,但实际上在循环中有四对或五对这样的逻辑,因为有各种各样的逻辑不可能一起处理(有些情况可能一起处理)。

    基本逻辑是采用特定成本中心/产品线/等的总金额(即 BALANCES )然后根据其份额将其分配给另一个成本中心/产品线等(即 ALLOCNS / ALLOCN_SUMRY 百分比份额)。

    有如此多的逻辑重复在 OUT 记录和 IN 当然还有 SUMRY 基于 ALLOCN 详细地说,我最终使用了内联表值函数来实现,这些函数的性能似乎相当好(它们与回归测试中现有系统的行为相匹配,这是一个优点!)(现有的系统是一个怪物C/C/+/MFC/ODBC程序,它将所有数据读入大量数组和其他数据结构,并且写得非常糟糕。)

    问题似乎是,当在循环中运行时,我似乎得到了执行计划的问题,因为我正在逐步提高级别 分配器 表开始改变(而且每件事都在改变,因为级别有不同的成本中心,所以用于驱动 ALLOCNS 正在改变。我想我有99个等级,但是最低的等级是2,4,6。似乎在跑步 @LVL_NUM = 6 就其本身而言,在UDF之外执行良好,但UDF执行不佳——可能是因为UDF有缓存计划,或者总体计划已经很糟糕,因为 分配器 从前面的步骤添加到 @LVL_NUM IN (2, 4) .

    在开发早期,我设法在30分钟内运行30个级别,但现在我无法在2小时内完成前3个级别。

    我正在考虑在另一个SP中运行这两个插入,并用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
    
    3 回复  |  直到 15 年前
        1
  •  1
  •   Community Reversed Engineer    7 年前

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

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

    1. 有了这样的疑问,自满的代价会很高。
    2. 当不需要UDF时,参数屏蔽也将应用。TV UDF没有这样的计划:它们是调用查询的一部分,因为它们是未经测试的。

    您可以将一些UDF调用分解成临时表,然后加入临时表吗? 我敢打赌,当不需要UDF时,查询太复杂,无法有效运行。乐观主义者可能需要一周的时间来找到一个复杂的理想计划。有了临时表(而不是表变量),我想您会得到值得尊敬的改进。

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

    你15万行的数据被我估计的复杂程度掩盖了。

    编辑 :

    TVF不需要参数屏蔽,因为它们只是宏。您可以直接用CTE或派生表替换它。

    请看我的答案: Does query plan optimizer works well with joined/filtered table-valued functions Tony Rogerson on Views

        2
  •  0
  •   Remus Rusanu    15 年前

    你能发布实际的T-SQL而不是伪SQL吗? 您所描述的听起来像是随着@lvl_num的增加而对越来越大的结果集进行的表扫描,并且可能重新编译对这一点没有帮助。但是,基于伪SQL,除了伪猜测之外,很难给出任何东西…

        3
  •  0
  •   Mitch Wheat    15 年前

    对于表值函数(尤其是多语句表值函数),需要注意的一点是,结果表与表变量一样,没有列统计信息,也没有索引。

    我倾向于谨慎使用电视。