Queries Slow Down in a Loop after 20 Iterations

Hi,

Thank you in advance for any help.  I notice this problem in much of my code in my SPs when using loops.  When I have a cursor or any looping mechanism for that matter, that runs code as my example below, the first 30 iterations take secords but then the next, say 300 iterations slow down to minutes or even hours.  I am going to just give an example since this seems to be happening with all my code.  Please do not focus on the syntax, I am just giving an example of the structure.  I am more interested if calling the dynamic queries, transactions, locking, etc could be an issue.  I also tried wrapping all the SQL statements and exec (@SQL) in BEGIN TRAN / COMMIT TRAN but it still slows down.  Also, the reason I am running it in this manner is to cut down the number of transactions at a time the update is performing.  In the example below, this query would update about 5000 records for every loop.


Any ideas?


DELETE FROM mytable WHERE ID = @ID




DECLARE rsDD CURSOR FAST_FORWARD
FOR
      SELECT             DataDate
      FROM             #tblDD WITH (NOLOCK)
      ORDER BY       DataDate    
OPEN rsDD
FETCH rsDD INTO @dd
WHILE (@@FETCH_status = 0 )
     BEGIN

          SET @SQL ='INSERT INTO mytable(ID, DD, value1, value2) '
          SET @SQL = @SQL + 'SELECT ID, Value1, Value2 FROM tblData WITH (NOLOCK) WHERE ID= ' + @ID + ' AND DD = ' + @dd
          Exec (@SQL)

          SET @SQL ='UPDATE mytable  '
          SET @SQL = @SQL + 'SET mytable.value3 = ''' + convert(varchar (20),@yedd) + ''' '
          SET @SQL = @SQL + 'WHERE mytable.ID = ''' + convert(varchar (20),@dd) + ''''
          exec (@SQL)

          SET @SQL ='UPDATE mytable  '
          SET @SQL = @SQL + 'SET mytable.value4 = ''' + convert(varchar (20),@yedd) + ''' '
          SET @SQL = @SQL + 'WHERE mytable.ID = ''' + convert(varchar (20),@dd) + ''''
          exec (@SQL)

          FETCH rsDD INTO @dd
     END
CLOSE rsDD
DEALLOCATE rsDD