none
EF query is excecuted too slow only on first time ? RRS feed

  • Question

  • Hello !

    i'm working with EF 4.0  , and SQL server 2008R2.

    On my project , all the queries that i have , are too slow when executing for first time , after first time everything is ok.

    For example :

    (From t in context.Mytable where t.pos=5 select t).Tolist

    this query may need 30+ seconds to execute.

    after , i can execute the same query , even with other filters and the query is executed for 3-4 seconds:

    (From t in context.Mytable where t.pos=45 select t).Tolist

    what can i do to speed up the first time ?

    thank you.

    Saturday, January 19, 2013 3:45 PM

All replies

  • Hi dcode25;

    You state, " all the queries that i have , are too slow when executing for first time , after first time everything is ok.", so if I understand you correct you perform something like the following :

    • You create a DataContext
    • Then you would lets say execute each query once and each of the queries would take a long time
    • Then you would run each query a second time and each would take seconds.

    Is that correct?

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, January 19, 2013 7:23 PM
  • Thank you !

    Example :

    i have a form with a text box and a button. in textbox i insert a value to filter the data .After i press the button to execute the query :

    (From t in context.Mytable where t.pos=Textbox1.text select t).Tolist

    when i open the form and press the button for the first time , the execution is very slow , i should wait 30 sec to get the data.

    if i press the button for second , third .... time with the same value on textbox or with another value , i get the result after 2-3 sec.

    I have many forms like this and the situation is the same.First time execution is very slow , after the performance is very good.

    what can i do to improve performance for the first time execution ?

    Thank you !

    Saturday, January 19, 2013 8:14 PM
  • Hi;

    Can you show the code for one such instance where this happens so I can see how you are creating the context to how you issue the queries.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, January 19, 2013 8:45 PM
  • The entity model is created on design from existing SQL server database.

    -------------------------------------------------------------

    dim context as myEntities

    Private sub Form1Load........... Handles Mybase.load

    context=new myEntities

    end sub

    Private sub button1click........ Handles Button1.click

    Dim query as IeNumerable(of  Mytable1)

    query= (From t in context.Mytable1 where t.pos=Textbox1.text

               Select t).Tolist

    ......

    end sub

    Saturday, January 19, 2013 8:59 PM
  • When ever we create a new model by instantiating the data context that very first query causes the model to be created and validated in local memory. This can take a while to do and the most likely cause for your issue. There are some things you can do to over come some of these issues. One such thing that would help for example is if the query is NOT going to be used to update the database but just use the results of the query then you should disable object tracking. Please have a look at the following post to see other things that can be done. 

    Performance Considerations for Entity Framework 5

    Performance Considerations (Entity Framework)

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, January 19, 2013 9:28 PM
  • Thank you !

    As i can read on your second link , Generating Views has High Cost on performance , so  i'm trying Pre-Generated views .

    I have created  using Entity Framework Power Tools.

    But after i rebuild my application , there's no changes in performance. The situation is the same.

    Saturday, January 19, 2013 11:21 PM
  •   

    How many tables are in your model?

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, January 20, 2013 4:08 AM
  • 65 tables

    Monday, January 21, 2013 12:51 PM
  •   

    Can you create a test project using your database with just the tables needed for one form or query pulling down the same amount of data from the database and see if that gives the same amount of time on the first query.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, January 21, 2013 5:06 PM
  • Thank you !

    On this test , the first query is executed in less time than first time on my full project , but again the first time is slow compared with other times.

    Monday, January 21, 2013 6:52 PM
  •   

    How many tables were in the test model?

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, January 21, 2013 9:14 PM
  •   

    The very first query to a newly created data context is going to be slower then the others after it because the model first needs to be created in local memory and then validated against the database and after all that is done then the query is sent to the database.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, January 21, 2013 9:19 PM
  •   

    The very first query to a newly created data context is going to be slower then the others after it because the model first needs to be created in local memory and then validated against the database and after all that is done then the query is sent to the database.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    But  i have created Pre-Generated Views. ( with entity Framework Power Tools) 

    and i have read that this may help reducing time when executing first time queries.

    But on my project nothing change after this.

    Monday, January 21, 2013 10:46 PM
  •   

    Please note the operative words in your statement "may help" it does not state "will help".

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, January 22, 2013 2:22 PM
  • ok , but if i have problems with 65 tables , what about models with 100+ tables .

    What can i do at program startup for example , to load something or ??? because is better to wait more at program startup and after to have some more performance with queries.

    Is there someone that have problems with this situation and have resolved ??

    Thank you.

    Tuesday, January 22, 2013 9:10 PM
  • So far this thread hasn't proved anything other than what the op stated.  First time queries are too slow.

    Try this: Start up a wireshark trace for just one query you know will take X amount of time.  After the data is returned turn the trace off.  Look at the packet flow.. Did you see anything that would take up the majority of the delay you describe?  If you did this problem has nothing to do with EF or .NET.

    Until you know what side the problem happens it's just a guess that the problem is EF.


    JP Cowboy Coders Unite!

    Tuesday, January 22, 2013 9:40 PM
  • It is interesting that using pre-generated views didn't help at all. One quick thing to check would be that the compiled views are being hit. If you put a breakpoint inside the code that is generated by the power tools then it should be hit when you execute the first query. If it doesn't then the pre-compiled views are not being hit, though I don't know why that would be and have never heard of it happening before.

    The other thing to try is to execute one query that is slow on the first run, and then execute a different query in the same session. The view model and other startup costs that EF has are generally cached per app-domain. So if the second query does not perform poorly then you are definitely hitting EF startup cost problems. If the second query is still slow then it is more likely your database server that is causing the problem.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, January 31, 2013 1:08 AM
    Moderator
  • Thank you !

    i have put a breakpoint inside the code generated by power tools and yes , is hit during execution of first query.

    and for the other thing , the first query is running slow , the second query run almost immediately ( this examples are for the same kind of query for example : first query : q1=From t in myentity.mytable1 where t.field1=2 select t    /   Second query : q2=From t in myentity.mytable1 where t.field1=5 select t)

    Friday, February 1, 2013 10:32 PM
  • Is there any chance of getting the model and query that you are using so that we can investigate the slow execution?

    I haven't heard of slow warm-up times that are not effected by view gen at all.

    If you can put together a repro, or even just send me the model, then I might be able to get to the bottom of it. glennc <at> Microsoft.com is my email address if you wanted to email it.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Friday, February 1, 2013 10:51 PM
    Moderator