为什么我的EntityFramework插入这么慢？ [英] Why is my EntityFramework insert so slow?

问题描述

我在应用程序中发现了一个瓶颈，作为一个特定实体的插入操作（通过导航属性分为三个表）。类定义如下：

I've found a bottleneck in my application to be the insert operation for one particular entity (into three tables, through navigation properties). The classes are defined as follows:

public class TrackerState
{
    public int Id { get; set; }
    [Index]
    public int TrackerId { get; set; }

    [Index]
    public DateTime DateRecorded { get; set; }
    public DateTime DatePublished { get; set; }
    public DateTime DateReceived { get; set; }
    public LocationStatus LocationStatus { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public double Altitude { get; set; }
    public double Accuracy { get; set; }
    public string Source { get; set; }
    public double Speed { get; set; }
    public double Heading { get; set; }
    public int PrimaryOdometer { get; set; }
    public int SecondaryOdometer { get; set; }
    public int OperationalSeconds { get; set; }
    public virtual IList<AnalogState> AnalogStates { get; set; }
    public virtual IList<DigitalState> DigitalStates { get; set; }
}

public class AnalogState
{
    public int TrackerStateId { get; set; }
    public virtual TrackerState TrackerState { get; set; }
    public int Index { get; set; }
    public int Value { get; set; }
}

public class DigitalState
{
    public int TrackerStateId { get; set; }
    public virtual TrackerState TrackerState { get; set; }
    public int Index { get; set; }
    public bool Value { get; set; }
}

AnalogState和DigitalState类使用TrackerStateId及其Index作为复合主

The AnalogState and DigitalState classes use the TrackerStateId and their Index as a composite primary key.

表目前非常小：

• TrackerStates：2719


• AnalogStates：0


• DigitalStates：32604

当我手动插入表格时，通过SQL管理工作室，操作运行在几秒钟之内。当我通过实体框架插入时，使用以下代码，最多可能需要15秒，所花费的时间取决于跟踪器状态中包含的数字值的数量。具有0数字值的跟踪器状态需要0.1至0.5秒，具有64个数字值的跟踪器状态需要10到15秒。

When I insert into the tables manually, through SQL management studio, the operation runs in a fraction of a second. When I insert through Entity Framework, using the following code, it can take up to 15 seconds, and the amount of time taken is very dependent on the number of digital values included in the tracker state - e.g. a tracker state with 0 digital values takes between 0.1 to 0.5 seconds, and a tracker state with 64 digital values takes between 10 and 15 seconds.

public async Task<int> AddAsync(TrackerState trackerState)
{
    using (var context = ContextFactory.CreateContext())
    {
        context.TrackerStates.Add(trackerState);
        await context.SaveChangesAsync();
        return trackerState.Id;
    }
}

基于此，实体框架似乎在做在背景中很慢，但是我不知道为什么。 0.5秒对于交易来说相当慢，考虑到这将是多久。 15秒就是太慢了。迄今为止我已经尝试过的事情没有成功：

Based on this, it seems like Entity Framework is doing something very slow in the background, but I can't figure out why. 0.5 seconds is pretty slow for a transaction, considering how often this is going to be done. 15 seconds is just too damn slow. Things I have tried so far, to no success:

• 禁用更改跟踪。我没想到这样做太多了，因为我正在为每个事务使用单独的上下文。


• 首先插入跟踪器状态，然后在单独的步骤中输入数字状态。实体框架可能在内部进行。

更新1

我正在使用EntityFramework 6.1.3。我不知道如何查看正在执行的SQL，但是我更新了存储库的存储方法以使用SQL而不是EF：

I'm using EntityFramework 6.1.3. I couldn't figure out how to view the SQL being executed, but I updated the repository's store method to use SQL instead of EF:

context.Database.ExecuteSqlCommand("INSERT INTO DigitalStates ([TrackerStateId], [Index], [Value]) VALUES (@Id, @Index, @Value)",
                    new SqlParameter("Id", entity.Id),
                    new SqlParameter("Index", digital.Index),
                    new SqlParameter("Value", digital.Value));

这部分单独占了大部分时间。插入7个条目需要3秒钟。

This part alone is accounting for the majority of the time. It takes 3 seconds to insert 7 entries.

推荐答案

在一个事务中保存所有的数字状态有很大的区别：

Saving all the digital states in one transaction made a huge difference:

if (trackerState.DigitalStates.Count > 0)
{
    var query = "INSERT INTO DigitalStates ([TrackerStateId], [Index], [Value]) VALUES "
        + string.Join(",", trackerState.DigitalStates.Select(state => String.Format("({0}, {1}, {2})", entity.Id, state.Index, state.Value ? 1 : 0)));
    context.Database.ExecuteSqlCommand(query);
}

由于某些原因，让实体框架自动添加收藏似乎是一个请求数据库中添加的每个数字状态，虽然我的印象是它应该是一个事务，由上下文的 SaveChanges（）方法触发。相对于集合的大小，此修补程序已将其从线性时间更改为大约恒定时间。现在我的下一个问题是，为什么？

For some reason, letting Entity Framework add the collection automatically seemed to be making a request to the database for each digital state that was added, although I was under the impression that it should have been one transaction, triggered by the context's SaveChanges() method. This fix has changed it from linear time to approximately constant time, relative to the size of the collection. Now my next question is, why?

这篇关于为什么我的EntityFramework插入这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！