3

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?

Scott 混合理论
  • 2,263
  • 8
  • 34
  • 59
  • 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. – flindeberg Jan 07 '13 at 10:16
  • @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. – Scott 混合理论 Jan 07 '13 at 10:23
  • Seems very similar to [this](http://stackoverflow.com/questions/14177424/why-does-entityframework-do-select-a-b-c-from-select-a-b-c-from-myview) issue. That would make @Dominik's deleted answer valid. – Ladislav Mrnka Jan 07 '13 at 10:57
  • 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 :) – flindeberg Jan 07 '13 at 12:17
  • @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); – Scott 混合理论 Jan 27 '13 at 14:29

1 Answers1

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
}
Marcel
  • 15,039
  • 20
  • 92
  • 150