question

palladiol avatar image
palladiol asked

How can i speed up dynamic sql in a loop

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?
stored-proceduresdynamic-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I agree with Grant's points. If you can give us more details of what you are attempting to do, we might be able to help you convert your iterative (looping) code to the set-based style that (as Grant said) is much more efficient with SQL Server.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.