why connect to postgres over internet or VPN slower than sql server ? I have test
DateTime now0 = System.DateTime.Now;
string sqlconnectsrting = "server=xxx.xxx.xxx.xxx;database=pubs;uid=sa;pwd=";
for (int i = 0; i < 1000; i++)
{
SqlConnection connect = new SqlConnection(sqlconnectsrting);
connect.Open();
connect.Close();
}
System.DateTime now1 = System.DateTime.Now;
Console.WriteLine(String.Format("SQL Connect time : {0}", now1 - now0));
now0 = System.DateTime.Now;
string npgconnectsrting = "Server=xxx.xxx.xxx.xxx;Port=5433;User Id=postgres;Password=postgres;Database=hr_data;";
for (int i = 0; i < 1000; i++)
{
NpgsqlConnection connect = new NpgsqlConnection(npgconnectsrting);
connect.Open();
connect.Close();
}
now1 = System.DateTime.Now;
Console.WriteLine(String.Format("Postgres Connect time : {0}", now1 - now0));
When connect is localhost they are similar, but when connect is over internet. SQL connect take 1-5s but postgres take 3-7 minutes. Is there anyway to fix this?
Tuan Hoang Anh
What's the setting for SSL?
SHOW ssl;
Creating an SSL-connection will be slower, even when you actualy don't use SSL. Turn it off if you don't need the extra security.
3 to 7 minutes is about 180 to 420 seconds, if you create 1000 connections, it will "only" take 0.18 to 0.42 seconds for each connection. SQL Server is not going to make a 1000 connections within 1 to 5 seconds over a network, the network will be too slow. Sounds like a connection pool that let you believe you have created a new connection.