Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
prog1
Grouping, aggregate function issue when joining to another table
db
performance on update using subquery versus correlated update
dbms
Time Difference Calculation (amended v2)
BC3TOSQL
Shalvika's Query
Las reglas Codd para una base de datos relacional Bry
Where clause inside sub query
Please log in to post a comment.