How to maximize the number of concurrent connections in C#/ IIS/SQL?

Question

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    
 -----25-05-2016--CR_REQ-19754----START  
 , isnull(MinLimitOutward,0)as MinLimitOutward        
 , isnull(MaxLimitOutward,0)as MaxLimitOutward        
 , isnull(ISLIMITOUTWARD,'N') as ISLIMITOUTWARD     
 -----25-05-2016--CR_REQ-19754----END    
 FROM Users         
 INNER JOIN ROLES ON USERS.RoleId = ROLES.ROLEID                      
 WHERE LoginId = @p_LoginID AND IsDeleted = 0

Posted 22-Mar-18 1:46am

ranio

Updated 22-Mar-18 3:02am

v2

Add a Solution

Comments

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

Add a Solution

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

#realJSOP · Answer 1 · 2018-03-22T03:03:00

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.

How to maximize the number of concurrent connections in C#/ IIS/SQL?

1 solution

Solution 1

Add your solution here

Preview 0

How to maximize the number of concurrent connections in C#/ IIS/SQL?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us