I have develop a web application in MVC4 it is a application about credit and financing. Application is used by 40 to 50 users at a time. Application reporting is very heavy takes 2 to 3 minutes in generating.
I am using single db user 'sa'
Now I am thinking to create all application users in server 2008 also.
Each application user will connect with own db user not with 'sa' user
my question is that is It right decision?
It will increase my application performance? If it is not right then how i can increase its performance? as 40 users taking reports with single 'sa' user may be take more time instead off each user taking with its own database user.
Best Answer
First of all: DO NOT USE 'sa' account.
Create database roles for the application with appropriate permissions, reader, writer, executor etc, with only permissions the application requires.
Secondly, is there any reason this server is not on the Windows Domain? It is preferable to handle permissions via AD Security Groups and service accounts (and managed service accounts) rather than SQL accounts. The benefit is simplified management and increased security - Kerberos vs plain text passwords.
EDIT : as I have not answered your question. In your case the number of users is irreleveant as they transalate to number of requests and spawned workers/threads. You are asking database to serve 40 reports, no matter if they all come from 1 user, or 40 users asks for 1 report, the end result is still 40. In extreme situations it may be preferable to have less users to save resources and time on creating new connections as each takes tiny amount of memory but that is a whole different conversation.
Hope this helps.