SO 51804194 : sql-recursive-cte-replace-statement-too-slow, Sql Server

SO 51804194 : sql-recursive-cte-replace-statement-too-slow

CREATE TABLE #table1(IdExpresion INT, expresion VARCHAR(MAX))
CREATE TABLE #table2(IdExpresion INT, searchExpresion VARCHAR(50), replacementExpresion VARCHAR(50))

INSERT INTO #table1(IdExpresion, expresion)
VALUES(1, 'Mary had a little lamb'),
      (2, 'The new student, student_name has the following grades Math - math_grade, Science - Science_grade')

INSERT INTO #table2(IdExpresion, searchExpresion, replacementExpresion)
VALUES(1, 'lamb','dog'),
      (2, 'student_name','Joe Smith'),
      (2, 'math_grade','A'),
      (2, 'Science_grade','B+');

CREATE TABLE #tmpSearch (
    IdExpresion INT, 
    rn INT,
    searchExpresion VARCHAR(50), 
    replacementExpresion VARCHAR(50),
    primary key (IdExpresion, rn));

insert into #tmpSearch (IdExpresion, rn, searchExpresion, replacementExpresion)
select 
 IdExpresion,
 row_number() over (partition by IdExpresion order by searchExpresion) as rn,
 searchExpresion, 
 replacementExpresion 
from #table2 
order by IdExpresion, searchExpresion;

;WITH CTE(IdExpresion, expresion, lvl) AS
(
    SELECT t1.IdExpresion, t1.expresion, max(s.rn)
    FROM #table1 t1
    JOIN #tmpSearch s ON s.IdExpresion = t1.IdExpresion
    GROUP BY t1.IdExpresion, t1.expresion

UNION ALL

SELECT c.IdExpresion, REPLACE(c.expresion, s.searchExpresion, s.replacementExpresion), c.lvl - 1
    FROM CTE c
    JOIN #tmpSearch s
    ON s.IdExpresion = c.IdExpresion AND s.rn = c.lvl
)
SELECT IdExpresion, expresion
FROM CTE
WHERE lvl = 0
OPTION (MAXRECURSION 0);


run \| edit \| history \| help	0

Please log in to post a comment.