Post reply

Running a DELETE Stored Proc but finding degradation in Performance in WHILE loop iterations increase

  • June 6, 2013 at 4:24 pm

    #294914

    1st time/long time,

    Quick question in general. I have a table i'm trying to purge a table. i'm deleting using a WHILE loop with WAIT time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. refer to the following:

    Minute Number|Number of Records Deleted:

    1|162,000

    2|116,000

    3|80,000

    4|72,000

    5|62,000

    6|38,000

    7|38,000

    8|34,000

    9|20,000

    Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly b/c as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.

    thanks in advance

    Dennis

  • June 6, 2013 at 6:39 pm

    #1621945

    Would help to see the code and the actual execution plan. Without at least the code all you are going to get are shots in the dark. The execution plan will tell us more about what is going on.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply