LINQ到实体：LINQ查询性能优化 [英] linq to entity: linq query performance optimization

问题描述

我使用 EF 4.4.20627.0 与的MySQL 5.6 ， MySQL的.NET连接器版本6.6.4

i'm using EF 4.4.20627.0 with MySQL 5.6, MySQL .net connector version 6.6.4

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

i have a method like this, which generated sql is very very slow(needs more than 1 min)

    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();
    }

的

generaed SQL是这样的：

the generaed sql like this:

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 */

如果我移动订单和限制条款列入梅开二度，这将SQL 非常快（成本低于 1秒）：

if i move order by and limit clause into the brace, this sql will be very fast(costs less than 1 sec):

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 是什么意思？
，为什么实体框架，不要把排序依据*限的x，y 真正的查询外??

what does ProjectX and Extent1 mean? and why entity-framework don't put the orderby * limit x,y outside the real query??

在SQL是怪异，绝对使查询速度很慢，我永远不会写SQL这样的...那么如何使EF生成一个正确 SQL？

the sql is weird and definitely make the query very slow, i will never write sql like that...So how to make EF generate a CORRECT sql??

任何建议

推荐答案

只是一个快速的猜测：伯爵（）和了ToList（）都执行查询。 。做了ToList（）第一，并使用所接收到的列表来获取元素的计数

Just a quick guess: The Count() and ToList() both execute the query. Do the ToList() first, and use the received list to get the count of elements.

像这样：

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
}

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