实体的载入时间超过1秒 [英] Loading instance of entity takes more than 1 second

问题描述

我在EF中遇到了一件有趣的事情。如果我们使用基本实体获得子实体，则加载实体会花费更多时间。我的模型如下所示：

I ran into one interesting thing in EF. If we get child entity using base entity, loading entities takes more time. My model looks like this:

public abstract class BaseDocument
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public abstract class ComplexDocument : BaseDocument
{
    public string AuthorName { get; set; }
}

public abstract class SimpleDocument : BaseDocument
{
    public int Level { get; set; }
}

public abstract class OfficeDocument : ComplexDocument
{
    public string OfficeName { get; set; }
}

public abstract class ClassDocument : SimpleDocument
{
    public string HeadName { get; set; }
}

public class WordDocument : OfficeDocument
{
    public int PagesCount { get; set; }
}

public class ExcelDocument : OfficeDocument
{
    public int SheetsCount { get; set; }
}

public class TextDocument : ClassDocument
{
    public int LinesCount { get; set; }
}

我正在使用 TPT 方法。这是继承树

这是我的上下文类：

I am using the TPT approach. Here is the inheritance tree Here is my context class:

public class Context : DbContext
{
    public Context() : base(@"Server=(localdb)\MSSQLLocalDB;Database=EFSIX;Trusted_Connection=True;")
    {
        Database.CreateIfNotExists();
    }
    public DbSet<BaseDocument> BaseDocuments { get; set; }
    public DbSet<ComplexDocument> ComplexDocuments { get; set; }
    public DbSet<SimpleDocument> SimpleDocuments { get; set; }
    public DbSet<OfficeDocument> OfficeDocuments { get; set; }
    public DbSet<ClassDocument> ClassDocuments { get; set; }
    public DbSet<ExcelDocument> ExcelDocuments { get; set; }
    public DbSet<WordDocument> WordDocuments { get; set; }
    public DbSet<TextDocument> TextDocuments { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       modelBuilder.Entity<BaseDocument>().ToTable("BaseDocuments");
       modelBuilder.Entity<ComplexDocument>().ToTable("ComplexDocuments");
       modelBuilder.Entity<SimpleDocument>().ToTable("SimpleDocuments");
       modelBuilder.Entity<OfficeDocument>().ToTable("OfficeDocuments");
       modelBuilder.Entity<ExcelDocument>().ToTable("ExcelDocuments");
       modelBuilder.Entity<WordDocument>().ToTable("WordDocuments");
       modelBuilder.Entity<ClassDocument>().ToTable("ClassDocuments");
       modelBuilder.Entity<TextDocument>().ToTable("TextDocuments");
    }
    public IQueryable<T> GetEntities<T>() where T : class
    {
        return Set<T>();
    }
}

我正在创建一些数据：

static void CreateTestData()
    {
        using (Context context = new Context())
        {
            for (int i = 0; i < 20; i++)
            {
                ExcelDocument excel = new ExcelDocument()
                {
                    Id = Guid.NewGuid(),
                    AuthorName = $"ExcelAuthor{i}",
                    Name = $"Excel{i}",
                    OfficeName = $"ExcelOffice{i}",
                    SheetsCount = (i + 1) * 10
                };
                context.ExcelDocuments.Add(excel);

                WordDocument word = new WordDocument()
                {
                    Id = Guid.NewGuid(),
                    AuthorName = $"WordAuthor{i}",
                    Name = $"Word{i}",
                    OfficeName = $"WordOffice{i}",
                    PagesCount = (i + 2) * 10
                };
                context.WordDocuments.Add(word);

                TextDocument text = new TextDocument()
                {
                    Id = Guid.NewGuid(),
                    Name = $"Text{i}",
                    LinesCount = (i + 3) * 10,
                    HeadName = $"Head{i}",
                    Level = i + 5
                };
                context.TextDocuments.Add(text);
            }
            context.SaveChanges();
        }
    }

我用两种方法获取<$ c $ db中的c> WordDocument 。其中一个使用 BaseDocument ，另一个使用 WordDocument 。两者都返回20个 WordDocument 的实例：

I made some two methods for getting WordDocument from db. One of them using BaseDocument and another one using WordDocument. Both returns 20 instances of WordDocument:

 static long ReadBaseDoc()
    {
        using (Context context = new Context())
        {
            var words= context.GetEntities<BaseDocument>().Where(e => e.Name.StartsWith("Word"));
            Stopwatch stopwatch = Stopwatch.StartNew();
            var instacnes = excel.ToList();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }
    static long ReadWordDoc()
    {
        using (Context context = new Context())
        {
            var words = context.GetEntities<WordDocument>().Where(e => e.Name.StartsWith("Word"));
            Stopwatch stopwatch = Stopwatch.StartNew();
            var instacnes = words.ToList();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }

我分别对飞蛾方法进行了多次测试，平均方法 ReadWordDoc 花费25ms，方法 ReadBaseDoc 花费52ms（实例相同）。
现在这不是一个太大的问题，但是当我们拥有复杂的继承时，它需要1秒钟以上的时间。我创建了10个类，并从 BaseDocument 继承。之后，我执行了 ReadBaseDoc 和 ReadWordDoc 方法。 ReadWordDoc 花了25毫秒，而 ReadBaseDoc 花了1023毫秒。实例相同，为什么 ReadBaseDoc 需要更多时间？避免EF中此类问题的更好方法是什么？

I tested moth method separately, several times, in average method ReadWordDoc takes 25ms and method ReadBaseDoc takes 52ms (instances are the same ). It's not too big problem now, but when we have complex inheritance it takes more than 1 second. I created 10 classes and inherited from BaseDocument. After that I executed ReadBaseDoc and ReadWordDoc methods. ReadWordDoc took 25ms and ReadBaseDoc took 1023ms. Instances are the same, why ReadBaseDoc takes more time? What is the better way to avoid this kind of problems in EF?

推荐答案

看看此处。有很多方法可以使EF更快，但是在那些复杂的情况下，ORM只会制造出更多无法解决的问题。

Take a look here. There are ways to make EF faster, but in those complex scenarios ORM just creates more problems than it solves.

在您的情况下，一种方法是尝试更改继承

One way in your case would be to try to change the inheritance to TablePerType, MAYBE it will be a little bit faster.

其他方法是找到速度较慢的请求并为其使用Dapper-它将更快。

Other way would be to locate the slow request and use Dapper for them - it will be much faster.

最后一种方法是使用实​​时缓存创建一个存储库，该存储库将整个数据库加载到内存中并保持最新状态-这应该是应用程序中的单例。如果您有多个使用同一数据库的应用程序，则需要连接数据更改触发器。

Last way would be to create a Repository with live cache that loads the full database into memory and keeps it up to date - this should be a singleton in an app. If you have more than one app using the same database, you need to hookup data change triggers.

通常，对于慢速（相对简单）的查询，我想说的是您可以使用Dapper + AutoMapper。保持EF，以便您的数据库与您的类保持同步，但不要依赖它进行查询。

In general, I would say for slow (and relatively simple) queries like yours, use Dapper + AutoMapper. Keep EF so that your database stays synchronized with your classes, but do not rely on it for queries.

如果您真的想坚持使用ORM，我认为您需要切换nHibernate。自己还没有尝试过，但是根据我的阅读，它几乎在所有可能的方面都表现出色，包括性能和启动时间。

If you really want to stick to ORM, I think you need to switch nHibernate. Haven't try it myself, but form what I read, it is superior in almost every possible way, that includes performance and startup time.

这篇关于实体的载入时间超过1秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！