Running DB operation for a long time

Question

I have an operation that I need to run once using c#:

it's much more complicated than the code following but still something like this:

using (DB1DataContext db1 = new DB1DataContext())
{
    int count = db1.tbale1.Count();
    int currentIndex = 0;
    while (currentIndex < count)
    {
        var obj= db1.table1s.Take(1).Skip(currentIndex).FirstOrDefault();
        using (DB2DataContext db2 = new DB2DataContext())
        {
            tableA tb = new table1();
            db2.tableAs.InsertOnSubmit(tb);
            db2.SubmitChanges();
        }
        currentIndex++;
    }    
}

one loop takes about 500 millisecond and the number of loops is about 15000 which means the operation will take about 125 minutes.

will this work or is there some kind of problems??

Edit:As I mentioned before just want to know if there is any time limits that will stop the operation

@EhsanUllah just want to know if there is any time limits that will stop the operation — Alaa Jabre, Jul 13 '13 at 11:44
Do You mean will operation timeout after sometime? See this http://www.dbforums.com/microsoft-sql-server/1115272-how-change-query-timeout.html — Ehsan, Jul 13 '13 at 11:46
It will timeout after the period that is setup in the SSMS. See above forum for details. — Ehsan, Jul 13 '13 at 11:49
See: [Connection timeout](http://stackoverflow.com/questions/12788972/set-database-timeout-in-entity-framework) — Brad Rem, Jul 13 '13 at 11:52

Pleun · Accepted Answer · 2013-07-13T14:29:00.257

0

It will not timeout, since you do one SubmitChanges at a time. Essentially this will do one transaction for each record.

So it will work, but it can be much more efficient.

Move the creation of the second datacontext out of the loop. Why are you even using two?

Move the db2.SubmitChanges(); out of the loop. This however, does mean you will run into timeout problems but it will be faster.

Do a foreach on db1.tbale1 because now your skip + take will make it very inefficient databasewise. You are fetching one record at a time the hard way.

PS - Since you are not getting any specific linq-2-sql benefits in this section, you might as well drop linq in this part and use a SQLBulkCopy and it will be magnitudes faster

edited Jul 13 '13 at 14:29

answered Jul 13 '13 at 14:23

Pleun

8,856
2
30
50

Well, It's not exacltly copying, but I did what you said and it really boost performance, Thank you. – Alaa Jabre Jul 14 '13 at 01:20
You chose SqlBulkCopy or not? – Pleun Jul 14 '13 at 18:27
I had to change a few things before (new views some changes to the code) then I used it, it did enhance the performance, thanks! – Alaa Jabre Jul 15 '13 at 08:51

Running DB operation for a long time

1 Answers1