Options
EntityFramework connection is much slower than Linq to Sql Connection
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!
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!
Comments
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?
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.
("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);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.
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 .