How to optimise LinqToSQL in c#

Question

hi, i am attempting to update approximately 150,000 records in a table in SQL 2005 using linq2sql. When it comes to xx.SubmitChanges() it is taking about 45 minutes.

I am running sql as a local instance on a quad core pc.

Does anyone know why this is taking so long? or is that normal?

Code Sample:

var y = db.x.Where(j => j.NumberOfOrders > 0).Select(k => k);

foreach (var item in y)
{
    try
    {
        item.k = "bla";
    }
    catch (Exception ex)
    {
        //
    }
}

db.SubmitChanges();

Answer 1

this will take much time there is no bulk insert in linq to sql.In this case it is inserting one by one record to your context and finally its goes and save in your database when you call SubmitChanges().So it is taking time.

If you have big record like 150,000 records. Better to use Bulk insert in sql.This will take only fraction of seconds only to insert .

Answer 2

You don't need the Select() because it is projecting the same thing as the Where()

And there's no need for using try-catch for just a simple assigment.

But definitely the best thing to do is the Bulk Insert Stuff that anishmarokey is talking about

Answer 3

A large update such as this would be done with an UPDATE query (or stored proc) that can use the database to do the heavy lifting (and transaction management/consistency). I know you're simplifying the example, but what about something like this:

string CommandText = "UPDATE x SET k = @k WHERE NumberOfOrders > 0";
using (SqlConnection conn = new SqlConnection(My.Settings.DatabaseConnection)) {
    using (SqlCommand cmd = new SqlCommand(CommandText, conn)) {
        cmd.Parameters.AddWithValue("@k", "bla");
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}