Speed up large Linq to Entities call MVC3

Question

Below is the code I am using and the database table it is pulling from has about 92000 records in it. The way it is pulling right now it is pulling all 92000 records then doing the filtering. What I am looking to do is the filtering on the initial pull from the DB so that it does not take aproximately 40 seconds to load the page. This is something I am still new at so I am lost as to how to do this and make it work with my view

public ViewResult Makes()
{
    var items = (from item in DBCacheHelper.recallslist
                 orderby item.MFGTXT ascending
                 select item.ToDomainRecall()).GroupBy(item => item.MFGTXT).Select(grp => grp.First());


    return View(items);
}

public static IEnumerable<Recall> recallslist
{
    get
    {
        if (c["GetAllRecalls"] == null)
        {
            c.Insert("GetAllRecalls", GetAllRecalls());
            return (IEnumerable<Recall>)c["GetAllRecalls"];
        }
        else
        {
            return (IEnumerable<Recall>)c["GetAllRecalls"];
        }
    }
}



public static IEnumerable<Recall> GetAllRecalls()
{
    using (DealerContext context = new DealerContext())
    {
        var items = from item in context.recalls.ToList<Recall>()
                     select item.ToDomainRecall();
        return items.ToList<Recall>();
    }
}


SELECT 
[Extent1].[RecallsId] AS [RecallsId], 
[Extent1].[RECORD_ID] AS [RECORD_ID], 
[Extent1].[CAMPNO] AS [CAMPNO], 
[Extent1].[MAKETXT] AS [MAKETXT], 
[Extent1].[MODELTXT] AS [MODELTXT], 
[Extent1].[YEARTXT] AS [YEARTXT], 
[Extent1].[MFGCAMPNO] AS [MFGCAMPNO], 
[Extent1].[COMPNAME] AS [COMPNAME], 
[Extent1].[MFGNAME] AS [MFGNAME], 
[Extent1].[BGMAN] AS [BGMAN], 
[Extent1].[ENDMAN] AS [ENDMAN], 
[Extent1].[RCLTYPECD] AS [RCLTYPECD], 
[Extent1].[POTAFF] AS [POTAFF], 
[Extent1].[ODATE] AS [ODATE], 
[Extent1].[INFLUENCED_BY] AS [INFLUENCED_BY], 
[Extent1].[MFGTXT] AS [MFGTXT], 
[Extent1].[RCDATE] AS [RCDATE], 
[Extent1].[DATEA] AS [DATEA], 
[Extent1].[RPNO] AS [RPNO], 
[Extent1].[FMVSS] AS [FMVSS], 
[Extent1].[DESC_DEFECT] AS [DESC_DEFECT], 
[Extent1].[CONEQUENCE_DEFECT] AS [CONEQUENCE_DEFECT], 
[Extent1].[CORRECTIVE_ACTION] AS [CORRECTIVE_ACTION], 
[Extent1].[NOTES] AS [NOTES], 
[Extent1].[RCL_CMPT_ID] AS [RCL_CMPT_ID]
FROM [dbo].[Recalls] AS [Extent1]

Update:

Ultimately I would like to only pull records from the Recalls Table where the MFGTXT is equal to the MakeName in the AutoMake Table

public class AutoMake
{
    [Key]
    public int MakeID { get; set; }
    public string MakeName { get; set; }

public AutoMake ToDomainAutoMakes()
{
    return new AutoMake
    {
        MakeID = this.MakeID,
        MakeName = this.MakeName
    };
}

}

public class Recall
{
    [Key]
    public int RecallsId { get; set; }
    public string RECORD_ID { get; set; }
    public string CAMPNO { get; set; }
    public string MAKETXT { get; set; }
    public string MODELTXT { get; set; }
    public string YEARTXT { get; set; }
    public string MFGCAMPNO { get; set; }
    public string COMPNAME { get; set; }
    public string MFGNAME { get; set; }
    public string BGMAN { get; set; }
    public string ENDMAN { get; set; }
    public string RCLTYPECD { get; set; }
    public string POTAFF { get; set; }
    public string ODATE { get; set; }
    public string INFLUENCED_BY { get; set; }
    public string MFGTXT { get; set; }
    public string RCDATE { get; set; }
    public string DATEA { get; set; }
    public string RPNO { get; set; }
    public string FMVSS { get; set; }
    public string DESC_DEFECT { get; set; }
    public string CONEQUENCE_DEFECT { get; set; }
    public string CORRECTIVE_ACTION { get; set; }
    public string NOTES { get; set; }
    public string RCL_CMPT_ID { get; set; }

    public Recall ToDomainRecall()
    {
        return new Recall
        {
            RECORD_ID = this.RECORD_ID,
            CAMPNO = this.CAMPNO,
            MAKETXT = this.MAKETXT,
            MODELTXT = this.MODELTXT,
            YEARTXT = this.YEARTXT,
            MFGCAMPNO = this.MFGCAMPNO,
            COMPNAME = this.COMPNAME,
            MFGNAME = this.MFGNAME,
            BGMAN = this.BGMAN,
            ENDMAN = this.ENDMAN,
            RCLTYPECD = this.RCLTYPECD,
            POTAFF = this.POTAFF,
            ODATE = this.ODATE,
            INFLUENCED_BY = this.INFLUENCED_BY,
            MFGTXT = this.MFGTXT,
            RCDATE = this.RCDATE,
            DATEA = this.DATEA,
            RPNO = this.RPNO,
            FMVSS = this.FMVSS,
            DESC_DEFECT = this.DESC_DEFECT,
            CONEQUENCE_DEFECT = this.CONEQUENCE_DEFECT,
            CORRECTIVE_ACTION = this.CORRECTIVE_ACTION,
            NOTES = this.NOTES,
            RCL_CMPT_ID = this.RCL_CMPT_ID

        };
    }
}

Can you show how you filter the records? – Eranga Jun 04 '12 at 02:53 — Eranga, Jun 04 '12 at 02:53
me thinks you need a foreign key... – RPM1984 Jun 04 '12 at 05:32 — RPM1984, Jun 04 '12 at 05:32

score 0 · Answer 1 · edited May 23 '17 at 10:24

Looks like your DBaccess is done in the call to DBCacheHelper.recallslist.

You need to edit the sql that runs from/in this function.

As Eranga pointed out, you don't show how you are filtering the large number down to a smaller number of records. I assume you want 20 or 100 at a time? If so, please see the accepted answer here:

efficient way to implement paging

Specifically, this part which shows how to only retrieve rows x to y (where x = @p0 + 1 AND y = @p0 + @p1):

SELECT [t1].[CodCity], 
    [t1].[CodCountry], 
    [t1].[CodRegion], 
    [t1].[Name],  
    [t1].[Code]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]) AS [ROW_NUMBER], 
        [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
    FROM [dbo].[MtCity] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

score 0 · Accepted Answer · answered Sep 10 '12 at 18:22

If you want to add server side filtering outside of your repository methods, you need to return your types as IQueryable rather than IEnumerable and not call .ToList, .AsEnumerable, or any other method that would cause .GetEnumerator to be called. Additionally, your cast `(IEnumerable)c["GetAllRecalls"];' forces LINQ to Objects to be used for subsequent requests rather than retaining the expression tree and using Entity Framework. That being said, you may need to move your call to ToDomainRecall method to after the additional filter is applied as well because that can't be translated to your database. Here are some of the changes you would need to make:

public ViewResult Makes()
{
    var items = (from item in DBCacheHelper.recallslist
                 orderby item.MFGTXT ascending
                 select item.ToDomainRecall()).GroupBy(item => item.MFGTXT).Select(grp => grp.First());


    return View(items);
}

public static IQueryable<Recall> recallslist
{
    get
    {  
        if (c["GetAllRecalls"] == null)
        {
            c.Insert("GetAllRecalls", GetAllRecalls(context));
        }
        return c["GetAllRecalls"];

    }
}



public static IQueryable<Recall> GetAllRecalls(DealerContext context)
{
        var items = context.recalls;
        return items;
}

Speed up large Linq to Entities call MVC3

2 Answers2