linq to entity: linq query performance optimization

Question

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

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

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

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

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

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??

any suggestions?

• flindeberg: Which version of entity framework and which provider are you using? Try restarting the database and use the second query again (the fast one) and see how quick it is.
• Scott 混合理论: @flindeberg file version:4.4.20627.0, .net connector version 6.6.4, i think it is the way the EF grnerate SQL cause this problem, i don't think restart can change any state about this. so i think the point is the way we use LINQ.
• Ladislav Mrnka: Seems very similar to this issue. That would make @Dominik's deleted answer valid.
• flindeberg: When I run those queries in a test-db (MS-SQL server) they become the same query after optimization. Which provider and DB are you using? (the MySQL, the SAP, etc are all called .NET Connector) If you are using MySQL it might be a good idea to upgrade if you want to use entity framework properly :)
• Scott 混合理论: @flindeberg i'm using mysql, i think MSSQL won't have this kind issue, this issue is caused by the limit clause(MSSQL doesn't have this)

Answer 1

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.

Like so:

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
}

• usr: This explains a factor of 2 but not a factor of 60 or so.
• Marcel: @Kevin混合理论 Can you please let it run and tell the time it takes? I'm curious.