Slow SQL Connection using .NET5 vs .NET Framework 4.7

Question

question

JPCControls asked May 4, '22 karenpayneoregon commented May 5, '22

Slow SQL Connection using .NET5 vs .NET Framework 4.7

I've got two very basic c# applications. One targeting .NET Fremework 4.7.2, the other targeting .NET 5.0. They were created at the same time, they are very simple programs that just connect to an SQL database using a connection string, perform a simple query, then disconnect.

I'm amazed at the difference in connection time between the two versions. The .net fremework 4.7 version will connect in 2.5 seconds.

The .NET 5.0 verison takes over 22 seconds to connect. Once connected the data transfer is fast enough, but the initial connection is crazy.

We even tried .NET 6.0 but got the same results.

Has anyone seen anything like this?

Here's the connection string:

connstring= "Server=servername\instancename;User Id=username;Password=***;Encrypt=false;"

SqlConnection conn = new SqlConnection(connString)

We're using System.Data.SqlClient

dotnet-csharp dotnet-sqlclient

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JPCControls · May 04 at 08:04 PM

Here's the program, not much to it. 198948-form1.txt

0 ·

form1.txt (4.0 KiB)

Answer 1 · 2022-05-04T21:31:56.827Z

karenpayneoregon answered May 4, '22 karenpayneoregon commented May 5, '22

First test local, going to do a remote next

Remote test to one of our data centers, faster than the local connection

figure1.png (36.6 KiB)

figure2.png (36.5 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JPCControls · May 05 at 01:06 PM

Although commenting out that line didn't make a difference for us, your input helped guide us to this solution. It ended up being our TCP/IP settings in the server configuration.

https://www.c-sharpcorner.com/article/configure-named-pipe-and-tcpip-settings-of-sql-server/

We have rules added to our firewall for TCP port 1433 and UDP port 1434, but the SQL server wasn't configured for that.

The key for us was to properly configure the SQL server to use port 1433. By default, our settings had some random port in the 50,000 range for the TCP Dynamic Ports setting and blank for the TCP port. We revised that to blank for TCP Dynamic ports and 1433 for TCP port.

Now the connection is fast every time. Your servers were probably already configured for that setting. That's most likely why it was always fast for you and slow for us. Thank you for your help and for testing our program, it confirmed that it wasn't our code, and led us to look into the network more.

0 ·

karenpayneoregon JPCControls · May 05 at 01:09 PM

Good to hear all is working now.

0 ·

Answer 2 · 2022-05-04T20:30:07.837Z

karenpayneoregon answered May 4, '22 karenpayneoregon commented May 4, '22

Doubtful it is the connection. I've never had an issue with connecting with .NET Core projects.

Checkout the following project, I connect in less than one second.

My guess is dAdapter.Fill(ds); connects fine but something else is going on other than the actual opening of the connection.

· 6

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JPCControls · May 04 at 09:06 PM

Thanks for the sample code, however it still takes 20 seconds to connect. Now I'm starting to wonder if it's something on our network. But why would 4.7 be so much faster?

One thing we found was that if we use named pipes, it goes much quicker. But we've been told that named pipes are bad. Would you have any reservations using named pipes?

0 ·

karenpayneoregon JPCControls · May 04 at 09:11 PM

So you tried my exact code sample no command or adapter and it still took 20 seconds?

Are using using NuGet package System.Data.SqlClient 4.8.3 ?

0 ·

JPCControls karenpayneoregon · May 04 at 09:23 PM

Yes, the 20 seconds was with running the exact sample project.

We confirmed that we're using SqlClient 4.8.3 also.

0 ·

JPCControls JPCControls · May 04 at 09:11 PM

Also, I failed to mention that the delay only occurs when SQL server is on a different computer. If I connect to a server on my computer, it's always fast.

If you would be willing, we have posted a test program here:
https://github.com/JPC-Controls/SqlConnTest

I'm curious if it is also slow for you?

0 ·

karenpayneoregon JPCControls · May 04 at 09:24 PM

Looking at in now, first thing I did was removed

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.0-preview2.22096.2" />

Looking at more now.

0 ·

karenpayneoregon JPCControls · May 04 at 09:40 PM

Check out my findings below

0 ·

question