question

JPCControls avatar image
0 Votes"
JPCControls asked karenpayneoregon commented

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-csharpdotnet-sqlclient
· 1
5 |1600 characters needed characters left characters exceeded

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

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


0 Votes 0 ·
form1.txt (4.0 KiB)
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

First test local, going to do a remote next

199021-figure1.png

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

199005-figure2.png



figure1.png (36.6 KiB)
figure2.png (36.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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

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 Votes 0 ·

Good to hear all is working now.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

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

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

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 Votes 0 ·

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 Votes 0 ·
JPCControls avatar image JPCControls karenpayneoregon ·

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

We confirmed that we're using SqlClient 4.8.3 also.

0 Votes 0 ·

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 Votes 0 ·

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 Votes 0 ·

Check out my findings below

0 Votes 0 ·