title: Slow SQL Updates within a Cursor
body: <p>I've added a new column, <code>packageNo</code>, to my table:</p>
  
  <pre><code> create table Packages(
    id varchar(20) primary key,  -- ok, I know :)
    orderNo uniqueIdentifier not null,
    orderlineNo int not null, 
    packageNo int not null default(0)
  )
  </code></pre>
  
  <p>Now I want to generate the <code>packageNo</code> with the following rules:
   <ul>reset it for each order</ul>
   <ul>ascendantfor order, orderline</ul></p>
  
  <p>My problem is that the script I wrote uses 15 minutes for 26500 rows on my testServer. 
  Here it is:</p>
  
  <pre><code>set NoCount ON
  declare @Counter int
  declare @handledCounter int
  declare @currentorder uniqueIdentifier
  declare @fetchedOrder uniqueIdentifier
  declare @fetchedId varchar(20) -- will using PK speed up things?
  
  declare PackageNo_Cursor cursor  for 
    select orderNo, id from packages order by orderNo, orderlineNo for update of packageNo
  
  open PackageNo_Cursor
  fetch next from PackageNo_Cursor into @fetchedOrder, @fetchedId 
  
  set @currentOrder = @fetchedOrder
  set @counter = 0
  set @handledCounter = 0
  while @@fetch_status = 0
   begin 
       if (@currentOrder &lt;&gt; @fetchedOrder)
         begin  -- reset counter for each order
          set @currentOrder = @fetchedOrder
          set @counter = 0
         end
       set @counter = @counter + 1
       set @handledCounter = @handledCounter +1
       if (@handledCounter % 50 = 0)
        begin
          print 'handled = ' + cast(@handledCounter as varchar) 
        end
     update packages set packageNo = @counter where current of PackageNo_Cursor    
       fetch next from PackageNo_Cursor into @fetchedOrder, @fetchedId 
    end
  
  close PackageNo_Cursor
  deallocate PackageNo_Cursor
  </code></pre>
  
  <p>This should result in:</p>
  
  <pre><code>id   - orderno - lineNo - packageNo (what I need to set)  
  ean1 - guid1   - 1      - 1  
  ean2 - guid1   - 2      - 2   
  ean3 - guid2   - 1      - 1  
  ean15- guid2   - 3      - 2  
  ean15- guid2   - 4      - 3
  </code></pre>
  
  <p>Can I make this run any faster?</p>