使用LINQ到SQL非常缓慢插入过程 [英] Very slow insert process using Linq to Sql

问题描述

我插入大量采用LinqToSql从C＃至2008年记录的SqlServer数据库表达。它看起来像插入在这很慢。以下是代码片段。

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.

public void InsertData(int id)
{

  MyDataContext dc = new MyDataContext();

  List<Item> result = GetItems(id);

  foreach (var item in result)
  {
    DbItem dbItem = new DbItem(){ItemNo = item.No, ItemName=item.Name};
    dc.Items.InsertOnSubmit();
  }

  dc.SubmitChanges();
}

难道我做错了什么？或者使用LINQ to插入大量的记录是一个不错的选择。

Am I doing anything wrong? Or using Linq to insert large number of records is a bad choice?

更新：感谢所有的答案
@p .campbell：很抱歉的记录数，这是一个笔误，实际上它是10万左右也记录到的范围200K为好。

Update: Thanks for all the answers. @p.campbell: Sorry for the records count, it was a typo, actually it is around 100000. Records also range till 200k as well.

根据所有的建议我搬到这个操作分成部分（也有需求变更和设计决定）和检索小块数据，并将其插入到数据库中，当它谈到。我已经把在线程运行此InsertData（）方法，而现在使用 SmartThreadPool 创造的25池线程做同样的操作。在这种情况下，我插入一次只有100个记录。现在，当我尝试这样做使用LINQ或SQL查询它并没有在拍摄时间方面的任何差异。

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 SmartThreadPool 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.

按我的要求，这个操作计划运行每小时取周边的4K-6K的用户记录。所以，现在我每天汇集的用户数据（检索和插入DB）经营为一体的任务，分配给一个线程。现在，这整个过程大约45分钟左右25万的记录。

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.

有没有更好的办法做到这样的任务？或者，任何人都可以建议我怎么能改善这个过程？

Is there any better way to do this kind of task? Or can anyone suggest me how can I improve this process?

推荐答案

对于在插入数据的巨量到SQL oner

LINQ的或的SqlCommand，的neither是专为大容量复制数据到SQL 。

Linq or SqlCommand, neither are designed for bulk copying data into SQL.

您可以使用 SqlBulkCopy类提供为bcp实用工具管理访问批量加载数据到SQL来自几乎任何数据源。

You can use the SqlBulkCopy class which provides managed access to the bcp utility for bulk loading data into Sql from pretty much any data source.

SqlBulkCopy类可以用来只写到SQL Server表中的数据。但是，数据源并不局限于SQL Server的;任何数据源可以使用，只要该数据可以被加载到一个DataTable实例或具有IDataReader的实例读

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

< STRONG>性能对比

SqlBulkCopy的是目前为止最快的，即使在装载从一个简单的CSV文件中的数据。

SqlBulkCopy is by far the fastest, even when loading data from a simple CSV file.

LINQ的只是生成SQL中的插入语句的负载并将它们发送到您的SQL Server。这绝不是您使用Ad-hoc查询与的SqlCommand 不同于。的SqlCommand的性能与LINQ的几乎是相同的。

Linq will just generate a load of Insert statements in SQL and send them to your SQL Server. This is no different than you using Ad-hoc queries with SqlCommand. Performance of SqlCommand vs. Linq is virtually identical.

的证明

（ SQL Express的2008年，.NET 4.0）

(SQL Express 2008, .Net 4.0)

的使用SqlBulkCopy 的

使用使用SqlBulkCopy加载从CSV文件100000行（包括加载数据）

Using SqlBulkCopy to load 100000 rows from a CSV file (including loading the data)

using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=EffectCatalogue;Data Source=.\\SQLEXPRESS;"))
{
    conn.Open();
    Stopwatch watch = Stopwatch.StartNew();

    string csvConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\data\\;Extended Properties='text;'";
    OleDbDataAdapter oleda = new OleDbDataAdapter("SELECT * FROM [test.csv]", csvConnString);
    DataTable dt = new DataTable();
    oleda.Fill(dt);

    using (SqlBulkCopy copy = new SqlBulkCopy(conn))
    {
        copy.ColumnMappings.Add(0, 1);
        copy.ColumnMappings.Add(1, 2);
        copy.DestinationTableName = "dbo.Users";
        copy.WriteToServer(dt);
    }
    Console.WriteLine("SqlBulkCopy: {0}", watch.Elapsed);
}

的的SqlCommand 的

using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDb;Data Source=.\\SQLEXPRESS;"))
{
    conn.Open();
    Stopwatch watch = Stopwatch.StartNew();
    SqlCommand comm = new SqlCommand("INSERT INTO Users (UserName, [Password]) VALUES ('Simon', 'Password')", conn);
    for (int i = 0; i < 100000; i++)
    {
        comm.ExecuteNonQuery();
    }
    Console.WriteLine("SqlCommand: {0}", watch.Elapsed);
}

的 LinqToSql 的

using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDb;Data Source=.\\SQLEXPRESS;"))
{
    conn.Open();
    Stopwatch watch = Stopwatch.StartNew();
    EffectCatalogueDataContext db = new EffectCatalogueDataContext(conn);
    for (int i = 0; i < 100000; i++)
    {
        User u = new User();
        u.UserName = "Simon";
        u.Password = "Password";
        db.Users.InsertOnSubmit(u);
    }
    db.SubmitChanges();
    Console.WriteLine("Linq: {0}", watch.Elapsed);
}

的结果的

SqlBulkCopy: 00:00:02.90704339
SqlCommand: 00:00:50.4230604
Linq: 00:00:48.7702995

这篇关于使用LINQ到SQL非常缓慢插入过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！