Slow connection by local IP

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
pszuba
Posts: 5
Joined: Fri 03 Nov 2017 10:48

Slow connection by local IP

Post by pszuba » Fri 03 Nov 2017 11:39

We are using Mobile edition to connect from Windows CE/Windows Mobile devices to Postgresql server. After update to version 7.10 (actualy all version above 7.4) we have very slo connection time to Linux servers in local network. When connecting to server by public IP connection is fast.
Example - this connection take about 10-12 seconds

Code: Select all

PgSqlConnectionStringBuilder csb = new PgSqlConnectionStringBuilder();
csb.Host = "192.168.0.3";
csb.Port = 5432;
csb.UserId = "****";
csb.Password = "*****";
csb.Database = "****";
csb.ConnectionTimeout = 30;
PgSqlConnection connection = new PgSqlConnection(csb.ConnectionString);
connection.Open();
Example - this connection take about 1 second

Code: Select all

PgSqlConnectionStringBuilder csb = new PgSqlConnectionStringBuilder();
csb.Host = "91.201.154.162";
csb.Port = 5433;
csb.UserId = "****";
csb.Password = "*****";
csb.Database = "****";
csb.ConnectionTimeout = 30;
PgSqlConnection connection = new PgSqlConnection(csb.ConnectionString);
connection.Open();
After some investigating I suspect that You changed connection method:
This takes couple of seconds:
IPHostEntry entry = Dns.GetHostEntry("192.168.0.3");

But this - which I supose was used before - takes about 0 secons
IPAddress[] entry = Dns.GetHostAddresses("192.168.0.3");

Is it possible that You changed connection method? Is there a workaround? Or could You provide us some source code that we can change? We bought new version of dotConnect so we can update our server to Postgresql 9.6 - but w still can't.
Version 7.3 connect fine always, but it's not compatible with Pg9.6.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Tue 07 Nov 2017 15:18

Please add the following code:

Code: Select all

csb.ForceIPv4 = true;
Does it fix the issue?

pszuba
Posts: 5
Joined: Fri 03 Nov 2017 10:48

Re: Slow connection by local IP

Post by pszuba » Wed 15 Nov 2017 13:06

Sorry for delay - I was out of office.
No it doesn't fix. But I found one more clue - when connecting to local net server on Visual Studio output show:
A first chance exception of type 'System.Net.Sockets.SocketException' occurred in System.dll

Maybe this help in finding solution.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Thu 16 Nov 2017 15:48

Thank you for the provided information. We will investigate it and post here about the results as soon as possible.

Please also try connecting via pgAdmin from the computer where you develop your mobile application with the local IP and external IP. What results are shown by pgAdmin?

pszuba
Posts: 5
Joined: Fri 03 Nov 2017 10:48

Re: Slow connection by local IP

Post by pszuba » Mon 20 Nov 2017 15:03

On pgAdmin connecting to local server is under 1 sec. And connecting to remote server is about 2 sec. But this time includes refreshing database info in pgAdmin.
As I said - on previous versions of dotConnect everything is working fine - howerver we can't us it because it can't connect to PG9.6

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Mon 27 Nov 2017 14:27

Thank you for the information. Please perform additional tests:
1. Try using "protocol=2" in the connection string. Is the issue reproduced in this case?
2. Please connect to your previous PostgreSQL server, not 9.6, with the dotConnect for PostgreSQL 7.10 and with previous dotConnect for PostgreSQL "(actually any version above 7.4)". Is the issue reproduced with any PostgreSQL server or only with 9.6?

pszuba
Posts: 5
Joined: Fri 03 Nov 2017 10:48

Re: Slow connection by local IP

Post by pszuba » Fri 29 Dec 2017 09:47

Sorry for long delay,
1. Protocol=2 dosn't change anything - still connecting to local network server takes 10 sec
2. I have 4 servers to test - on public IP address PG 9.4 and PG9.6 and local network PG 9.4 and PG 9.6 - I perform tests on all of them. But there is no difference between PG 9.4 and PG 9.6.
With dotConnect >7.4 connecting to local network server takes about 10 sec - doesn't matter is this PG 9.4 or 9.6.

Can You just compare part of code where connection is initialized in version 7.3 and 7.4 - it seems that You made some changes there.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Wed 03 Jan 2018 15:42

We are studying the changes made between dotConnect for PostgreSQL 7.3 and 7.4 and post here when find the possible reason. If possible, please specify in which exact version a connection start to connect for a long time. For example in 7.3.389 connection is fast and in 7.3.397 is slow. Or in 7.3.457 connection is fast and in 7.4.464 connection is slow. You can see the list of public dotConnect for PostgreSQL versions by the following link https://www.devart.com/dotconnect/postg ... story.html

Does only connecting take longer for you or commands also are executed slower?

pszuba
Posts: 5
Joined: Fri 03 Nov 2017 10:48

Re: Slow connection by local IP

Post by pszuba » Mon 22 Jan 2018 10:41

Ok - I have made invastigation. I gave You wrong details - 7.3 is not the highest version working correctly.
Version 7.4.478 works fine
Version 7.4.492 works bad - connection takes 10 sek.

Appologize for wrong details of our issue. Maybe that helps finding solution.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Mon 22 Jan 2018 15:50

Thank you for the provided information. We will compare these versions to find out what can be the reason of the issue.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Slow connection by local IP

Post by Pinturiccio » Mon 19 Feb 2018 13:03

We made some changes that should fix the issue. These changes are available in the new public build 7.10.1086. It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Customer Portal (for users with valid subscription only).

The ForceIPv4 property must be set to true.
Please try using this build of dotConnect of PostgreSQL and notify us about the result.

Post Reply