Entity Framework - First query slow

Question

Hello

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 :)

Edit 19-10-2010: 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.

Answer 1

Well, lots of things can make a SQL Server query slower the first time it is run. Most of them do not take multiple seconds, however.

… Except for hard drive random accesses. The first time you run the query, SQL Server may have to read the database pages from hard disk storage. The next time you run the query those pages are likely in memory.

Regarding the Entity Framework, the first time you run a query it must be compiled into SQL. You can use the CompiledQuery type to pre-compile Entity Framework queries in order to do this work ahead of time, before the end user has to wait for it.

On a very large model, view generation take some time, as well. You can move this to compile time, instead. See this article for more such tips.

Answer 2

Hello

Sorry for the late answer.
I do not believe this to be a case of SQL server reading from the harddisk. The slow query happens every time there is a "first query" in EF.
ex.
1: Run the first query with EF, the SQL statement is slower then any secondary query.
2: Dispose the context/repository.
3: Create a new Context.
4: Run the same query as before. (again the first query is slow and so is the SQL statement)
It is almost like EF sends some options along with the first query that makes the server slow.

As for Query Compilation, as i remember the query is compiled the very first time it is used which means that the first query would take even longer to execute.
Secondary queries would be faster but the speed on secondary queries is not the issue.
I also did a test where i created a compiled query as a static so that it would be compiled for all contexts that was created. I then created a context, ran the query, destroyed the context and created a new one and ran the same query once more. The difference was not that big, only a few seconds and the very first time i ran the query it still took as long as without pre-compiling it.

As for View Generation, we already implement this using T4 Templates.

Is the answer really that EF only works if you don't do anything but the simplest queries that return only a relatively small amount of data?