none
Entity Framework - First Query Slow RRS feed

  • Question

  • Hello

    I have already asked this same question at stackoverflow but it doesn't look like anyone has an answer so i was hoping that i might get one here instead.

    As the title suggest i'm having a problem with the first query against a MS SQL database using the Entity Framework. I have tried looking for an answer on different sites but no one seems to actually have a solution to this.

    I'm loading quite a lot of rows from the database including two 0-many relationships. The tests was done in Visual Studio 2010 using the Entity Framework 4.0 Model and the POCO generator (there isn't much difference in timings between normal entities and POCO objects). I also used the T4 Views Template to pre-compile the views. The database was on a MS SQL Server 2008.

    What i would really like to know is why the first query is soo much slower than any secondary queries. I also wanna know if something can be done to increase the speed of the first query to a point where it is within acceptable limits. This is a big query and we may get other queries that are even bigger and it is understandable that they may be a bit slow but 30 seconds is way too slow for user to wait for especially when datasets can get the same data a lot faster.

    I have done some timing tests to try and find out where the problem lies and i was a bit surprised to see that it looks like it is the SQL server that is slow on the first query. Timings was as follows:

    .NET testing application.
    - First Query: 29,6 seconds
    - Second Query: 3,2 seconds

    SQL Profiler
    - First Query: 27 seconds
    - Second Query: 3,2 seconds

    SQL Server Query Window
    - First Query: 8 seconds
    - Second Query: 4 seconds

    Timings in the application was measured with the Stopwatch. Only the query was measured and .ToList() was used to execute the query.

    Timings in the SQL Profiler is for the same queries that was executed in the application which shows that the application only use about 2,6 seconds to fill data into the objects. The last 27 seconds is used for executing the query on the SQL server.

    Looking at the secondary query the timings are the same for both application and SQL server but executing the query is much faster this time. I can understand why the application doesn't use any time because there is no new rows that need to be converted to objects but why is the query so much faster, i would have expected a few seconds because of execution plans but not 24 seconds.

    Just for testing purpose i copied the SQL that the Entity Framework generates and opened a new query window with a separate connection and executed the query in it. As you can see it takes 8 seconds for the first query and 4 seconds for the second.

    I hope someone have some suggestions.
    ps. I apologize for the wall of text :)

    Monday, October 18, 2010 1:52 PM

Answers

  • On 10/19/2010 5:39 AM, Night-Blade wrote:
    > Hello
    >
    > As i mentioned in my post I already use compiled views.
    >
    > I also know about MergeOptions and the plan is to use NoTracking most of
    > the time but that is still rather slow compared to getting the same data
    > using SQL commands and loading it into a dataset.
    >
    > Compiled queries don't help on the first query as the query is compiled
    > the first time it is run.
     
    Yeah that's true, and any subsequent query is doing to use less time.
    >
    > If you use Entity SQL to load a datatable then you might as well just
    > use old school sqlcommands and T-sql.
     
    Use Entity-SQL to load a datatable? No I don't think you should be doing
    that. You should be using objects and not datatables
    >
    > If you use Entity SQL to populate entities then you aren't really
    > getting anything that you wouldn't get using EF the "normal" way.
     
    Yes you are, you're getting the speed and using a ESQL query and a
    datareader which is the fastest way possible to query and load data.
     
    The same principles would apply if T-SQL, SQL Command object, and a
    datareader were being used.
    >
    > I did a test yesterday that seems to support that rows are being
    > returned in an Sequential(right word?) manner. Meaning that when a row
    > is returned from the database it is immediately materialized (if it does
    > not already exist in the context) then the next row is returned and so on.
    >
    > That is why it appears that the query is taking a lot of time on the
    > database server because materialization time is included in the sql
    > profiler timings.
     
     
    Well you can do part of it and use Linq-2-Entities with EF and refactor
    the query, use ESQL or use can use TSQL within the EF.
     
    If you're worried about the 30 seconds on the first query hit, which it
    doesn't seem you're coming around that using Linq-2-Entities, then
    switch to the latter two above.
     
    I'll bet it's not going to take 30 seconds to query and load those
    entities with ESQL or TSQL and using a datareader directly and return them.
     
    <http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx>
     
    There is also nHibernate
     
     
     
     
     
    • Marked as answer by liurong luo Monday, October 25, 2010 7:24 AM
    Tuesday, October 19, 2010 12:05 PM

All replies

  • On 10/18/2010 8:52 AM, Night-Blade wrote:
    > Hello
    >
    > I have already asked this same question at stackoverflow
    > <http://stackoverflow.com/questions/3891125/entity-framework-first-query-slow>
    > but it doesn't look like anyone has an answer so i was hoping that i
    > might get one here instead.
    >
    > As the title suggest i'm having a problem with the first query against a
    > MS SQL database using the Entity Framework. I have tried looking for an
    > answer on different sites but no one seems to actually have a solution
    > to this.
    >
    > I'm loading quite a lot of rows from the database including two 0-many
    > relationships. The tests was done in Visual Studio 2010 using the Entity
    > Framework 4.0 Model and the POCO generator (there isn't much difference
    > in timings between normal entities and POCO objects). I also used the T4
    > Views Template to pre-compile the views. The database was on a MS SQL
    > Server 2008.
    >
    > What i would really like to know is why the first query is soo much
    > slower than any secondary queries. I also wanna know if something can be
    > done to increase the speed of the first query to a point where it is
    > within acceptable limits. This is a big query and we may get other
    > queries that are even bigger and it is understandable that they may be a
    > bit slow but 30 seconds is way too slow for user to wait for especially
    > when datasets can get the same data a lot faster.
    >
    > I have done some timing tests to try and find out where the problem lies
    > and i was a bit surprised to see that it looks like it is the SQL server
    > that is slow on the first query. Timings was as follows:
    >
    > .NET testing application.
    > - First Query: 29,6 seconds
    > - Second Query: 3,2 seconds
    >
    > SQL Profiler
    > - First Query: 27 seconds
    > - Second Query: 3,2 seconds
    >
    > SQL Server Query Window
    > - First Query: 8 seconds
    > - Second Query: 4 seconds
    >
    > Timings in the application was measured with the Stopwatch. Only the
    > query was measured and .ToList() was used to execute the query.
    >
    > Timings in the SQL Profiler is for the same queries that was executed in
    > the application which shows that the application only use about 2,6
    > seconds to fill data into the objects. The last 27 seconds is used for
    > executing the query on the SQL server.
    >
    > Looking at the secondary query the timings are the same for both
    > application and SQL server but executing the query is much faster this
    > time. I can understand why the application doesn't use any time because
    > there is no new rows that need to be converted to objects but why is the
    > query so much faster, i would have expected a few seconds because of
    > execution plans but not 24 seconds.
    >
    > Just for testing purpose i copied the SQL that the Entity Framework
    > generates and opened a new query window with a separate connection and
    > executed the query in it. As you can see it takes 8 seconds for the
    > first query and 4 seconds for the second.
    >
    > I hope someone have some suggestions.
    > ps. I apologize for the wall of text :)
    >
     
    I'll assume you know about the mergeoption.
     
    http://blogs.msdn.com/b/dsimmons/archive/2010/01/12/ef-merge-options-and-compiled-queries.aspx
     
    Do you know about compiled views?
     
    http://msdn.microsoft.com/en-us/library/bb896240.aspx
     
    Do you know about complied queries.
     
    http://msdn.microsoft.com/en-us/library/bb896297.aspx
     
    <copied>
     
    4 This overhead is not required for EntityClient queries because
    EntityClient queries return an EntityDataReader instead of objects. For
    more information, see EntityClient Provider for the Entity Framework.
     
    http://msdn.microsoft.com/en-us/library/cc853327.aspx
     
     
    http://msdn.microsoft.com/en-us/library/bb387145.aspx
    http://msdn.microsoft.com/en-us/library/bb387118.aspx
     
    Just like you can use ADO.NET, SQL Command Object and a datareader, you
    can do the same thing with EF with Entity SQL.
     
    You can make the query, even getting the children of the parent, use a
    datareader, instantiate new an entity (an object), populate it or them
    with the datareader, return the entity or return the List<entities>
    List<T>.
     
     
    Tuesday, October 19, 2010 5:32 AM
  • Hello

    As i mentioned in my post I already use compiled views.

    I also know about MergeOptions and the plan is to use NoTracking most of the time but that is still rather slow compared to getting the same data using SQL commands and loading it into a dataset.

    Compiled queries don't help on the first query as the query is compiled the first time it is run.

    If you use Entity SQL to load a datatable then you might as well just use old school sqlcommands and T-sql.

    If you use Entity SQL to populate entities then you aren't really getting anything that you wouldn't get using EF the "normal" way.

     

    I did a test yesterday that seems to support that rows are being returned in an Sequential(right word?) manner. Meaning that when a row is returned from the database it is immediately materialized (if it does not already exist in the context) then the next row is returned and so on.

    That is why it appears that the query is taking a lot of time on the database server because materialization time is included in the sql profiler timings.

    Tuesday, October 19, 2010 10:39 AM
  • If you are using NoTracking then there should be no difference in
    materialization speed between to calls against the same context.

    How are you controlling MergeOption?


    /\/\arkus.
    Tuesday, October 19, 2010 11:29 AM
  • Correction:

    between to calls

    "between TWO calls" of course.


    /\/\arkus.
    Tuesday, October 19, 2010 11:40 AM
  • On 10/19/2010 5:39 AM, Night-Blade wrote:
    > Hello
    >
    > As i mentioned in my post I already use compiled views.
    >
    > I also know about MergeOptions and the plan is to use NoTracking most of
    > the time but that is still rather slow compared to getting the same data
    > using SQL commands and loading it into a dataset.
    >
    > Compiled queries don't help on the first query as the query is compiled
    > the first time it is run.
     
    Yeah that's true, and any subsequent query is doing to use less time.
    >
    > If you use Entity SQL to load a datatable then you might as well just
    > use old school sqlcommands and T-sql.
     
    Use Entity-SQL to load a datatable? No I don't think you should be doing
    that. You should be using objects and not datatables
    >
    > If you use Entity SQL to populate entities then you aren't really
    > getting anything that you wouldn't get using EF the "normal" way.
     
    Yes you are, you're getting the speed and using a ESQL query and a
    datareader which is the fastest way possible to query and load data.
     
    The same principles would apply if T-SQL, SQL Command object, and a
    datareader were being used.
    >
    > I did a test yesterday that seems to support that rows are being
    > returned in an Sequential(right word?) manner. Meaning that when a row
    > is returned from the database it is immediately materialized (if it does
    > not already exist in the context) then the next row is returned and so on.
    >
    > That is why it appears that the query is taking a lot of time on the
    > database server because materialization time is included in the sql
    > profiler timings.
     
     
    Well you can do part of it and use Linq-2-Entities with EF and refactor
    the query, use ESQL or use can use TSQL within the EF.
     
    If you're worried about the 30 seconds on the first query hit, which it
    doesn't seem you're coming around that using Linq-2-Entities, then
    switch to the latter two above.
     
    I'll bet it's not going to take 30 seconds to query and load those
    entities with ESQL or TSQL and using a datareader directly and return them.
     
    <http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx>
     
    There is also nHibernate
     
     
     
     
     
    • Marked as answer by liurong luo Monday, October 25, 2010 7:24 AM
    Tuesday, October 19, 2010 12:05 PM