LINQ to Entity：LINQ查询性能优化

Question

我正在使用EF 4.4.20627.0与MySQL 5.6，MySQL .net connector版本为6.6.4。

我有一个像这样的方法，生成的SQL非常慢（需要超过1分钟）。

    private List<TNews> GetPagedNews(int pagenum, int pagesize,
        AdvSearcherArgs advcArgs, string keyword)
    {
        var dataSrc = _dbRawDataContext.TNews.Where(x => x.Id>0);
        if (!string.IsNullOrWhiteSpace(advcArgs.PMAC))
        {
            dataSrc = dataSrc.Where(m => m.Pmac == advcArgs.PMAC);
        }
        if (!string.IsNullOrWhiteSpace(advcArgs.BegineDate))
        {
            var begin = Convertion.ToDate(advcArgs.BegineDate);
            var end = Convertion.ToDate(advcArgs.EndDate);

            dataSrc = dataSrc.Where(m => m.PmacDT >=begin && m.PmacDT<end);
        }
        dataSrc = dataSrc.OrderByDescending(n => n.PmacDT).Skip(pagenum * pagesize).
          Take(pagesize);

        var cnt = dataSrc.Count();
        SetPagerValues(pagenum, pagesize, cnt);

        return dataSrc.ToList();
    }

生成的SQL语句如下：

SELECT
`Project1`.*
FROM 
(
   SELECT
   `Extent1`.*
   FROM `tnews` AS `Extent1`
   WHERE (`Extent1`.`Id` > 0) 
   AND ((`Extent1`.`PmacDT` >= '2013-01-01 00:00:00 ') AND          
        (`Extent1`.`PmacDT` < '2013-01-07 00:00:00 '))
) AS `Project1`
 ORDER BY 
`Project1`.`PmacDT` DESC LIMIT 0,20
/* Affected rows: 0  Found rows: 20  Warnings: 0  Duration for 1 query: 00:01:30 */

如果我把 order by 和 limit 子句移到括号里面，这个 SQL 语句将会非常快速（花费不到 1 秒）：

SELECT
`Project1`.*
FROM 
(
   SELECT
   `Extent1`.*
   FROM `tnews` AS `Extent1`
   WHERE (`Extent1`.`Id` > 0) 
   AND ((`Extent1`.`PmacDT` >= '2013-01-01 00:00:00 ') AND          
        (`Extent1`.`PmacDT` < '2013-01-07 00:00:00 '))
   ORDER BY 
   `PmacDT` DESC LIMIT 0,20
) AS `Project1`
 /* Affected rows: 0  Found rows: 20  Warnings: 0  Duration for 1 query: 0.000 sec. */

ProjectX和Extent1是什么意思？为什么实体框架不将orderby * limit x,y放在真正的查询之外呢？

这个SQL语句很奇怪，肯定会使查询变得非常缓慢，我永远不会写这样的SQL语句...那么如何让EF生成一个正确的 SQL语句呢？

有什么建议吗？

- Scott 混合理论

---

你正在使用哪个版本的Entity Framework和哪个提供程序？尝试重新启动数据库，然后再次使用第二个查询（快速查询），看看它有多快。 - flindeberg

@flindeberg 文件版本：4.4.20627.0，.net连接器版本为6.6.4，我认为这是EF生成SQL的方式导致了这个问题，我不认为重新启动可以改变任何关于此的状态。所以我认为关键在于我们使用LINQ的方式。 - Scott 混合理论

看起来与这个问题非常相似：http://stackoverflow.com/questions/14177424/why-does-entityframework-do-select-a-b-c-from-select-a-b-c-from-myview。这将使@Dominik的已删除答案有效。 - Ladislav Mrnka

当我在测试数据库（MS-SQL服务器）中运行这些查询时，它们在优化后变成了相同的查询。您使用的是哪个提供程序和数据库？（MySQL、SAP等都称为“.NET连接器”）。如果您正在使用MySQL，如果想要正确使用实体框架，升级可能是一个不错的选择 :) - flindeberg

@flindeberg 我正在使用MySQL，我认为MSSQL不会有这种问题，这个问题是由limit子句引起的（MSSQL没有这个）。 - Scott 混合理论

Answer 1

简单猜测：Count()和ToList()都会执行查询。先使用ToList()，然后使用接收到的列表获取元素数量。

像这样：

private List<TNews> GetPagedNews(int pagenum, int pagesize,
    AdvSearcherArgs advcArgs, string keyword)
{
    var dataSrc = _dbRawDataContext.TNews.Where(x => x.Id>0);
    if (!string.IsNullOrWhiteSpace(advcArgs.PMAC))
    {
        dataSrc = dataSrc.Where(m => m.Pmac == advcArgs.PMAC);
    }
    if (!string.IsNullOrWhiteSpace(advcArgs.BegineDate))
    {
        var begin = Convertion.ToDate(advcArgs.BegineDate);
        var end = Convertion.ToDate(advcArgs.EndDate);

        dataSrc = dataSrc.Where(m => m.PmacDT >=begin && m.PmacDT<end);
    }
    dataSrc = dataSrc.OrderByDescending(n => n.PmacDT).Skip(pagenum * pagesize).
      Take(pagesize);

    var myList = dataSrc.ToList(); //execute the query to an in-memory list

    var cnt = myList.Count(); //get the count from the already exeuted query
    SetPagerValues(pagenum, pagesize, cnt);

    return myList; //return the list
}

- Marcel

---

这解释了2的因素，但不是60左右的因素。 - usr

@Kevin混合理论，你能让它运行并告诉我需要多长时间吗？我很好奇。 - Marcel