How can i speed up dynamic sql in a loop

Question

question

palladiol asked Sep 29, '15

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-procedures dynamic-sql

10 |1200

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

Answer 1 · 2015-09-30T09:45:56Z

Grant Fritchey answered Sep 30, '15

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

10 |1200

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

Tom Staab ♦ commented · Sep 30, 2015 at 01:17 PM

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 ·

question