Question
I have a complex C# MVC 4 website that connects to multiple databases. Every request goes through the following steps
- Check if user is signed in and get username from authentication cookie.
- Query a lookup database to figure out where the client's database is located and fetch it's connection string (we maintain a different database for each client).
- Connect to client database and run query to get user's details (ID, phone number, preferences etc).
- Perform select/update/delete or insert based on the request.
In addition to the above steps, we also have various action filters that query the database and check for roles etc. These action filters have to perform steps 1,2,3 and 4 to fetch the user info. I am now finding that the application runs slow because of the number of queries it needs to perform. I can dramatically speed it up by caching the connection string and user details in the session.
We will be moving the website to a server farm soon and I don't want to rely on the session for caching. Are there any other obvious design choices that I am overlooking or do I have to continue using caching in the form of memcached or some solution that works across a server farm?
Edit: I should have mentioned that the database and website are on different servers and sometimes the database calls have to be made over the WAN depending on the client's location. I have already measured performance and know that the database calls are the cause for bad performance.
Accepted Answer
You can try using HttpCookie as well as AuthenticationCookie here to maintain the state of the redundant data that you will be accessing across your views and controllers. A simple example I would say would be to maintain the state of the userId and userName of the user that has logged in. This is what you can use as a reference :
private void SetCookie(string userName, long userId)
{
string IdUser = Common.Encrypt(userId.ToString());
FormsAuthentication.SetAuthCookie(userName, false);
HttpCookie uCookie = new HttpCookie("UserId", IdUser);
Response.Cookies.Add(uCookie);
}
This is just a sample code to show how cookies can be generated. After you have set the information in your cookies, you wont have to hit the database every time you need that ID or Name or Phone number or anything else. You just have to retrieve that information from the cookies that you have created.
Hope this helps.
Popular Answer
You can generate and Authentication cookie after 1st step and save data in session or cookie ..later you can use data from previous stored cookie/session and skip step 1.
You can try cluster indexing for fetching data faster