please help me tune the below SQL Server query..its running forever [closed]

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 CAST or 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 t_temp table).

Also, you could speed up this process by declaring a unique clustered index (or primary key) on the id column:

CREATE UNIQUE CLUSTERED INDEX PK ON t_temp (id);