none
How to reduce Entity framework slow startup time and first call time? RRS feed

  • Question

  • Hello all,

        I am using Entity framework 5.0 with DB first in my WCF services. For slow startup I used pre-generated views. Then I optimized my queries, used eager loading where needed, used compiled queries for some complex queries. I also tried profiling queries using Entity framework profiler and methods using "New Relic". The startup time and time delays in executing queries reduced a lot.

    But, it is not satisfying. A method just getting 10-20 records from two related tables takes 12-15 seconds generally for first time, and worst case goes to 25-30 seconds. Later calls give response within 1 second. 

    Is there any way to reduce the first call time? 

    What is meant by first call? Because the time delay doesn't happens once a day or when Application pool refreshes. But it happens after even a small gap of 5-10 minutes. When I call a method for first time in a day, it takes 20 seconds, I call it again just after getting response from previous calls, and it takes less than 1 second. If i repeat calling the method just after getting response from previous call, it takes less than 1 second for each call. Now I give a pause for say,  5 mins., and call that method again, and it will take 5-6  secs. 

    Why this happens?

    If an EF query takes comparatively more time for first time than later calls. So why it takes more time in that much small gap. If i call a query for the first time in a day, and if it takes 20 secs and then for later calls (if it take 1 sec) whether I call them after 5 mins or after 5 hrs, it should take 1 sec everytime.

    Is it related to context caching? What can i do to keep that cache live longer?

    Is there any config settings, to prevent this from happening? 

    Please tell me anything relevant you know.

    Thanks


    Deepti

    • Edited by deepti_M Monday, April 8, 2013 12:21 PM
    Monday, April 8, 2013 6:03 AM

Answers

  •  The program is going to take the penalty on IIS for the first time execution. There is no way around it. After things like compiled queries and compiled views are executed the first time, then they stay in static memory on IIS, which makes execution faster the next time they are used.  They will stay in static memory until the ASP.NET Worker thread is recycled by doing an IISReset as an example or something else forces the ASP.NET Worker thread to recycle.

    Also if you are using the "using" statement to open/close connections to the database using EF, then you are again taking a hit on execution time. You should be explicit opening/closing the connection and not use the "using" statement.

    <http://msdn.microsoft.com/en-us/library/cc853327.aspx>

    • Marked as answer by deepti_M Friday, April 12, 2013 4:37 AM
    Monday, April 8, 2013 1:18 PM

All replies

  •  The program is going to take the penalty on IIS for the first time execution. There is no way around it. After things like compiled queries and compiled views are executed the first time, then they stay in static memory on IIS, which makes execution faster the next time they are used.  They will stay in static memory until the ASP.NET Worker thread is recycled by doing an IISReset as an example or something else forces the ASP.NET Worker thread to recycle.

    Also if you are using the "using" statement to open/close connections to the database using EF, then you are again taking a hit on execution time. You should be explicit opening/closing the connection and not use the "using" statement.

    <http://msdn.microsoft.com/en-us/library/cc853327.aspx>

    • Marked as answer by deepti_M Friday, April 12, 2013 4:37 AM
    Monday, April 8, 2013 1:18 PM
  • Hello darnold,

    thanks for your reply. Yes, I too think that there is no way to reduce the time taken by IIS when it recycles.

    Also, I think there is no way to reduce the first call time in Entity framework, if you have already applied pre-generated views, compiled queries and optimized queries. Isn't it?

    I tried explicit opening/closing the connection and not use the "using" statement, but doesn't seem it made any significance difference. Although I implemented it.

     I am having a case where the entities are related as Merchant --> (each merchant can have multiple stores) Stores ----> ( a store can have more than one Working Days) WorkingDays. Now I have to fetch this related record. Currently there is only one merchant, but it contains 81 stores and each store have atleast one working day, while 6-7 store have 2 working day record.

    To get this record my EF query looks like :

     var allMerchants = from m in context.Merchants
                     .Include("Stores.WorkingDays")                
                     select m;

    I have written this query as compiled query, which for the first time takes 10-12 seconds, and for later calls it takes 3-4 seconds. Why is it taking that much time for later calls? Is there any way to reduce the time taken in later calls? Is I am doing something wrong ?

    Thanks.


    Deepti

    Tuesday, April 9, 2013 12:31 PM
  • On 4/9/2013 8:31 AM, deepti_M wrote:

    Hello darnold,

    thanks for your reply. Yes, I too think that there is no way to reduce the time taken by IIS when it recycles.

    Also, I think there is no way to reduce the first call time in Entity framework, if you have already applied pre-generated views, compiled queries and optimized queries. Isn't it?

    I tried explicit opening/closing the connection and not use the "using" statement, but doesn't seem it made any significance difference. Although I implemented it.

      I am having a case where the entities are related as Merchant --> (each merchant can have multiple stores) Stores ----> ( a store can have more than one Working Days) WorkingDays. Now I have to fetch this related record. Currently there is only one merchant, but it contains 81 stores and each store have atleast one working day, while 6-7 store have 2 working day record.

    To get this record my EF query looks like : var allMerchants = from m in context.Merchants
                      .Include("Stores.WorkingDays")
                      select m;

    I have written this query as compiled query, which for the first time takes 10-12 seconds, and for later calls it takes 3-4 seconds. Why is it taking that much time for later calls? Is there any way to reduce the time taken in later calls? Is I am doing something wrong ?

    Thanks.

    You should only bring back the data that is needed. The above query may need to be looked at, like bringing back Merchants only. After doing a selection process on which  merchant is to be used, then do a query on the merchant and only bringing back Stores.WorkingDays that are linked to the merchant.
     <copied>

    Return the correct amount of data

    In some scenarios, specifying a query path using the Include method is much faster because it requires fewer round trips to the database. However, in other scenarios, additional round trips to the database to load related objects may be faster because the simpler queries with fewer joins result in less redundancy of data. Because of this, we recommend that you test the performance of various ways to retrieve related objects. For more information, see Shaping Query Results (Entity Framework).

    To avoid returning too much data in a single query, consider paging the results of the query into more manageable groups. For more information, see How to: Page Through Query Results (Entity Framework).

    <end copy>

    Tuesday, April 9, 2013 8:08 PM

  • Where I got the paragraph I copied from was in the link below in the prior post. Even when using ADO.NET with T-SQL, SQL command objects and custom objects for data transportation, one makes the determination to bring back the data all at once,  or does one only bring back the needed data as a subset of data for speed.

    Sometimes, you have to give the illusion of speed by not querying for everything in one-shot and bring back data in lessor amounts for speed.

    http://msdn.microsoft.com/en-us/library/cc853327.aspx

    Wednesday, April 10, 2013 3:07 AM
  • Hi Darnold,

    Thanks for your reply. In the query (I told you in previous reply), I tried selecting only required columns. I manipulated my query like this : 

    var allmerchants = from m in context.Merchants
    .Include("Stores.WorkingDays")                                   
    select new
    {
        MerchantId = m.ID,
        MerchantName = m.Name,
        Stores = m.Stores, //because I need all stores in a merchant with all their details
    };

    But when I go and access 

    foreach(var merchant in allmerchants) { var allstores = merchant.Stores; foreach(var store in allstores) { var workingDays = store.WorkingDays;

    } }

    at var workingDays = store.WorkingDays; , it goes to database to get WorkingDays, which in my current case goes to 81 times.

    Thus time exceeds. So, the most optimization I could do was the original query I posted.

    But the system I am working at requires fast processes and the method which takes even

    2 seconds to give response is slow enough.

    Is Entity framework is little slow? Do people commonly face these kind of issues?

    If not, is I am missing something?

    Thanks.


    Deepti


    • Edited by deepti_M Wednesday, April 10, 2013 9:55 AM
    Wednesday, April 10, 2013 9:54 AM