Linq to SQL loop performance

OK, I have a bunch of complex logic I need to iterate through. I am calling a web service and getting back results. I take the results and loop through them and make different database calls based on various rules.

The initial code I us开发者_运维知识库ed was the following:

foreach(var obj in MyObjects)
{
    using(var connection = new LinqToSqlDBContext())
    {
        connection.StoredProc1(obj.Name);
        connection.StoredProc2(obj.Name);
        connection.SubmitChanges();
    }
}

I figure this would be more efficient:

using(var connection = new LinqToSqlDBContext())
{
    foreach(var obj in MyObjects)
    {
        connection.StoredProc1(obj.Name);
        connection.StoredProc2(obj.Name);       
    }
    connection.SubmitChanges();
}

Is there a better way I can do this to increase performance? I am using Linq to SQL classes and C# 4.0.

Thanks!

---

Since you are invoking stored-procs, but not performing object changes, the SubmitChanges() is redundant. In normal usage, I would expect this SubmitChanges to be a significant factor, so balancing the number of calls vs the size of individual transactions is important. However, that doesn't apply here, so I would just use:

using(var connection = new LinqToSqlDBContext())
{
    foreach(var obj in MyObjects)
    {
        connection.StoredProc1(obj.Name);
        connection.StoredProc2(obj.Name);       
    }
}

---

yes, I think 2 one is better one because you are updating data just establishing connection where as in 1 one you are dropping and than again connecting so that it add cost of dropping and gaining connection with database.

---

Consider batching the operations performed inside the stored procedures by making the procedures accept a list of names instead of one name. This can be achieved using Table-Valued parameters.

---

As ever - it depends.

In your examples, the first block of code runs the stored procedures and submits changes for each object in separate transactions.

The second block of code, however, executes the stored procedures... and then commits all the changes in one go, in one single transaction.

This may be more efficient, depending on your application.

Best of all, if you can, is to reduce the number of database calls. So if you can rework the stored procedure to take a list of names rather than one single name, and then perform the loop on the database server, you'll most likely see a significant improvement in performance.

Note that there's in practice usually little overhead associated with opening and closing connections as they are typically cached in a connection pool. So, the performance of

using(var connection = new LinqToSqlDBContext())
{
    foreach(var obj in MyObjects)
    {
        connection.StoredProc1(obj.Name);
        connection.StoredProc2(obj.Name);       
    }
}

and

foreach(var obj in MyObjects)
{
    using(var connection = new LinqToSqlDBContext())
    {
        connection.StoredProc1(obj.Name);
        connection.StoredProc2(obj.Name);
    }
}

will most likely be very similar.

---

Well, actually your question has nothing to do with Linq2Sql since you are using stored procedures. You may try to invoke the stored procedures not with the datacontext but using ado.net. Perhaps that has some performance advantages but I am not sure.

First of all, since your two stored procedures have the same arguments, you can combine that into one stored procedure so it might again take a bit of your overhead.

Also do as Jonas H says: provide a list of names and call the SP once.

And perhaps you can do some parrallel processing by invoking the SP's all in a different thread but this is where Marc Gravell might shine some light?