我使用 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 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查询性能优化
发布评论