EntityFramework connection is much slower than Linq to Sql Connection

Kiragon

I am trying to set up LinqPad for use as a code scratchpad on my project. My project is set up to use entity framework from a DbContext. I created a connection, gave it my dlls and connection string, and queries do work. However, they are very extremely slow compared to ssms - even a fairly simple query (select a single small object with a given ID) takes upwards of 30 seconds to complete. Ouch!

I created another connection to the same database using the Linq to Sql connection type. With this second connection, the same queries work at much better speeds, comparable to my query speeds in ssms. However, this doesn't give me access to some EF-specific features like .Include, which are pretty important to me since we use EF throughout our application and I want to be able to freely move queries between Linqpad and VS.

When I look at the SQL translation queries in the EF connection, I see a lot of stuff happening before my translated query, including:

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ([All of my database tables])

This and some other things appear to be appended to the beginning of the SQL output for every query I write with the EF connection. I assume this is some kind of setup for f the DB context, and it may be what is killing my performance. EF has a noticeable spin-up time when I first launch my app that also takes about 30 seconds, but in my app this only occurs once when the DB Context is first queried, not for every query in the application. Is my entire application context having to spin up every single time I execute a query in Linqpad?

Is there some way for me to set up this connection to spin up the application context just once, and keep it open for multiple queries? Has anyone else encountered similar issues to this when using a DbContext connection? What did you do to resolve them? I purchased Linqpad primarily as a query scratchpad for my EF project, and that is not going to be possible with the performance I am currently getting.

Thanks for any help!

JoeAlbahari

Does the following help?

http://stackoverflow.com/questions/22079055/entity-framework-sql-first-database-query

Also, you said it takes 30 seconds on every query run. So if you run a query and then press F5 to re-run it, it takes another 30 seconds?

Kiragon

Entering a query and hitting F5 to rerun does run the query very quickly the second time. However, if I modify the query in any way, or write a new one, it has to go through the same spin-up time again. This takes place whether I do it in the same query window, or a new one.

The link you posted definitely describes the spin-up time that I get in my application. It seems to be an unavoidable feature for people who do code first with migrations. However, it normally doesn't have to be run on every single query, only the first query when the app pool is spun up. If I only had to go through the wait time once when first querying through Linqpad, that would be a huge improvement. Having to do it on every updated query makes the software very hard to use.

The line given by the SO link is actually already in my project, in the constructor for the DbContext object:

Database.SetInitializer(null);

With my context added in, naturally.

JoeAlbahari

I'm surprised that modifying the query would cause EF to re-initialize. Try adding the following to the start of your query:

("Process " + Process.GetCurrentProcess().Id + " Domain " + AppDomain.CurrentDomain.Id).Dump();

Is the slowdown associated with a change of process or domain? (I would expect the process/domain to remain the same).

Also, does it make a difference if you add the following to the start of your query:

Database.SetInitializer<UserQuery>(null);

Kiragon

I just tried this with a few different queries. I queried three tables each with one object in them. The Process and Domain were the same with each query. Each time that I changed the table that was being queried, I got the long spin-up time. The Database.SetInitializer call did not seem to make a difference.

If it matters, my connection is made using the DbContext EF type. I am connecting through a connection string parameter with an app.config file.

JoeAlbahari

I've tried to reproduce this with an AdventureWorks DbContext, but I'm not experiencing what you describe. The initialization overhead is around 2 seconds, and that overhead occurs only with new query tabs, not when changing the query and re-running in the same tab. Do you get a similar result with an AdventureWorks DbContext? If so, there is presumably some difference between this and your typed DbContext. If you can identify the difference, and it's something I can work around by modifying LINQPad, let me know.

Sorax

Joe thanks for the DatabaseInitializer tip. I never understood why the initializer would run even though my context had it as null. I knew UserQuery inherited it but I just didn't think I had to set it on UserQuery.

Could you add this as a property of the connection? As either a checkbox for null initializer or a box where we could enter the code.

Thanks for everything. Life is better with LINQPad .