Suddenly slow ado.net connections

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Last week we were running some code that connects to two different SQL 7
servers. On Friday, the connection times were normal (almost instananeous).
Today, from all of our development boxes, the connections are taking 5-7
seconds to open. The app is written using the 2.0 framework.

In our code we

connect
do some work
disconnect

Operations that took seconds on Friday are taking nearly an hour today.
Our connection strings didn't change and we are using the native SQLClient
namespace.

For a test we decided to try creating a test app that opens a connection
then while that connection is open, open several more connections to see if
pooling comes into play. Oddly enough it seems pooling isn't being used.
Each connection still takes 5-7 seconds to open. The connection string we
are using is simply "Server=server;Database=db;Integrated Security=SSPI" -
nothing out of the ordinary.

We even sent so far as to create a test app in VB6 using ADO 2.7 to see if
it was something on the our network. That test app opens the connections as
normal (nearly instantaneous) so the problem appeared to be with ADO.NET

We then coded up the same simple test app in VS2003 and it ran just fine.
At this point we are at a loss and thinking something is up with ADO.NET and
the 2.0 framework on all our dev boxes.

Has anyone got any ideas on what we are seeing?

Here is our actual test app code (server and db changed for posting)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim cn As New
SqlClient.SqlConnection("Server=server;Database=db;Integrated Security=SSPI")
cn.Open()


End Sub

In VB6/ADO and VB.NET 2003/ADO.NET 1.1 this connects in less than one
second. In VB.NET 2005/ADO 2.0 this takes 5-7 seconds.
 
Are you using perfmon to monitor the open connections? If so, how many
does it show?

The following statement doesn't make much sense:

"For a test we decided to try creating a test app that opens a
connection
then while that connection is open, open several more connections to
see if
pooling comes into play. Oddly enough it seems pooling isn't being
used.
Each connection still takes 5-7 seconds to open."

Why would pooling be used if you're not closing the connection? Of
course it's going to create (well, try to create) a new connection if
you still haven't closed the initial one. Your test app connections
will eventually be closed but only when Dispose() is called on the
abandoned object and you really have no control over that.
 
The bottom line is this

ADO.NET 2.0 Framework
SQL Server 7.0
Integrated Security=SSPI

Connect via OleDb.OleDbConnection --> Connection is instantaneous
Connect via SqlClient.SqlConnection --> Connection takes 5-7 seconds

Take the exact same code and run it in this configuration

ADO.NET 1.1 Framework
SQL Server 7.0
Integrated Security=SSPI

Connect via OleDb.OleDbConnection --> Connection is instantaneous
Connect via SqlClient.SqlConnection --> Connection is instantaneous

Here is the code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New
SqlClient.SqlConnection("Server=Server;Database=db;Integrated Security=SSPI")

Me.Text = "Connecting " & Now.ToString
cn.Open()
Me.Text = "Connected SQLClient 7.0 " & Now.ToString

cn.Close()
cn.Dispose()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim cn As New OleDb.OleDbConnection("Provider=sqloledb;Data
Source=Server;Initial Catalog=db;Integrated Security=SSPI")

Me.Text = "Connecting " & Now.ToString
cn.Open()
Me.Text = "Connected SQLClient 7.0 " & Now.ToString

cn.Close()
cn.Dispose()
End Sub

This condition exists on all our dev boxes as well as end user workstations
against development servers as well as production servers. For now our only
solution is to recode our data layer for oledb because we cannot explain the
sudden slowdown in connection times. This is not the option we were hoping
for but we appear to have no choice at this time.
 
Have you tried to use an IP address for your server may be your dns is not
resolving quick enough.

Lit
 
Lit,

Yes we did try and it didn't work. That makes sense because we think we have
it. We think the problem was that the TCP/IP connection was timing out and
then connecting via named pipes. We changed the connection statement to use
the named pipes network library and all seems to work.
 
Back
Top