Re: SqlCommand Open VERY slow on application load

From: B B (BB_at_discussions.microsoft.com)
Date: 02/04/05 

Date: Fri, 4 Feb 2005 12:13:03 -0800

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

"Andre Ranieri" wrote:

> 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?

Relevant Pages