SqlConnection.Open first time very slow android - 10 times slower than windows

[thevirtualdj]

Greetings all,

We have s strange issue and cannot get the reason behind.
We are using this connection string:
string connectionString = @"Server=webserver;" +
"Database=MyDB;" +
"User Id=sa;" +
"Password=mysecretpass;" +
"Persist Security Info=False;" +
"Encrypt=True;" +
"TrustServerCertificate=True";
Connection encryption is forced by SQL server. In our project we have that certificate so we can make the connection.

When we use:

Microsoft.Data.SqlClient.SqlConnection con = new Microsoft.Data.SqlClient.SqlConnection(connectionString);
con.Open(); // <-- on Windows this is around 500 ms to 1s on android same code goes above 5s on Android device, in emulator this is bellow 2s.

Already tried everything with ConnectionString properties, non of it works. It is something underlying.

Anybody got any ideas, we can mask this by opening our connection on startup but if we have 2 different connections, it is a hustle.

So if anyone has any idea why is this so slow please advise..
It is huge pitfall for our apps..

Android

[0:] First connection open: 5236ms
[0:] Second connection open: 20ms
[0:] Third connection open: 0ms

Windows:

First connection open: 499ms
Second connection open: 5ms
Third connection open: 0ms

[JRahnama]

@thevirtualdj, this does not seem like a SqlClient issue, can you provide a sample application?

[JRahnama]

@thevirtualdj what hardware did you use for testing?

[thevirtualdj]

Hardware is Android 10 device with QuadCore CPU up to 1.8 GHz and 2GB of RAM.

Lowest I can go is Microsoft.Data.SqlClient tds ssl layer.
Most of the time spent is when send initial ssl handshake, half of the time spent is when ssl stream is trying to AuthenticateAsClient to server.

Even when we try without server certificate validation (#1656 (comment)) it will still send handshake and encrypt connection. And still take this 5 seconds to open first connection.

If javax.net.ssl.Socket is used, this could be the problem. Because this is could be very slow.
In this thread I found out that it could be a potential problem.

Maybe this could be the underlying problem.
https://stackoverflow.com/questions/42417183/100times-performance-slowdown-when-using-javax-net-ssl-sslsocket

[JRahnama]

AuthenticateAsClient method can take a long time when the Certificate Revocation List (CRL) is downloaded. The delay occurs before the RemoteCertificateChainCallback is called, so it’s not the building of the cert chain or any other action there that’s causing the delay. The issue only occurs when the CRL is not cached. This is the only explanation that I could find in this scenario, now why this is happening on Android and not windows, seems like android does not store CRL and downloads it every time that method is called. One solution I can suggest is installing the root CA cert locally. That can help avoid going to the Internet for the list of trusted root CA certs.

[thevirtualdj]

Tried to install it to android trusted root certificates. Have it installed. Still the same problem.

[roji]

@JRahnama note that SslStream.AuthenticateAsClientAsync has a flag to enable/disable CRL; Npgsql exposes this via the connection string, allowing users to control whether to do this or not.

[thevirtualdj]

Have tried to manually override it thru Microsoft.Data.SQLClient code. Set it to false.
Still the same.

Our cert is in application itself.
Trusted by network security policy..
#1656 (comment)

[thevirtualdj]

Any progress on this issue, anyone?

[cheenamalhotra]

This looks like due to host name resolution timing out on Android devices, for which you can try to connect to an IP address or flush DNS cache and try to fix DNS entries to speed up connection duration.

[cheenamalhotra]

@thevirtualdj

Could you capture event source traces and share with us for investigation?
Doc for reference: https://learn.microsoft.com/en-us/sql/connect/ado-net/enable-eventsource-tracing

Additionally, can you share:

• What server endpoint are you connecting to? Is it an on-prem server or an Azure SQL instance?
• How are you performing SSL Authentication? Any traces that you can capture and share for the SSL Stream not limited to Client and SQL Server (including any external HTTP API calls)?