title: Cursor performance
body: <p>I've got quite big script which uses cursor and in it nested cursors.</p>
  
  <p>I'm facing performance problem, I discovered that last instruction in the script which finishes main while loop takes most of the time:</p>
  
  <pre><code>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
  </code></pre>
  
  <h2>SQL Server Execution Times:</h2>
  
  <ul>
  <li>CPU time = 0 ms, elapsed time = 0 ms.</li>
  <li>SQL Server Execution Times:</li>
  <li>CPU time = 4328 ms, elapsed time = 4335 ms.</li>
  </ul>
  
  <p>It takes more than 4 seconds while one step in all takes sth like 4,6 s</p>
  
  <p>Table MetaOffer has got ~150 k rows but I use cursor on 8,5 k rows. (I filter rows at the beginning).</p>
  
  <p>Is there any way to improve that poor performance?</p>
  
  <p>At the beginning of the loop I have:</p>
  
  <pre><code>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) 
  </code></pre>
  
  <p>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 ?</p>