Asp.Net web app read query performance
using EntityFramework 5 (POCO), SQL2012, ASP.NET-MVC4.
My problem is that, my app runs slow, the reason is that the technology stack I mentioned above uses the same connection for different queries running on the same server.
Lets say we have two different queries, one runs slow (up to a minute), another one quite fast 1,2 secs. Even on the test server when there is only one user and that is me, if I open two different browser windows(sharing same session),and execute the long query first and then the quick query next at the same time, and when I query the running sessions on the sql server for the active ones I only see one session, and as a result, the quick query also gets stuck waiting on the slow query. I tried couple of things and got random results: I disabled pooling on the connections string;
Sometimes it ran those queries in two different sessions, sometimes in one. I set the min pool size to 10, I think until hitting the min pool size, it was okay, then it started using the same db session.
Another thing is that MARS was enabled by default; And I tried removing that, (I do not know the full impact of this atm, I have not tested everything, so actually I intend to leave this enabled.) and I again got random results.
I used two different browsers, so 2 different IIS Sessions, and got better results. It opened up two db sessions for each query.
We use MSDTC so all the transactions run in a transaction context and this leaves all open connections in Serializable setting, as a result we had to run read queryies also in Transaction Scopes and opted for Read Uncommitted isolation level in that one.
So having quite a capable SQL Server, how can I prevent ASP.Net/ADO.Net/EF5 from using a connection that still has a query running on the server.
PS: I instantiate DbContext by passing in a connection string to the DbContexts's constructor. The queries running above are on two different contexts.
this seems to fix the problem.
[SessionState(System.Web.SessionState.SessionStateBehavior.ReadOnly)] public class SomeController: Controller
If you set the session state to readonly, Asp.Net MVC lets you have multiple queries running at the same time.