Click here to Skip to main content
15,887,214 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to connect to a remote SQL Server from C#. Each time when I open connection, it takes at least 6 to 10 seconds to open the connection and execute the steps after opening the conection.

e.g.
C#
public static bool connectDB()
       {
           try
           {
               Variables var = new Variables();
               string connection = Properties.Settings.Default.serverUrl;

               var.connection = new SqlConnection(connection);
               var.connection.Open(); //takes time to execute this step
               {
                   //set of commands executes after time lag;
               }
               var.connection.Close();
               return true;

           }
           catch (Exception ex)
           {
               MessageBox.Show("Error while conneting to database" + ex.Message,"Demo",MessageBoxButtons.OK,MessageBoxIcon.Error);
               return false;
               throw;
           }

       }

After that for few minutes it runs smoothly. Again after few minutes, it takes 6 to 10 seconds to open a connection.

What I have tried:

I am using this as a connection string:
C#
Data Source=159.70.76.16;Initial Catalog=Itemsense;Integrated Security=True; max pool size=1000

I even tried by removing the 'max pool size' parameter from connection string but still getting same time lag. Is there any solution for this issue?
Posted
Updated 22-Mar-18 11:26am
v3
Comments
RedDk 22-Mar-18 13:04pm    
I can't imagine there's anything more important than a big CPU a big GPU and lots of memory ... especially when it comes to developing an application of my own. Disk size? Sure ...

But why are we quibbling over something like 6 seconds to 10 seconds for some third-party app to respond to a (TSQL?) request? Is there anything wrong with the code above? I can't see anything. From where does the perplexing slowness come?
webmail123 22-Mar-18 13:51pm    
So what you what me to provide you?
j snooze 22-Mar-18 17:12pm    
Couple suggestions on this url that might be worth trying.
https://stackoverflow.com/questions/4269268/initial-connection-to-sql-server-connection-is-slow-why
Richard Deeming 23-Mar-18 14:01pm    
Does your database have "Auto Close" turned on?
webmail123 23-Mar-18 14:27pm    
I checked with it. Its False.

1 solution

It's impossible to answer your question directly for set ot reason...
1. Windows firewall disallows/locks connection to SQL server[^]
2. LAN/WAN connection issues[^]
3. SQL server performance issues
  - SQL Server Performance Tuning Tips[^]
  - Performance Tuning for SQL Server[^]
4. etc.

Unfortunately, you have to check every possibility.

Good luck!
 
Share this answer
 
Comments
webmail123 23-Mar-18 7:43am    
@Maciej. Thank you for the links. I can understand the issue. I should have provided more details for the same. I will explain you the points:
1. Windows Firewall: Firewall is configured to allow the connections to SQL Server. Otherwise I would not able to connect to SQL Server at all. I can connect to SQL Server remotely.
2. LAN/WAN Connection Issue: I am connected to internal network. Which is secure and well configured. From both (client and server) I can access internal network without any connectivity issue.
3. SQL Server Performance Issue: I considered this issue initially, but my database is tiny. It has only 3000 records. Not more than that.

Btw, thanks for the links. I will follow and see what I can improve.

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