title: Poor performance from looping SQL Server Update statement
body: <p>So I have a stored procedure that needs to regularly update a large table with around 70 million records in it. I typically always have followed the standard of doing looping updates to avoid locking any of my other large tables and with that I normally didnt see a performance impact too great.</p>
  
  <p><strong>The Problem:</strong>
  I am seeing my execution time increase by 10 or 20 times when I use looping compared to the original run time without looping logic.</p>
  
  <p>For Example:</p>
  
  <p>If I were to run the below query I it would update 3 million records in around 1.5 minutes.</p>
  
  <pre><code>    UPDATE [db1].[dbo].[Preferences]
      SET LastUpdate = Getdate()
      WHERE
      LastUpdate &gt;= CAST(CONVERT( Varchar(10), DATEADD(day,-1,GETDATE()), 110) as DateTime) 
      AND 
      LastUpdate &lt; CAST(CONVERT( Varchar(10), GETDATE(), 110) as DateTime)
      AND (@PreferenceID is null or @PreferenceID = PreferenceID)
  </code></pre>
  
  <p>This is my regular update statement without a looping mechanism behind it. Basically the parameter @PreferenceID is either supplied with an ID or is left null. Depending on that it will update either all lastUpdate's too today's date or only update one preferenceID. In my test case I am using 1 preferenceID so the @PreferenceID is being populated.</p>
  
  <p>So when I added looping to this statement it went from taking 1.5 minutes to 20 minutes.</p>
  
  <p>Below is the looping statement:</p>
  
  <pre><code>BEGIN
      SET ROWCOUNT  10000
  
          UPDATE [DB1].[dbo].[Preferences]
          SET LastUpdate = Getdate()
          WHERE
          LastUpdate &gt;= CAST(CONVERT( Varchar(10), DATEADD(day,-1,GETDATE()), 110) as DateTime) 
          AND 
          LastUpdate &lt; CAST(CONVERT( Varchar(10), GETDATE(), 110) as DateTime)
          AND (@PreferenceID is null or @PreferenceID = PreferenceID)
  
     WHILE @@ROWCOUNT &gt; 0
  
          UPDATE [DB1].[dbo].[Preferences]
          SET LastUpdate = Getdate()
          WHERE
          LastUpdate &gt;= CAST(CONVERT( Varchar(10), DATEADD(day,-1,GETDATE()), 110) as DateTime) 
          AND 
          LastUpdate &lt; CAST(CONVERT( Varchar(10), GETDATE(), 110) as DateTime)
          AND (@PreferenceID is null or @PreferenceID = PreferenceID)
  
  
  SET ROWCOUNT  0
  END
  </code></pre>
  
  <p>So my core question is why would my execution time increase by so much just by doing a loop of 10k records a time? I can post more detailed info in regards to the table structure but I wasnt sure if this is just a steadfast rule that it will be a performance hit to do a looping update compared to just one full batch update.</p>
  
  <p>Thanks in advance for any advice anyone can provide.</p>