EF, how to increase query performance

Question

i have about 1 million data in my database(MySQL)

and there's a advancedSearch function which is very slow(more than 30 sec), because the SQL EntityFramework generated is not very good, SQL:

SELECT
`Project1`.*
FROM 
(
SELECT
`Extent1`.*
FROM `tnews` AS `Extent1`
 WHERE `Extent1`.`Region` = 'Americas(2)'
 ) AS `Project1`
 ORDER BY 
`Project1`.`PnetDT` DESC LIMIT 0,20

C# function:

    private List<CNNews> AdvancedSearchAndPage(int pagenum, int pagesize,
        AdvSearchArgs advArgs)
    {
        IQueryable<CNNews> result = _dbRawDataContext.CNNews.
            OrderByDescending(n => n.PnetDT);

        if (!string.IsNullOrWhiteSpace(advArgs.Feed))
        {
            result = result.Where(news => news.Feed == advArgs.Feed);
        }

        if (!string.IsNullOrWhiteSpace(advArgs.PNET))
        {
            result = result.Where(news=>news.PNET == advArgs.PNET);
        }

        if (!string.IsNullOrWhiteSpace(advArgs.ProdCode))
        {
            result = (from news in result
                      where news.ProdCode == advArgs.ProdCode
                      select news);
        }

        if (!string.IsNullOrWhiteSpace(advArgs.Code))
        {
            result = (from news in result
                      where news.Code == advArgs.Code
                      select news);
        }

        if (!string.IsNullOrWhiteSpace(advArgs.BegineDate))
        {
            var begin = Convertion.ToDate(advArgs.BegineDate);
            var end = Convertion.ToDate(advArgs.EndDate);

            result = (from news in result
                      where news.PnetDT >= begin && news.PnetDT < end
                      select news);
        }

        if (!string.IsNullOrWhiteSpace(advArgs.Region))
        {
            result = result.Where(x => x.Region == advArgs.RegionName);
        }

        var pagedList = result.
            Skip(pagenum * pagesize).
            Take(pagesize);
        return pagedList.ToList();
    }

if the SQL format like this, it will very fast:

 SELECT
*
FROM `tnews` AS `Extent1`
 WHERE `Extent1`.`Region` = 'Americas(2)'
 ORDER BY 
 `PnetDT` DESC LIMIT 0,20

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

You can execute your own SQL directly off the DbSet and get all the benefits of EF, see

http://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery(v=vs.103).aspx

Also other ways, see these answers for more details

Is it possible to run native sql with entity framework?

edited May 23 '17 at 12:03

Community

1
1

answered Nov 01 '12 at 07:04

Paul Hadfield

6,088
2
35
56

score 0 · Answer 2 · answered Nov 01 '12 at 07:16

0

The LINQ that generated your query looks something like this:

IQueryable<CNNews> result = _dbRawDataContext.CNNews
    .OrderByDescending(n => n.PnetDT)
    .Where(x => x.Region == advArgs.RegionName)
    .Skip(pagenum * pagesize)
    .Take(pagesize);

You tell LINQ to select all items and order them. Then you tell it to take a subset of that. The SQL looks exactly like what you have specified, I would say.

If you rearrange your code somewhat so that the Where() call is before the OrderByDescending() call I think you might get better SQL:

IQueryable<CNNews> result = _dbRawDataContext.CNNews
    .Where(x => x.Region == advArgs.RegionName)
    .OrderByDescending(n => n.PnetDT)
    .Skip(pagenum * pagesize)
    .Take(pagesize);

Also, I don't know if changing order of OrderByDescending() and Skip()/Take() would give different results.

(Disclaimer: I haven't tested it)

answered Nov 01 '12 at 07:16

Torbjörn Kalin

1,976
1
22
31

Yes. What makes you think I haven't? – Torbjörn Kalin Nov 01 '12 at 08:00
Euhm... did you read the question? It certainly sounds like you are asking about performance and the if-clauses have nothing to do with performance. – Torbjörn Kalin Nov 01 '12 at 12:47
that is the problem, u can have a try. look the downstairs answer – Scott 混合理论 Nov 01 '12 at 12:54
Try this: move the `OrderByDescending()` from the top of the method to the bottom: `IQueryable result = _dbRawDataContext.CNNews;` and `var pagedList = result.OrderByDescending().Skip(...`. This, I think, will improve your performance (and that is what I tried to say in my answer). – Torbjörn Kalin Nov 01 '12 at 13:32

EF, how to increase query performance

2 Answers2