Update records in a loop performance in c# and sql

c#, foreach, performance, sql

Solution

I assume this is just an incorrect measurement. Your loop does not restart the `StopWatch`, it just starts and stops it consecutively. So `Elapsed` will always increase.

If you want to measure the entire time:

dbWatch.Start();
foreach (var record in recordList)
{  
    DbService.UpdateRecord(Id, ProcessDate, MessageID, task.Result.Email.TryTimes);
}
dbWatch.Stop();
LogMessage(string.Format("All updates took:{0}",dbWatch.Elapsed));

If you want to measure the time for each iteration use `StopWatch.Restart`:

foreach (var record in recordList)
{  
    dbWatch.Restart();
    DbService.UpdateRecord(Id, ProcessDate, MessageID, task.Result.Email.TryTimes);
    dbWatch.Stop();
    LogMessage(string.Format("Single database row update took:{0}",dbWatch.Elapsed));
}

Problem

I need to update 100 records in database from my application in C#. I have used a foreach statement and called a stored procedure to update each records like this: ``` foreach (var record in recordList) { dbWatch.Start(); DbService.UpdateRecord(Id, ProcessDate, MessageID, task.Result.Email.TryTimes); dbWatch.Stop(); LogMessage(string.Format("Single database row update toke:{0}",dbWatch.Elapsed)); } ``` Everything is working fine except that when its starts, it's taking about 00:00:00.00123343 to update each record, but as its going to update more and more, it takes more and more. After almost 1000 records it take about 00:00:04 seconds to update each records. I'm wondering why it's like that?

Original source