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;

Pooling=False;

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.

Answers


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.


Need Your Help

How to import Google Web Font in CSS file?

css css3 fonts google-webfonts

I'm working with a CMS which I only have access to the CSS file. So, I can't include anything thing in the HEAD of the document. I was wondering if there was a way to import the web font from withi...

Request.IsAuthenticated returns false unless page refreshed

c# asp.net-mvc asp.net-mvc-5

As a part of my user logon/logoff process of my webpage (using standard MVC authentication module) I have the following on my view: