CODEWITHSUNDEEP
c#sqlperformanceforeach
Masoud Tabatabaei
Masoud Tabatabaei

Reputation: 269

Update records in a loop performance in c# and sql

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?

Upvotes: 3

Views: 1386

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460238

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));
}

Upvotes: 9

Jobo
Jobo

Reputation: 1084

You would have 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.

Upvotes: 2

Related Questions