Entity Framework - Slow first query

.net entity-framework performance

Question

As implied by the title, I'm experiencing trouble with Entity Framework's initial query against a SQL Server database.

I've tried searching for a solution on several websites, but nobody appears to truly have one.

I'm importing two 0-many relationships together with a large number of database records.

The tests were carried out in Visual Studio 2010 utilizing the POCO generator and the Entity Framework 4.0 Model (there isn't much of a difference in execution times between POCO objects and regular entities). Additionally, I pre-compiled the views using the T4 Views Template.

SQL Server 2008 hosted the database.

Why the initial query is so much slower than any other inquiries is what I really want to know.

I also want to know if there is a way to make the initial query run faster till it is within acceptable bounds.

This is a large query, and we could get others that are much larger. It is reasonable to expect such searches to be a little delayed, but 30 seconds is far too long for a user to wait, particularly when they can obtain the same data much more quickly.

I ran some timing tests to try to identify the issue, and I was quite shocked to notice that it seems that the SQL Server is the one that is sluggish on the first query.

Following were the timings:

.NET testing software

  • Initial search time: 29.6 seconds
  • Question 2: 3,2 seconds

the SQL Profiler

  • Initial search time: 27 sec
  • Question 2: 3,2 seconds

Query Window for SQL Server

  • Initial search: 8 seconds
  • Time interval: 4 seconds

The application's timings were measured using theStopwatch class. Query alone was measured, and.ToList() was employed to run the query.

The program only needed 2,6 seconds to populate the objects with data, according to timings in SQL Server Profiler for the identical queries that were used in the application.

The query on SQL Server is run over the previous 27 seconds.

The timings for the secondary query are same for the application and the SQL server, but this time, the query is executed considerably more quickly.

I can see why the application doesn't take any time since no new rows need to be converted to objects, but I don't get why the query is so much quicker. I would have anticipated a few seconds due to execution plans, but not 24 seconds.

Just for testing, I created a new query window with a different connection, copied the SQL that the Entity Framework creates, and ran the query there.

As you can see, the first question takes 8 seconds to complete, whereas the second one does it in 4 seconds.

I'm hoping someone has some ideas.

p.s. I'm sorry for the long text.

Edit 19-10-2010:
Yesterday, I conducted a test that seems to confirm that rows are being returned in a consecutive order. Meaning that once the first entry is retrieved from the database, it is materialized right away (assuming it does not already exist in the context), and so on.

Because materialization time is taken into account in the SQL Server profiler timings, it seems that the query is running slowly on the database server.

I don't think SQL Server is reading data from the hard drive in this instance. Every time EF does a "initial query," a slow query occurs.

ex.

  1. Run the first query using EF; the SQL statement executes more slowly than any subsequent queries.
  2. Get rid of the context and repository
  3. Make a fresh context
  4. Run the previous query (again the first query is slow and so is the SQL statement)

It nearly seems as if EF sends extra information with the first query to slow down the server.

Regarding query compilation, from what I can recall, the first time a query is used, it is compiled, therefore the initial query would take much longer to perform.

Although secondary inquiries might be quicker, speed is not the problem.

Additionally, I ran a test in which I constructed a static compiled query that would be compiled for each new context that was formed.

I then built a context, executed the query in it, deleted the context, created a new one, and repeated the process.

The change was not significant—only a few seconds—and the first time I executed the query, it took the same amount of time as before pre-compilation.

We already use T4 Templates to implement view creation.

Is the response that EF only works if you stick to the simplest queries that only return a limited amount of data true?

1
27
1/12/2016 10:03:47 AM

Popular Answer

We had the same problem with EF 5.0, and as of right now, a cursory Google search turns up no significant speedup.

This site claims that while "Loading Metadata" takes a reasonable amount of time, it only has to happen once per AppDomain. No pre-compilation-like techniques for loading the meta-data have come to my attention.

The fix we've devised is to run the application with a quick query on the Context in a different thread. Although it still takes a while (18–19 seconds in our instance), the app remains responsive while the meta-data is loaded. Additionally, the initial load itself does not take as long.

Please be aware that in our situation, the user may utilize the application for up to 18–19 seconds before requiring an EF call to be sent in response to their activities. Obviously, this workaround may not result in much of a performance improvement if this is not achievable in your program.

13
2/5/2013 12:22:41 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow