Why is my SQL Server cursor so slow?

Question

I use the cursor in my stored procedure. It works with a database with a huge amount of data. for each item in the cursor, I perform an update operation. It takes a huge amount of time. Almost 25min. :( .. In any case, can I reduce the time spent on this?

Answer 1

If you need to perform a more complex operation for each line than the fact that a simple update allows you, you can try:

• Write a user-defined function and use it in an update (perhaps still slow)
• Put the data in a temporary table and use this in UPDATE ... FROM:

UPDATE... FROM? , :

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

, , , . , .: -)

Answer 2

. . , , , , .

SQL, , .

Answer 3

, , . - , Microsoft SQL Server, SQL (SELECT, INSERT, UPDATE, DELETE), .

, .

, , , .

Answer 4

, ?

? ? ? ? ?

Answer 5

UPDATE... FROM, , . , .

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

, ( ), " ", , order by.

Answer 6

, ?

( ), , - .

, . .