SqlCommand Open VERY slow on application load

[Guest]

Okay, here is what's happening:

I have a reasonably fast laptop (1.4 GHz Mobile M, so comparable to 2.5GHz
P4) doing .net development.
Running Windows XP pro, SP2
IIS is installed and running fine
All SQL Servers I am referring to share a small (10 computers or so) LAN
with a 100MB Switch.
No other computers on the LAN exhibit this problem.

The situation is this:
Whenever I start my .net application from scratch (either by
stopping/restarting IIS or recompiling the application dll), the first
SqlConnection "open" statement takes 45 seconds to open the SQL connection.
It is nearly exactly 45 seconds every time, which makes me think it's some
sort of timeout.

The code:
SqlConnection conn = new SqlConnection("user
id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30");
conn.Open(); // this line takes 45 seconds to execute the first time only

I have been researching this and trying things for two days, so please note
that:
A) I have re-installed .net Framework 1.1 SP1.
B) I have re-installed SQL Server SP3a.
C) I have tried turning pooling on and off deliberately in the connection
string. When I turn pooling off, every subsequent call to this function
takes a long time.
D) I have verified that this super-slow login occurs between this laptop and
each of 3 different SQL Servers: one running server 2003, one running 2000
server, and one running on a XP Pro machine (so it is probably not the SQL
Server machine itself)
E) I have verified that it happens in every .net project (tried 3) that uses
a SqlConnection. (so it's not the particular project settings).
F) I have turned off the QOS packets for the NICs (so it's probably not QOS
packet overhead)
G) I have repeated the problem both connected through a 100MB switch (at
various speeds, from 10MB half duplex through 100MB full) and connected via
802.11b wireless and I have set the ethernet adaptive retry to 40 instead of
1 (so it's probably not collisions).
H) I have disabled 'named pipes' in the SQL Server Network Utility on both
the client and the server.
I) I have tried with another user id in the connection string.

More information:
I can open enterprise manager and connect to any of these SQL Servers in
less than 10 seconds. I can create an ODBC DSN and 'test' it with no
perceivable delay. It is only SqlConnection.Open() that exhibits the
slowness, and it does it EVERY TIME.

The connection strings I've tried:
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30;Trusted_Connection=False"
(same result)
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30;Trusted_Connection=True"
(fails because trusted connection doesn't work)
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=10.0.10.10,1433;DNetwork Library=DBMSSOCN;Connect Timeout=30;"
(same result)
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30;Pooling=true"
(same result)
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30;Pooling=false"
(same result, but all calls to open take long time)
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=2"
(same result)
"user id=<anotherID>;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30"
(same result).

As I said, I have researched this on google and MS for over 2 days now and
have tried most things I found there. I would appreciate any suggestions as
long as they don't repeat things I've tried above!

Thanks,

B B

 

[Kevin Spencer]

I feel your pain.

However, you're going to kick yourself when you find out what the issue is.
It has nothing to do with Connection Strings, and very little at all to do
with a database at all. It has everything to do with .Net, and specifically,
ASP.Net.

The clue to your problem (nicely explained, BTW), lies in 1 phrase from your
post:

Whenever I start my .net application from scratch (either by
stopping/restarting IIS or recompiling the application dll),

..Net uses managed code. This means that the code has a lot of hidden
overhead associated with the management of the code. One of the ways that
Microsoft optimized the speed of execution was with judicious and liberal
use of caching. .Net objects are cached the first time they are used. The
most expensive part of using them is creating them. Therefore, ANY .Net
application will take some time to load the first time. Interestingly, this
even applies to executables, which I imagine are also cached the first time
they are used, and Garbage Collected later. If you open a .Net executable,
it takes awhile to load. If you close it and immediately open it again, it
loads much faster.

The same goes with an ASP.Net application, especially when you first start
the web Application. The reason is, that a lot of code has to be loaded when
you first start the Application, not just the Page requested, but the entire
Application, Session, etc. It all has to be loaded from disk. The next
request does not require all that loading, as the objects are now cached.

Database Connections fall along similar lines. Opening a database connection
is the most expensive thing you can do with a database in a web app (other
than fetching huge result sets, or executing huge Stored Procedures). For
this reason, .Net uses Connection Pooling. The first time a Connection is
opened, it is built from scratch. When you close and/or dispose the
Connection, it is not destroyed, but returned to the Connection Pool. The
next time you open a Connection with the same parameters (Connection
String), it fetches the Connection from the pool.

So, what you're seeing is normal, nothing to worry about, and will only
happen to the occasional user. Take a break, and congratulate yourself on
your great detective work!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Guest]

Kevin, thank you for your answer.

However, I left out (my fault) one valuable piece of information: on
another laptop of the same speed, I can start the app from a recompile in
under 15 seconds. In fact, another developer working on this application
over a VPN connection over the internet - and connecting to our DB server
over that VPN connection - can start the application from scratch in under 30
seconds.

I can step-through debug, and verify that when the debugger is at the
conn.Open();
statement, I press F10 to step over it, and it takes 45 seconds to perform
that one single command (so we're not talking about the .net DLL-loading
managed-code overhead). And remember from my initial post:
I tried this connection string:
"user id=sa;password=thepassword;initial catalog=thecatalog;data
source=THESERVER;Connect Timeout=30;Pooling=false"

and got the same slow results (so it's not Pooling overhead).

so to summarize:
I think your point is well-intended but overlooks that A) I turned off
pooling and got the same result, and the inadvertantly omitted B) 2 other
comparable machines do not experience this level of delay, including one that
connects over a slow VPN connection.

Thanks again for the reply, but I need a little more!

 

[Kevin Spencer]

I think your point is well-intended but overlooks that A) I turned off

pooling and got the same result, and the inadvertantly omitted B) 2 other
comparable machines do not experience this level of delay, including one
that
connects over a slow VPN connection.

A) That would not have any effect. The first time you create a Connection it
is pooled. So, the first Connection is created from scratch, pooling or not.

B) The difference between 30 and 45 seconds is not much. There could be any
number of factors at work there.

I'm going to stick to my original advice:

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Guest]

I have to think that 45 seconds to create a SqlConnection is not a normal
thing, even for managed code like .NET.

If you run SQL Profiler, do you start seeing the log fill up as soon as you
F11 step into the conn.Open() line or does the Profile log sit empty for the
45 seconds and fill at the end? How about memory usage on your laptop? does
it hit the roof when it hits this line? I'm guessing it doesn't.

I'm running a very similar situation, 2.4GHz Pentium IV 2.4 notebook w/ 1 mb
memory, currently developing an ASP.NET app that includes web.config
connection strings to two separate SQL 2000 servers. I'm not seeing any
noticable delay when creating, instantiating and opening a SQLConnection
object.

One last point of curiosity: it looks like your first call to the database
is through a SqLCommand to a datareader of something like that. What if your
first call is to fill a dataset using a data adapter? I know a da
automatically handles connection state for the connection, maybe that would
be faster?

 

[Guest]

Thanks for your post, Andre. I would buy Kevin's theory if I didn't have two
other comparably fast machines that did not experience this delay at all -
and if one of them weren't connecting twice as fast over a 512Kbps vpn.

Profiler: The profiler is empty for the entire 45 seconds, and finally ends
up with a login message right at the end when it connects - as if it's having
trouble even opening the socket.

The delay was the same when we did not explicitly open the connection The
only reason we explicitly put the 'open' in was the narrow down the slowness.
Getting rid of it only moves the slow speed to the first command that
actually fetches data.

My next step - that I am not looking forward to - is to run ethereal and try
to monitor packets. But the most reliable way to do that is to form a mini
subnet on a hub (not a switch) and monitor with a 3rd computer; hubs are hard
to find these days.

I remember reading somewhere that there is some sort of default login
timeout of 45 seconds that can be configured - does anyone know what this
might be?

Thanks again for your help,

Brian

 

[Guest]

Hey, don't give up on me everybody! I still haven't solved this! Does
anyone have any other suggestions? Does anyone from MS actually monitor this
group?