T-SQL流程设计和执行计划(UDF参数嗅探？)
T-SQL流程设计和执行计划(UDF参数嗅探？)
提问于 2009-05-15 02:08:53
在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的ALLOCS (可能是一些直接分配加上来自上一级别的一些IN分配)，而ALOCNS(@LVL_NUM)基于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中运行这两个插入，并使用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
Stack Overflow用户
回答已采纳
发布于 2009-05-15 04:28:03
是的，重新编译应该扩展到TV UDFS。
但是，我会使用参数屏蔽，而不是重新编译。
对于像这样的查询，编译将是昂贵的
- 当UDF未嵌套时，参数掩码也将适用。TV UDF本身没有计划:它们是调用查询的一部分，因为它们是未嵌套的。
您可以将一些UDF调用分解到临时表中，然后在临时表上连接吗？我敢打赌，当UDF不嵌套时，查询就会太复杂而无法有效地运行。对于如此复杂的事情，优化器可能需要一周的时间才能找到理想的计划。使用临时表(而不是表变量)，我猜您会得到可观的改进。
我自己在一些较大的查询中使用过这种技术(为金融工具生成定价树)
我认为，您的150,000行数据完全被它的复杂性所掩盖。
编辑
TVF不需要参数掩码，因为它们只是宏。您可以直接用CTE或派生表替换它。
在这里查看我的答案：Does query plan optimizer works well with joined/filtered table-valued functions和Tony Rogerson on Views
Stack Overflow用户
发布于 2009-05-15 02:50:34
对于表值函数(尤其是多语句表值函数)需要注意的一件事是，结果表就像表变量一样，没有列统计信息，也没有索引。
我倾向于谨慎使用TVF。
Stack Overflow用户
发布于 2009-05-15 03:11:18
你能发布真正的T-SQL而不是伪SQL吗？您所描述的听起来像是随着@LVL_NUM的增加而在越来越大的结果集上扫描表格，而重新编译可能对此没有帮助。但是，基于伪SQL，真的很难给出比伪猜测更多的东西了……
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/866722
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号