question

shay avatar image
shay asked

avoid table writes in cursor

Hi guys,

I was wondering if there is a way to avoid table writes(select into or insert into) inside cursors. I mean, if i can use the data that is being formed inside the cursor without storing in tables(temporary or permanent)?

As u understand the cursor goes for 120 times, so I was trying to avoid writing into tables (not 120 separate tables because i create the table at the beginning , use it to perform the required task and then drop it ; so its not 120 separate tables but one table being written and overwritten). The logic works great, but it took about an hour for the execution. When i look into the execution plan, it shows that the table write part took maximum %age ; hence the need for shortening the time period.

Would appreciate any kind of help.Thanks in advance

sql-server-2008insertcursortemporary-tables
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
TG avatar image
TG answered

The best way to speed up code that uses a cursor is to eliminate the cursor altogether. Convert the logic to use a set-based methodology. Edit your post to include the cursor code if you want help with it. (don't forget to format the code block so we can read it)

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.

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.