优化实体框架查询 [英] Optimize entity framework query

问题描述

我正试图在我自己的时间做一个stackoverflow克隆来学习EF6和MVC5，我目前正在使用OWin进行身份验证。

I'm trying to make a stackoverflow clone in my own time to learn EF6 and MVC5, i'm currently using OWin for authentication.

一切正常我有50-60个问题，我使用了红门数据生成器，并尝试将其高达100万个问题，数以千计的小孩表行无关，只是为了强调ORM。以下是linq如何看起来像

Everything works fine when i have like 50-60 questions, i used Red Gate data generator and try to ramp it up to 1million questions with a couple of thousands of child table rows without relationship just to 'stress' the ORM a bit. Here's how the linq looks like

var query = ctx.Questions
               .AsNoTracking()     //read-only performance boost.. http://visualstudiomagazine.com/articles/2010/06/24/five-tips-linq-to-sql.aspx
               .Include("Attachments")                                
               .Include("Location")
               .Include("CreatedBy") //IdentityUser
               .Include("Tags")
               .Include("Upvotes")
               .Include("Upvotes.CreatedBy")
               .Include("Downvotes")
               .Include("Downvotes.CreatedBy")
               .AsQueryable();

if (string.IsNullOrEmpty(sort)) //default
{
    query = query.OrderByDescending(x => x.CreatedDate);
}
else
{
    sort = sort.ToLower();
    if (sort == "latest")
    {
        query = query.OrderByDescending(x => x.CreatedDate);
    }
    else if (sort == "popular")
    {
        //most viewed
        query = query.OrderByDescending(x => x.ViewCount);
    }
}

var complaints = query.Skip(skipCount)
                      .Take(pageSize)
                      .ToList(); //makes an evaluation..

不用说我得到SQL超时，安装后href =http://miniprofiler.com/ =noreferrer> Miniprofiler ，看看生成的sql语句，这是一个可怕的几百行长。

Needless to say i'm getting SQL timeouts and after installing Miniprofiler, and look at the sql statement generated, it's a monstrous few hundred lines long.

我知道我加入/包括太多的表，但现实生活中有多少项目，我们只需要加入1或2表格？有可能有这样的情况，我们要做这么多的联合数百万行，去存储过程的唯一途径？

I know i'm joining/including too many tables, but how many projects in real life, we only have to join 1 or 2 tables? There might be situations where we have to do this many joins with multi-million rows, is going stored procedures the only way?

如果是这样，EF本身只适合于小规模项目？

If that's the case, would EF itself be only suitable for small scale projects?

推荐答案

我没有看到任何明显错误的LINQ查询（ .AsQueryable（）不应该是强制性的，但是如果你删除它）。当然，不包括不必要的导航属性（每个都添加一个SQL JOIN ），但是如果一切都需要，它应该

I don't see anything obviously wrong with your LINQ query (.AsQueryable() shouldn't be mandatory, but it won't change anything if you remove it). Of course, don't include unnecessary navigation properties (each one adds a SQL JOIN), but if everything is required, it should be OK.

现在，C＃代码看起来很好，现在是时候看到生成的SQL代码了。正如你已经做的那样，第一步是检索执行的SQL查询。有 .Net的做法，对于SQL Server，我个人总是启动 SQL Server概要分析会话。

Now as the C# code looks OK, it's time to see the generated SQL code. As you already did, the first step is to retrieve the SQL query that is executed. There are .Net ways of doing it, for SQL Server I personally always starts a SQL Server profiling session.

一旦你有SQL查询，尝试直接对你的数据库执行，不要忘记包含实际执行计划。这将显示您的查询的绝大部分时间。甚至会指出你是否明显缺少索引。

Once you have the SQL query, try to execute it directly against your database, and don't forget to include the actual execution plan. This will show you exactly which part of your query takes the majority of the time. It will even indicate you if there are obvious missing indexes.

现在的问题是，如果你添加所有这些索引你的SQL Server告诉你他们失踪了不必要。参见例如不要盲目地创建缺失的索引。您必须选择哪些索引应该添加，哪些不应该。

Now the question is, should you add all these indexes your SQL Server tells you they are missing? Not necessarily. See for example Don't just blindly create those missing indexes. You'll have to choose which indexes should be added, which shouldn't.

作为代码优先的方法为您创建索引，我假设这些是仅在主键和外键上的索引。这是一个好的开始，但这还不够。我不知道表中的行数，但是一个明显的索引，只能添加（没有代码生成工具可以做到这一点，因为它与您的业务查询有关），是为例如您在 CreatedDate 列上的索引，因为您按此值排序项目。如果不这样做，SQL Server将不得不对1M行执行表扫描，这当然会在表现方面造成灾难。

As code-first approach created indexes for you, I'm assuming those are indexes on the primary and foreign keys only. That's a good start, but that's not enough. I don't known about the number of rows in your tables, but an obvious index that only you can add (no code-generation tool can do that because it's related to your business queries), is for example an index on the CreatedDate column, as you're ordering your items by this value. If you don't, SQL Server will have to execute a table scan on 1M rows, which will of course be disastrous in terms of performances.

so：

• 尝试删除一些 Include 如果可以


• 查看实际执行计划，以查看其中是您的查询中的性能问题


• 仅添加缺少有意义的索引，取决于您如何订购/过滤您从数据库获取的数据

• try to remove some Include if you can
• look at the actual execution plan to see where is the performance issue in your query
• add only the missing indexes that make sense, depending on how you're ordering/filtering the data you're getting from the DB

这篇关于优化实体框架查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！