Why is my SQL Server cursor very slow?

Question

I am using a Cursor in my stored procedure. It works on a database that has a huge number of data. for every item in the cursor i do a update operation. This is taking a huge amount of time to complete. Almost 25min. :( .. Is there anyway i can reduce the time consumed for this?

Answer 1

The quick answer is not to use a cursor. The most efficient way to update lots of records is to use an update statement. There are not many cases where you have to use a cursor rather than an update statement, you just have to get clever about how you write the update statement.

If you posted a snapshot of your SQL you might get some help to achieve what you're after.

Answer 2

I would avoid using a cursor, and work with views or materialized views if possible. Cursors is something that Microsoft doesn't optimize much in SQL Server, because most of the time, you should be using a more general SQL statement (SELECT, INSERT, UPDATE, DELETE) than with a cursor.

If you cannot perform the same end result even with using views or subqueries, you may want to use a temp table or look at improving the data model.

You don't provide much specific information, so all that I can do is give some general tips.

Answer 3

Are you updating the same data that the cursor is operating over?

What type of cursor? forward only? static? keyset? dynamic?

Answer 4

When you need to do a more complex operation to each row than what a simple update would allow you, you can try:

• Write a User Defined Function and use that in the update (probably still slow)
• Put data in a temporary table and use that in an UPDATE ... FROM:

Did you know about the UPDATE ... FROM syntax? It is quite powerful when things get more complex:

UPDATE
  MyTable
SET
  Col1 = CASE WHEN b.Foo = "Bar" THEN LOWER(b.Baz) ELSE "" END,
  Col2 = ISNULL(c.Bling, 0) * 100 / Col3
FROM
  MyTable 
  INNER JOIN MySecondTable AS b ON b.Id = MyTable.SecondId
  LEFT  JOIN ##MyTempTable AS c ON c.Id = b.ThirdId
WHERE
  MyTabe.Col3 > 0
  AND b.Foo NOT IS NULL
  AND MyTable.TheDate > GETDATE() - 10

The example is completely made-up and may not make much sense, but you get the picture of how to do a more complex update without having to use a cursor. Of course, a temp table would not necessarily be required for it to work. :-)

Answer 5

The UPDATE...FROM syntax mentioned above is the prefered method. You can also do a sub query such as the following.

UPDATE t1
SET t1.col1 = (SELECT top 1 col FROM other_table WHERE t1_id = t1.ID AND ...)
WHERE ...

Sometimes this is the only way to do it, as each column update may depend on a differant criteria (or a diferant table), and there may be a "best case" that you want to preserve bysing the order by clause.

Answer 6

Can you post more information about the type of update you are doing?

Cursors can be very useful in the right context (I use plenty of them), but if you have a choice between a cursor and a set-based operation, set-based is almost always the way to go.

But if you don't have a choice, you don't have a choice. Can't tell without more detail.