Click here to Skip to main content
15,748,930 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am having time out issue while logging in concurrently if the limit is 50 or above.

If the logged in count is less than 50 no time out issue occurs.

I am working in .net using c# . I have uploaded the application in IIS server and the version is above 7. SQL server version used is 2008.

What I have tried:

<add name="MyConnectionString" connectionString="Data Source=NTP-434;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=password1.;" providerName="System.Data.SqlClient"/>

Logging in Code

Front End
 DataSet Ds = new DataSet();
                DbCommand DbCmd = DataAccessBase.Database.GetStoredProcCommand("CHECK_AUTHENTICATEUSER");
                DataAccessBase.Database.AddInParameter(DbCmd, "@p_LoginID", DbType.String, p_USERID);
                DataAccessBase.Database.AddInParameter(DbCmd, "@p_Password", DbType.String, p_PASSWORD);
                DataAccessBase.Database.AddOutParameter(DbCmd, "@p_error_code", DbType.String, 40);
                DataAccessBase.Database.AddOutParameter(DbCmd, "@p_error_msg", DbType.String, 1000);
                Ds = DataAccessBase.Database.ExecuteDataSet(DbCmd);
                p_error_code = Convert.ToString(DataAccessBase.Database.GetParameterValue(DbCmd, "@p_error_code"));
                p_error_msg = Convert.ToString(DataAccessBase.Database.GetParameterValue(DbCmd, "@p_error_msg"));
                return Ds;

Back End

SELECT UserId        
 , LoginId        
 , Active        
 , Approved        
 , UserName        
 , ROLES.RoleId        
 , ROLECODE        
 , ROLENAME        
 , (ROLECODE + ' [' + ROLENAME + ']') AS USER_ROLE         
 , EmailId        
 , CorporateId        
 , IsPwdReset        
 , SessionID         
 , isnull(MinLimit,0)as MinLimit        
 , isnull(MaxLimit,0)as MaxLimit        
 , isnull(ISLIMIT,'N') as ISLIMIT       
 , isnull(IsDashboard,'N') as ISDASHBOARD    
 , isnull(MinLimitOutward,0)as MinLimitOutward        
 , isnull(MaxLimitOutward,0)as MaxLimitOutward        
 FROM Users         
 INNER JOIN ROLES ON USERS.RoleId = ROLES.ROLEID                      
 WHERE LoginId = @p_LoginID AND IsDeleted = 0      
Updated 22-Mar-18 3:02am
F-ES Sitecore 22-Mar-18 8:05am    
Are you closing the connections after you use them?
ranio 22-Mar-18 8:09am    
Connection is closed.

1 solution

Increase the timeout value on whatever you're using to send the query to the database. I double the default timeout value when I hit a database.

You may also want to revisit your stored proc to make sure it's performing as optimally as possible. The more complex a query is, and the more records you're dealing with all conspire to drag db performance into the dirt.

EDIT ============

SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable (32767). Knowing this, I don't think it has anything to do with the number of connections. It might be that your server doesn't have enough oomph to handle more than 50 connections efficiently. You could add memory (a reasonable Db server has AT LEAST 64gb of RAM), and/or increase the number of cores in the box (the servers at my work have 12 individual CPUs).

The problem isn't with IIS because you'd actually get a "server busy" error if you exceeded the number of allowed connections. The problem (assuming your hardware can handle it) is that you queries are bogging it down.

A SQL connection should only be open long enough to get/save data, and then immediately closed. If you're doing that, then it comes back to the efficiently of your queries and the amount of data you're working with.
Share this answer
ranio 22-Mar-18 9:31am    
above issue is happening only when the users concurrent count is 50 or above. I mean when 50 users are logging at the same time the time out issue is occurring. SP also and call from front end which is executing without much time delay until 50.

Do we increase the concurrent count of IIS or Sql?
#realJSOP 22-Mar-18 9:44am    
I modified my answer to address this question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900