locked
Entity Framework performance RRS feed

  • Question

  • Hi,

    I'm having performance issues with the EF. I've got a view made up by more than 20 tables with millions of rows, and I need to retrieve all the results applying 2 filters (just doing a simple SELECT with a WHERE clause using LINQ): a date and a string. I was looking into the SQL Server Profile, and I found out that simple query was being generated in more than 2 minutes, and the execution time was less than a second. In addition, when I tried to execute the generated query by myself in the SSMS, it returned the expected the results very fast. So it seems like the issue is related to the query creation from the EF. However, when I apply other similar filters (for example, another date and another string), it runs very fast, as expected. Any ideas?

    Thanks.

    • Moved by VMazur Tuesday, July 6, 2010 9:48 AM (From:ADO.NET Managed Providers)
    Monday, July 5, 2010 5:43 PM

Answers

  • I have used EF in large application with a complicated model with 200 tables and different type of inheritance going on. i think u essentially need to concentrate on three areas to improve performance.

    1. If data is readonly use MergeOption.NoTracking to avoi change tracking and identity management.

    2. On inheritance strcuture, if you know the type you are going to retrieve use the OfType to improve sql generation. for instance db.People.OfType<Student>().where(s => s.Name.Starts("Zee"));

    3. Avoid Include. It creates somewhat ugly query, returns duplicate data which is fixed at the time of materialization. There are several options that can give u the same semantic like returning data in anonymous type and taking advantage of relationship span.

     


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Saturday, July 10, 2010 9:59 PM
  • Hello,

    There are many performance consideration in Entity Framework: meta data, database connection, views, query, tracking and so on. Accordingly, to improve the performance, we made strategies from these aspects: pre-generate views; use compiled linq query; using notracking merge option for query. please take a look at this article for more informtion on strategies: http://msdn.microsoft.com/en-us/library/cc853327.aspx

    In your case, I suspect the issue happens the first time you execute the query, right? Please post a code snippet here for further discussion.

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Friday, July 9, 2010 10:08 AM

All replies

  • Hello,

    There are many performance consideration in Entity Framework: meta data, database connection, views, query, tracking and so on. Accordingly, to improve the performance, we made strategies from these aspects: pre-generate views; use compiled linq query; using notracking merge option for query. please take a look at this article for more informtion on strategies: http://msdn.microsoft.com/en-us/library/cc853327.aspx

    In your case, I suspect the issue happens the first time you execute the query, right? Please post a code snippet here for further discussion.

    Thanks a lot!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Friday, July 9, 2010 10:08 AM
  • I have used EF in large application with a complicated model with 200 tables and different type of inheritance going on. i think u essentially need to concentrate on three areas to improve performance.

    1. If data is readonly use MergeOption.NoTracking to avoi change tracking and identity management.

    2. On inheritance strcuture, if you know the type you are going to retrieve use the OfType to improve sql generation. for instance db.People.OfType<Student>().where(s => s.Name.Starts("Zee"));

    3. Avoid Include. It creates somewhat ugly query, returns duplicate data which is fixed at the time of materialization. There are several options that can give u the same semantic like returning data in anonymous type and taking advantage of relationship span.

     


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Saturday, July 10, 2010 9:59 PM