none
Entity framework Code First optimize / performance issues RRS feed

  • Question

  • Hi,

    I am using Entity framework, Code first approach, and database side we have two tables - FormEntities and FormValues.

    We are retrieving values from the above two tables, the table contains entries around 50 lacks.So while retrieving I am facing performance related issues.

    Code details

    var orderedFormEntitiesQueryable = _context.FormEntities
                    .Include(e => e.FormValues)
                    .Include(e => e.Children)
                    .Include(e => e.Children.Select(c => c.FormValues))
                    .WasCreatedBy(accountId)
                    .IsEntityOfType(returnEntityType)
                    .WhereSearchableFormValuesContains(criteria, searchableFields, applType)
                    .Distinct()
                    .GetByDashboard()
                    .NotcontainWithdrawn()
                    .IsOrderByGridFilters(orderByFormValue, isAscendingOrder);

    var orderedFormEntities = orderedFormEntitiesQueryable.ToList();

    here -  WasCreatedBy,IsEntityOfType,WhereSearchableFormValuesContains,GetByDashboard,NotcontainWithdrawn,IsOrderByGridFilters

    are the extension methods which returns IQueryable<FormEntity>.

    However the above query "orderedFormEntitiesQueryable" takes around 8 to 10 minutes to execute, Can any one tell me how to optimize in Code first approach.

    Regards

    Uday Mahajan


    • Edited by UdayMahajan Thursday, April 13, 2017 11:04 AM
    Thursday, April 13, 2017 11:04 AM

All replies

  • Hi UdayMahajan,

    Based on your description and related code, it seems that it is a complex query, which takes around 8 to 10 minutes to execute.

    Please try to use the following code to get SQL statement and execute the SQL statement in your database, check if it spend much times.

    In addition, please try to use Lazy Loading and check if it is faster. like this:

    var orderedFormEntitiesQueryable = _context.FormEntities
                     .WasCreatedBy(accountId)
                     .IsEntityOfType(returnEntityType)
                     .WhereSearchableFormValuesContains(criteria, searchableFields, applType)
                     .Distinct()
                     .GetByDashboard()
                     .NotcontainWithdrawn()
                     .IsOrderByGridFilters(orderByFormValue, isAscendingOrder);

    For more information about Performance Considerations for EF 4, 5, and 6, please refer to:

    https://msdn.microsoft.com/en-us/library/hh949853(v=vs.113).aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 5:20 AM
    Moderator