SQL Server CE 4.0和Entity Framework 4.2的批量插入性能差 [英] Very poor performance for batch insert with SQL Server CE 4.0 and Entity Framework 4.2

问题描述

我使用Entity Framework 4.2（代码优先）将大量数据插入到SQL Server CE 4.0中，与直接SQL插入相比，性能极差。

I'm inserting a lot of data into SQL Server CE 4.0 using Entity Framework 4.2 (code-first), and the performance is abysmal when compared to direct SQL insertion.

该模型非常简单：

public class DocMember
{
    public DocMember() { this.Items = new List<DocItem>(); }

    public int Id { get; set; }

    public string Name { get; set; }
    public string MemberType { get; set; }
    public string AssemblyName { get; set; }

    public virtual IList<DocItem> Items { get; set; }
}

public class DocItem
{
    public int Id { get; set; }
    public DocMember Member { get; set; }
    public string PartType { get; set; }
    public string PartName { get; set; }
    public string Text { get; set; }
}

我有2623 DocMembers 并且总计7747 DocItems 插入，我得到以下执行时间：

I have 2623 DocMembers and a total of of 7747 DocItems to insert, and I'm getting the following execution times:

With SQL: 00:00:02.8
With EF:  00:03:02.2

我可以理解EF有一点开销，但是比SQL简单的 65倍

I can understand there's a bit of overhead with EF, but it is 65 times slower than SQL!

也许在我的代码中有一个问题，但它是非常简单的，我看不到可能是错误的：

Perhaps there's a problem in my code, but it is quite straightforward and I can't see what could be wrong:

    private TimeSpan ImportMembersEF(IList<DocMember> members)
    {
        using (var db = new DocEntities())
        {
            db.Database.CreateIfNotExists();

            var sw = Stopwatch.StartNew();
            foreach (var m in members)
            {
                db.Members.Add(m);
            }

            db.SaveChanges();
            sw.Stop();
            return sw.Elapsed;
        }
    }

我还试图调用每个插入的项目或每100或200个项目的SaveChanges 无效（实际上使其变差）。

I also tried to call SaveChanges for each inserted item, or every 100 or 200 items, to no avail (it actually makes it worse).

有没有办法为了提高性能，或者我必须使用SQL进行批量插入？

Is there a way to improve the performance, or do I have to use SQL for batch inserts?

编辑：为了完整，这里是SQL插入的代码： http://pastebin.com/aeaC1KcB

for completeness, here's the code for the SQL insertion: http://pastebin.com/aeaC1KcB

推荐答案

您可以使用我的SqlCeBulkCopy库来加载批量数据，它模仿SqlBulkCopy api： http://sqlcebulkcopy.codeplex.com

You can use my SqlCeBulkCopy library for loading bulk data, it mimics the SqlBulkCopy api: http://sqlcebulkcopy.codeplex.com

这篇关于SQL Server CE 4.0和Entity Framework 4.2的批量插入性能差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！