Hi, In a stored procedure, I build a temporary table and then I fill it in a loop.
The loop uses dynamic sql to fill the table like this: while begin set @dynSql = "UPDATE #tmptable SET [' + @column_name '] = ''' + @value + ''' WHERE c = ''' + @variable exec @dynSql end The @column_name, @value and @variable variables vary according to the loop condition. The temporary table is not very big, it has 50 columns and 400 rows. The problem is the update runs 13000 times and it takes 40 seconds.
If I remove the dynamic sql and put a hardcoded query that always does the same update, the update time is 5 seconds.
I guess its because the statement is recompiled in each loop iteration when using dynamic sql.
Is there something I can do?
Don't use a cursor (and a WHILE loop is a cursor) to attempt to do things within SQL Server. It is an inherently slow process. This is especially true if you're running a loop 13,000 times. T-SQL is a set based processing language. It's always best to use a set based approach. It's unclear exactly what you're attempting to do unless it's write a single query for processing all different types of tables. If so, don't do that. Another thing that T-SQL doesn't lend itself well to is the concept of code re-use. If you need to process two different tables, write two different queries.