Declare @bst varchar(1000), @pst varchar(1000), @ost varchar(1000), @t_cnt int = 1; WHILE @t_cnt <= (SELECT count(*) FROM t_temp) BEGIN SELECT @bst = g_Search_Text FROM t_temp WHERE id = @t_cnt; SELECT @pst = p_Search_Text FROM t_temp WHERE id = @t_cnt; SELECT @ost = o_Search_Text FROM t_temp WHERE id = @t_cnt; WHILE CHARINDEX(' ',@bst) > 0 BEGIN SET @bst = replace(@bst,' ',' ') END WHILE CHARINDEX(' ',@pst) > 0 BEGIN SET @pst = replace(@pst,' ',' ') END WHILE CHARINDEX(' ',@ost) > 0 BEGIN SET @ost = replace(@ost,' ',' ') END update t_temp set b_Search_Text=@bst WHERE id = @t_cnt; update t_temp set p_Search_Text=@pst WHERE id = @t_cnt; update t_temp set o_Search_Text=@ost WHERE id = @t_cnt; SET @t_cnt = @t_cnt + 1 END;
The first thing you need to look at is that SQL is a set based way of solving problems. In traditional programming languages, you’d loop through each row, apply some kind of logic, find the next row, rinse-repeat. In the database world, a set-based approach means that you tell the database what you want to do on a set of rows, not row-by-row.
This is the root of your performance problem.
The following code uses a recursive common table expression to repeat the
REPLACE(..., ' ', ' ') operation until it’s not needed anymore (which is kind of row-by-row in a sense), but it does that on the entire table, so I expect it to be considerably more efficient.
It then takes that result and applies it back to the table using a cross-UPDATE.
WITH cte AS ( --- 1. This is the anchor of the recursion: here are --- all the rows that need fixing: SELECT id, b_Search_Text, p_Search_Text, o_Search_Text FROM t_temp WHERE b_Search_Text LIKE '% %' OR p_Search_Text LIKE '% %' OR o_Search_Text LIKE '% %' UNION ALL --- 2. Perform the REPLACE() as many times as necessary, --- until there are no more double-space occurrences: SELECT id, REPLACE(b_Search_Text, ' ', ' '), REPLACE(p_Search_Text, ' ', ' '), REPLACE(o_Search_Text, ' ', ' ') FROM cte WHERE b_Search_Text LIKE '% %' OR p_Search_Text LIKE '% %' OR o_Search_Text LIKE '% %') --- 3. Join the result of the cleanup back to the "t_temp" --- table, and apply the results using a cross-UPDATE: UPDATE t_temp SET t.b_Search_Text=cte.b_Search_Text, t.p_Search_Text=cte.p_Search_Text, t.o_Search_Text=cte.o_Search_Text FROM t_temp AS t INNER JOIN cte ON t.id=cte.id --- ... but only those rows from the cte that have been fixed! WHERE NOT (cte.b_Search_Text LIKE '% %' OR cte.p_Search_Text LIKE '% %' OR cte.o_Search_Text LIKE '% %');
Because you haven’t provided table definitions, I’ve made the assumption that
id is a unique primary key in your table. If it isn’t, this code (and yours) won’t work.
You will probably get a “Types don’t match” error, in the cte. If you do, you’ll need to explicitly
CONVERT the result of the
REPLACE() so the datatypes of the columns in the recursion match those of the columns in the anchor (i.e. in the
Also, you could speed up this process by declaring a unique clustered index (or primary key) on the
CREATE UNIQUE CLUSTERED INDEX PK ON t_temp (id);