Databases
StackOverflow2013
Postsdbo
POVery slow insert process using Linq to Sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVery slow insert process using Linq to Sql
    primarykey
    data
    text
    <p>I'm inserting large number of records using LinqToSql from C# to SqlServer 2008 express DB. It looks like the insertion is very slow in this. Following is the code snippet.</p> <pre><code>public void InsertData(int id) { MyDataContext dc = new MyDataContext(); List&lt;Item&gt; result = GetItems(id); foreach (var item in result) { DbItem dbItem = new DbItem(){ItemNo = item.No, ItemName=item.Name}; dc.Items.InsertOnSubmit(); } dc.SubmitChanges(); } </code></pre> <p>Am I doing anything wrong? Or using Linq to insert large number of records is a bad choice?</p> <p><strong>Update: Thanks for all the answers.</strong> @p.campbell: Sorry for the records count, it was a typo, actually it is around 100000. Records also range till 200k as well. </p> <p>As per all the suggestions I moved this operation into parts (also a requirement change and design decision) and retrieving data in small chunks and inserting them into database as and when it comes. I've put this InsertData() method in thread operation and now using <a href="http://www.codeproject.com/KB/threads/smartthreadpool.aspx" rel="noreferrer">SmartThreadPool</a> for creating a pool of 25 threads to do the same operation. In this scenario I'm inserting at a time only 100 records. Now, when I tried this with Linq or sql query it didn't make any difference in terms of time taken.</p> <p>As per my requirement this operation is scheduled to run every hour and fetches records for around 4k-6k users. So, now I'm pooling every user data (retrieving and inserting into DB) operation as one task and assigned to one thread. Now this entire process takes around 45 minutes for around 250k records.</p> <p>Is there any better way to do this kind of task? Or can anyone suggest me how can I improve this process?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJPReddy
    1. USJPReddy
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVery slow insert process using Linq to Sql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POVery slow insert process using Linq to Sql
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POVery slow insert process using Linq to Sql
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhow many records, and how long does the operation take? What datatypes are being used here?
      singulars
      1. POVery slow insert process using Linq to Sql
      1. USp.campbell
    2. COMore than 1000000 records and mostly string datatypes but not more than 10 fields.
      singulars
      1. POVery slow insert process using Linq to Sql
      1. USJPReddy
    3. COA million inserts will take time, no matter what. I suspect if you copy the generated SQL statements, all 1 million of them, and run them ad-hoc, that you won't see much of a perf difference from Management Studio!
      singulars
      1. POVery slow insert process using Linq to Sql
      1. USp.campbell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload