Inserting customer care database analysis detail records

Question

I have written this linq-to-sql query to insert a bunch of customer care database analysis detail records:

DataClasses1DataContext behzad = new DataClasses1DataContext(conn);
            var query = (from p in behzad.Customer_Care_Database_Analysis_TEMPs
                         select p).ToArray();

            for (int i = 0; i < query.Count(); i++)
            {
                Customer_Care_Database_Analysis_DETAIL custom_detail = new Customer_Care_Database_Analysis_DETAIL();
                custom_detail.code_faaliyat = query[i].code_faaliyat.Trim();
                custom_detail.code_markaz = query[i].code_markaz.Trim();
                custom_detail.code_switch = query[i].code_switch.Trim();
                custom_detail.ellate_xata = query[i].ellate_xata.Trim();
                custom_detail.name_faaliyat = query[i].name_faaliyat.Trim();
                custom_detail.name_markaz = query[i].name_markaz.Trim();
                custom_detail.name_switch = query[i].name_Switch.Trim();
                custom_detail.noe_moshtarak = query[i].haq_hoq.Trim();
                custom_detail.shomare_tel = query[i].phone_number.Trim();

                behzad.Customer_Care_Database_Analysis_DETAILs.InsertOnSubmit(custom_detail);
                behzad.SubmitChanges();


            }

The code works, but it can take a long time - more than 10 minutes for my data.

How can I speed this up?

Answer 1

1. With ToArray() you create an in-memory copy of all queried data. If you have 10m records this is incredibly expensive (both for CPU and memory). Avoid that and fetch data from DB each time you use it without needless copies. See also this question post for further details.

   First change it to:

   var query = from p in behzad.Customer_Care_Database_Analysis_TEMPs select p;
   

   Your query does nothing then you may simply drop it if you don't need to perform any filtering.

2. Instead of for use foreach and do not (re)count collection each time. Even if LINQ should be smart enough (it was an array and it may cast and directly access .Length property) it's useless (see also Eric Lippert's answer about purpose) and with a pure enumeration it'll hit database with SELECT COUNT(*) ... multiple times.

   foreach (var temp in behzad.Customer_Care_Database_Analysis_TEMPs)
   {
       var detail = new Customer_Care_Database_Analysis_DETAIL();
       detail.code_faaliyat = temp.code_faaliyat.Trim();
       detail.code_markaz = temp.code_markaz.Trim();
   
       // And so on...
   
       behzad.Customer_Care_Database_Analysis_DETAILs.InsertOnSubmit(detail);
       behzad.SubmitChanges();
   }
   

3. Now you should move SubmitChanges() outside your loop, this is 2nd big step to improve performance. Start searching from this question but there are many (better) posts out there about this topic. See also this question.

   foreach (var temp in behzad.Customer_Care_Database_Analysis_TEMPs)
   {
       var detail = new Customer_Care_Database_Analysis_DETAIL();
       detail.code_faaliyat = temp.code_faaliyat.Trim();
       detail.code_markaz = temp.code_markaz.Trim();
   
       // And so on...
   
       behzad.Customer_Care_Database_Analysis_DETAILs.InsertOnSubmit(detail);
   }
   
   behzad.SubmitChanges();
   

   Performance impact of removing multiple array item deferencing and of introducing enumeration is absolutely negligible in this case.

4. Note that you may also partition your inputs. This is something you should profile for your specific case but you may get better performance working on batches of input data.

   You may find many good implementations of Partition() function (also on Stack Overflow, for example or one of linked duplicates) then here I just provide pseudo-code for that:

   const int batchSize = 100;
   var batches = Partition(behzad.Customer_Care_Database_Analysis_TEMPs, batchSize);
   
   foreach (var batch in batches)
   {
       foreach (var temp in partition)
       {
           var detail = new Customer_Care_Database_Analysis_DETAIL();
           detail.code_faaliyat = temp.code_faaliyat.Trim();
           detail.code_markaz = temp.code_markaz.Trim();
   
           // And so on...
   
           behzad.Customer_Care_Database_Analysis_DETAILs.InsertOnSubmit(detail);
       }
   
       behzad.SubmitChanges();
   }