CTE表格优化

我有一个包含150万行的表。它具有有效的起始时间和有效的起始时间,我正在创建这些时间序列。我计划将它们合并到我拥有的其他表中(行为类型变量,如每月付款/账单等),但是在使用以下代码创建表时,这将永远花费。在前1000名上奔跑大约需要3秒钟。最高10000大约50秒,所以我认为运行时间是指数的。

下面是代码

SELECT
*
FROM
(
SELECT
    a.[PROP_NUM]
    ,b.table_code
    ,a.[RATE_UNIT_from]
    ,a.[RATE_UNIT_to]
    ,EOMONTH( a.[RATE_UNIT_from]) AS [Valid From]
    ,EOMONTH(CASE WHEN a.[RATE_UNIT_to] >= CAST(GETDATE() AS DATE) THEN CAST(GETDATE() AS DATE) ELSE a.[RATE_UNIT_to] END) AS [Valid To]
    ,ROW_NUMBER() OVER(PARTITION BY a.[PROP_NUM], EOMONTH(a.[RATE_UNIT_from]) ORDER BY a.[RATE_UNIT_to] DESC) AS [Row Number]
    INTO [dbo].[Historical LUC]
FROM [Grange_Prod].[dbo].[PROP_RATING_UNIT] as a
LEFT JOIN CODE_TABLE  as b
on a.prop_pru_tcode = b.table_code
WHERE a.[RATE_UNIT_TYPE_TCODE] = 'LUC' and b.table_type_code = 'LUC' and EOMONTH(CASE WHEN a.[RATE_UNIT_to] >= CAST(GETDATE() AS DATE) THEN CAST(GETDATE() AS DATE) ELSE a.[RATE_UNIT_to] END) >= '20131231'
) as z1
WHERE [Row Number] = 1
;
CREATE INDEX LUC ON [dbo].[Historical LUC] ([PROP_NUM], [Valid From], [Valid To])
;
DROP TABLE IF EXISTS [LUC Time Series]
;
WITH CTE AS
(
SELECT
    z1.[PROP_NUM]
    ,z1.[table_code]
    ,z1.[RATE_UNIT_from]
    ,z1.[RATE_UNIT_to]
    ,CASE WHEN z1.[Valid From] <= '20140101' THEN '20140101' ELSE z1.[Valid From] END AS [Valid From]
    ,z1.[Valid To]
FROM [dbo].[Historical LUC] AS z1
UNION ALL
SELECT
    [PROP_NUM]
    ,[table_code]
    ,[RATE_UNIT_from]
    ,[RATE_UNIT_to]
    ,EOMONTH(DATEADD(MONTH, 1, [Valid From])) AS [EOM Date]
    ,[Valid To]
    FROM CTE
WHERE [Valid From] < [Valid To]
)
SELECT
    [PROP_NUM]
    ,[table_code]
    ,EOMONTH(DATEADD(MONTH, 1, [Valid From])) AS [EOM Date]
    INTO [LUC Time Series]
FROM CTE AS a
ORDER BY [PROP_NUM], [Valid From]
OPTION (MAXRECURSION 32767)

有什么方法可以进一步增强此查询?我愿意接受任何建议。

评论