Updating records in loop performance in c # and sql
I need to update 100 records in a database from my C # application. I used a foreach statement and called storde proc to update each record 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 works fine, except that when it starts, its taking around 00: 00: 00.00123343 to update each record, but as it is about to be updated more and more, it requires more and more. After almost 1000 records, it takes about 00:00:04 seconds to update each record.
I wonder why this is so?
source to share
I am guessing this is just the wrong measurement. Your loop doesn't restart StopWatch
, it just starts and stops it sequentially. Thus, Elapsed
it will always increase.
If you want to measure all the 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));
}
source to share
You will need to call dbWatch.Reset()
at the end of the loop or Restart()
at the beginning if you want to measure the time of each update. I assume its a System.Diagnostics.Stopwatch
. See MSDN - Stopwatch .
source to share