0

I have an ASP.NET MVC application which gathers data from multiple Databases.

The databases hold information for various sites and for every new site we have a new Database. The database for each site is connected at two points, from the site and then from HQ.

A web application updated data every minute from the site and the data is is served to the HQ (via another web application) every minute. Sometimes the application response is very slow and from what I have investigated, it may be because the connection pool starts filling up swiftly.

I want to ask what is the best approach to such application, where I can get the best performance out of it. Any guidance is welcome.

progrAmmar
  • 2,606
  • 4
  • 29
  • 58
  • how you fetch data from data base? using stored procedure or EF? and give some details about you application architecture. Because answer is "really depends". – Hadee Nov 17 '15 at 20:23
  • In the MVC app I am using entity framework (the LINQ to SQL Classes) to fetch the data – progrAmmar Nov 18 '15 at 00:38

1 Answers1

4

How to improve your web application performance regarding to database, really depends on your architecture. But there are some general rules which you should always follow:

  1. Check about thread starvation:On the Web server, the .NET Framework maintains a pool of threads that are used to service ASP.NET requests. When a request arrives, a thread from the pool is dispatched to process that request. If the request is processed synchronously, the thread that processes the request is blocked while the request is being processed, and that thread cannot service another request.

    This might not be a problem, because the thread pool can be made large enough to accommodate many blocked threads. However, the number of threads in the thread pool is limited. In large applications that process multiple simultaneous long-running requests, all available threads might be blocked. This condition is known as thread starvation. When this condition is reached, the Web server queues requests. If the request queue becomes full, the Web server rejects requests with an HTTP 503 status (Server Too Busy).

    for "thread starvation" the best approach is using "Asynchronous Methods". refer here for more information.

  2. Try to use using block for your datacontext, to dispose them immediately after finishing with them.

  3. Huge data amount in transaction: you should check your code. May be you using too much data without need to all of them. For example you transfer all object which you may need just one properties of object. In this case use "projection"(refer here for an example).

    Also you may use "lazy loading" or "eager loading" base on you scenarios. But please be noted that none of these are magic tool for every scenario. In some cases "lazy loading" improve performance and on others "eager loading" makes things faster. It depends to your deep understanding of these two terms and also your case of issue, your code and your design.

  4. Filter your data on server side or client side. Filtering data on server side helps to keep your server load and network traffic as less as possible. It also makes your application more responsive and with better performance. Use IQueryable Interface for server side filtering (check here for more information). One side effect of using server side filtering is having better security

  5. Check your architecture to see do you have any bottleneck. A controller which gets called too much, a methods which handles lots of objects with lots of data, a table in database which receives requests continuously, all are candidates for bottle neck.

  6. Ues cashing data when applicable for most requested data. But again use cashing wisely and based on your situation. Wrong cashing makes your server very slow.

If you think your speed issue is completely on your database, the best approach is using sql profiling tools to find out which point you have critical situation. Maybe redesign of your own tables could be an answer. Try to separate reading and writing tables as much as possible. Separation could be done by creating appropriate views. Also check this checklist for monitoring your database.

Community
  • 1
  • 1
Hadee
  • 1,392
  • 1
  • 14
  • 25
  • Hi Hadee thanks for the detailed answer. The problem that I am facing is that the application is basically transforming into a streaming data application. We have multiple end points updating the server within 1 minute. The data is coming in at 40 KB/s, which means the DB server is always transacting (Insert, Updaes or deletes). Every second we have new data so I don't know if it rules out the cache. What will you suggest in this scenario – progrAmmar Nov 18 '15 at 01:25
  • Where exactly you have speed issue? – Hadee Nov 18 '15 at 01:27
  • The DB querying, when the load is light, everything works brilliantly, but when about 20 sites are uploading simultaneously, we get a little bit slow, like may be 10 to 20 seconds. That may not be much but it really is as every second's data is very important. The reason for that is that the Connection pools are almost filled up, (or that's what we are thinking) – progrAmmar Nov 18 '15 at 01:30