Cursor performance

I've got quite big script which uses cursor and in it nested cursors.

I'm facing performance problem, I discovered that last 开发者_如何学Cinstruction in the script which finishes main while loop takes most of the time:

SET STATISTICS TIME ON

FETCH NEXT FROM OldMetaOffer_cursor
INTO @MetaOfferId, @CustomerId, @OfferName, @CheckedOutById, @CheckOutDate, @LastOfferStatusId, @LastCalculationNumber, @CreatedByDisplayName, @CreatedById, @CreateDate, @CoordinatorId, @CoordinatorDate, @CentralAnalystId, @CentralAnalystDate, @DeployUserId, @DeploymentDate, @OwnerId;

SET STATISTICS TIME OFF

SQL Server Execution Times:

• CPU time = 0 ms, elapsed time = 0 ms.
• SQL Server Execution Times:
• CPU time = 4328 ms, elapsed time = 4335 ms.

It takes more than 4 seconds while one step in all takes sth like 4,6 s

Table MetaOffer has got ~150 k rows but I use cursor on 8,5 k rows. (I filter rows at the beginning).

Is there any way to improve that poor performance?

At the beginning of the loop I have:

DECLARE   @MetaOfferId uniqueidentifier     
    , @MetaOfferTypeId int
    , @CustomerId  uniqueidentifier         -- CustomerId
    , @OfferName nvarchar(50)               -- OfferName
    , @CheckedOutById int                   -- CheckOutById
    , @CheckOutDate datetime                -- CheckOutDate
    , @LastOfferStatusId int                -- LastProcessStatusId            
    , @LastCalculationNumber nvarchar(20)   -- LastCalculationNumber
    , @CreatedByDisplayName nvarchar(300)   -- CreatedByDisplayName
    , @CreatedById int                      -- CreatedById
    , @CreateDate datetime                  -- CreateDate
    , @CoordinatorId int                    -- CoordinatorId
    , @CoordinatorDate datetime             -- CoordinatorDate
    , @CentralAnalystId int                 -- CentralAnalystId
    , @CentralAnalystDate datetime          -- CentralAnalystDate
    , @DeployUserId int                     -- DeployUserId
    , @DeploymentDate datetime              -- DeploymentDate
    , @OwnerId int                          -- OwnerId
    -- id statusu po zmapowaniu
    , @NewLastOfferStatusId int             



DECLARE OldMetaOffer_cursor CURSOR FOR
SELECT  MetaOfferId, CustomerId, OfferName, CheckedOutById, CheckOutDate, LastOfferStatusId, LastCalculationNumber, CreatedByDisplayName, CreatedById, 
        CreateDate, CoordinatorId, CoordinatorDate, CentralAnalystId, CentralAnalystDate, DeployUserId, DeploymentDate, OwnerId

FROM [Other].[dbo].[MetaOffer] MO where 
    exists 
    (select * from [Other].[dbo].[OfferHistoryItem] 
    where MetaOfferId = MO.MetaOfferId  and NewStatusId = 9 and DiscountId is null and KoosOfferId is null) 

Maybe there is a problem that on the fetch next this query is made once again ? That results arent buffered anywhere. If so is there any Way I can cashe result of that query and operate on data without making the query on each loop step ?

---

Since you left out the most important part of the problem (what the cursors actually do) I'll simply give you this reference that should hopefully show you how to do your task without cursors. Cursors are extremely poor performers and should not be used if any other alternative exists. I once changed a process from 45 minutes to less than a minute by removing a cursor and another one went from over 24 hours to about 30 minutes. There are very few reasons to use a cursor and many to not use them. They are the technique of last resort, not the first thing you try.

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

---

If you take a look at the documentation for CURSOR for any reasonably current version of SQL Server, and I would think several other RDBMS as well, you'll find it states DO NOT USE CURSOR and an exaplanation that they are included for backward compatibility only.

There are many ways to avoid them, but it does depend on what database server you're working with.

For example, you could use a temp table:

SELECT 
  [ID], [col1], [col2] 
INTO
  #stuff
WHERE
  [col1] LIKE '%something%'

DECLARE @id int, @c1 varchar(32), @c2 varchar(32)

SELECT TOP 1 @id = [ID], @c1 = [col1], @c2 = [col2]
FROM #stuff

WHILE @@ROWCOUNT > 0
BEGIN
  -- do something, say execute a stored procedure, for each row
  EXEC someproc @id, @c1, @c2

  SELECT TOP 1 @id = [ID], @c1 = [col1], @c2 = [col2]
  FROM #stuff
  WHERE [ID] > @id
END

Depending on what the code does, and what DB you're using, there may be much more efficient solutions available, but temp tables will anyway provide massive improvement over cursors.

If on SQL Server, be aware that if you write scalar functions that access tables (in practice that executes SELECT statements or calls something else that does) such a function effectively becomes a cursor - so avoid it whenever you can.

---

Cursors are just plain slow. Nested cursors are even slower. Other than that we would need to see a whole lot more specific information to offer any useful advice.