CODEWITHSUNDEEP
c#sqllinqienumerable
jorcast
jorcast

Reputation: 15

Update all list items with Linq to SQL

To update the list items use the following code:

foreach (MyObject obj in listObj)
  {
      // modify fields              
      obj.myProperty = value; //any value
      objRepository.Modify(obj);
  }
contex.SubmitChanges();

The problem is that this list contains over 5000 records and the process is very slow. There is a way to update it faster? Directly with sql?

Thank you for your help.

Upvotes: 1

Views: 992

Answers (3)

Levi Botelho
Levi Botelho

Reputation: 25214

Two possibilities in my opinion:

  1. Implement a stored procedure within SQL and access it directly through a straight-up connection to your database. Hard to give you any detailed help without any code, but you should definitely squeeze more performance out of a s.p. than a LINQ to SQL loop because the statement is prepared ahead of time, and so long as you only create the connection once all you are really doing is passing data to the server and letting the server take care of the rest.

  2. Another option would be to modify your repository to accept a cache size as a possible parameter. That is to say, you tell the repository to only affect changes to the database once it has a given number of records in its memory, or once every x milliseconds, which ever comes sooner. This is kind of like how a StringBuilder works, in as much as you can give a StringBuilder an idea of how much space to reserve before you start using it to improve performance.

Upvotes: 1

GKlesczewski
GKlesczewski

Reputation: 302

If you are using a database, your slow performance is caused by 5000+ round trips to the database to save the changes. Each round trip can be expensive. The best bet for saving those changes would be to batch them up, which EF does for you (I assume that's what you are working with).

If you can identify the objects in your list using a single SQL statement, a stored procedure would probably be the fastest way to update that data. But you will run the risk of your object cache falling out of sync with the underlying database if you don't refresh objects afterward. Using Entity Framework, you can import the stored proc into your context, and call that directly.

The other option is what you've written - the foreach loop. I would take a close look at profiling the Modify method of your repository class, if it is something you've written, not something that was generated.

This may be one of those cases where performance needs may out-weigh Model purity. In my experience, EF and other ORM frameworks are great for working on objects individually and small sets, however I found when working with medium to large collections of data (only hundreds of records), it became much more efficient to offload set-based data processing to the database, letting the database engine do what it was optimized for. We are talking reducing run times from several minutes to seconds.

Best of luck!

Upvotes: 1

Wiktor Zychla
Wiktor Zychla

Reputation: 48250

There is an old but great article which shows how to perform batch updates witch linq2sql

http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

Upvotes: 1

Related Questions