CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
noobMan
noobMan

Reputation: 123

CTE Table optimisation

I have a table that contains 1.5 million rows. It has a valid from and a valid to and I am creating a time series of these. I plan on merging these to other tables I have (behavioural type variables like monthly payments / bills etc) but while creating the table using the below code it takes forever. Running on the top 1000 takes about 3 seconds. Top 10000 about 50 seconds so I think the run time is exponetial.

Below is the code

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)

Is there a way in which I can further enhance this query? I am open to any suggestions.

Upvotes: 0

Views: 68

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I am surprised SQL Sever struggles with this query. After all, the recursive query contains no join, so for each row you always only generate one new row (the next month until the period's end that is).

An alternative may be to create a table of months since 2014. That's less then 100 rows. Then you can write something like this, which shouldn't take much time to process:

select *
from [dbo].[Historical LUC] h
join months m on m.month between h.[Valid From] and h.[Valid To];

Upvotes: 1

Related Questions