SqlConnection.open() slow in 2.0

microsoft.public.dotnet.framework.adonet

4 Apr 2006 1:58 PM

Peter Chapman

Hi

I have been using the SqlConnection class in version 1.1 for a while with no
problems. I have now converted my code across to version 2.0 and the open is
now incredibly slow. The exact same code takes 20 seconds to open the
connection when compiled using 2.0 and opens almost instantly when compiled
using 1.1. I have also tried using OleDb instead of SqlClient and this works
fine in 2.0 opening the connection almost immediately.

DateTime startTime = DateTime.Now;
System.Data.SqlClient.SqlConnection connection = new
System.Data.SqlClient.SqlConnection();
connection.ConnectionString = "Data Source=Server1; Initial Catalog=Master;
Integrated Security=SSPI; Connect Timeout=100;";
connection.Open();
System.Data.SqlClient.SqlCommand command = new
System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = "sp_who";
command.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlDataReader dataReader = command.ExecuteReader();
dataReader.Close();
connection.Close();
MessageBox.Show("Time = " + (DateTime.Now - startTime).ToString());

Thanks in advance
Peter

5 Apr 2006 1:07 AM

Robbe Morris [C# MVP]

I have not noticed that at all. And, you are timing far more than
the connection.Open. Are you sure that is where the slowness resides?

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp

Show quote

"Peter Chapman" <PeterChap***@discussions.microsoft.com> wrote in message
news:0A8F9A25-40FB-40A1-92C1-9DC614723FAB@microsoft.com...
> Hi
>
> I have been using the SqlConnection class in version 1.1 for a while with
> no
> problems. I have now converted my code across to version 2.0 and the open
> is
> now incredibly slow. The exact same code takes 20 seconds to open the
> connection when compiled using 2.0 and opens almost instantly when
> compiled
> using 1.1. I have also tried using OleDb instead of SqlClient and this
> works
> fine in 2.0 opening the connection almost immediately.
>
> DateTime startTime = DateTime.Now;
> System.Data.SqlClient.SqlConnection connection = new
> System.Data.SqlClient.SqlConnection();
> connection.ConnectionString = "Data Source=Server1; Initial
> Catalog=Master;
> Integrated Security=SSPI; Connect Timeout=100;";
> connection.Open();
> System.Data.SqlClient.SqlCommand command = new
> System.Data.SqlClient.SqlCommand();
> command.Connection = connection;
> command.CommandText = "sp_who";
> command.CommandType = CommandType.StoredProcedure;
> System.Data.SqlClient.SqlDataReader dataReader = command.ExecuteReader();
> dataReader.Close();
> connection.Close();
> MessageBox.Show("Time = " + (DateTime.Now - startTime).ToString());
>
> Thanks in advance
> Peter

5 Apr 2006 7:44 AM

Peter Chapman

It us undoubtably the connection open as I have timed that bit independantly
as well. I added the code to ensure that the connection was really open in
both cases.

Show quote

"Robbe Morris [C# MVP]" wrote:

> I have not noticed that at all. And, you are timing far more than
> the connection.Open. Are you sure that is where the slowness resides?
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
>
>
>
> "Peter Chapman" <PeterChap***@discussions.microsoft.com> wrote in message
> news:0A8F9A25-40FB-40A1-92C1-9DC614723FAB@microsoft.com...
> > Hi
> >
> > I have been using the SqlConnection class in version 1.1 for a while with
> > no
> > problems. I have now converted my code across to version 2.0 and the open
> > is
> > now incredibly slow. The exact same code takes 20 seconds to open the
> > connection when compiled using 2.0 and opens almost instantly when
> > compiled
> > using 1.1. I have also tried using OleDb instead of SqlClient and this
> > works
> > fine in 2.0 opening the connection almost immediately.
> >
> > DateTime startTime = DateTime.Now;
> > System.Data.SqlClient.SqlConnection connection = new
> > System.Data.SqlClient.SqlConnection();
> > connection.ConnectionString = "Data Source=Server1; Initial
> > Catalog=Master;
> > Integrated Security=SSPI; Connect Timeout=100;";
> > connection.Open();
> > System.Data.SqlClient.SqlCommand command = new
> > System.Data.SqlClient.SqlCommand();
> > command.Connection = connection;
> > command.CommandText = "sp_who";
> > command.CommandType = CommandType.StoredProcedure;
> > System.Data.SqlClient.SqlDataReader dataReader = command.ExecuteReader();
> > dataReader.Close();
> > connection.Close();
> > MessageBox.Show("Time = " + (DateTime.Now - startTime).ToString());
> >
> > Thanks in advance
> > Peter
>
>
>

5 Apr 2006 10:49 AM

Peter Chapman

After two days of hell I have finally solved this one.
It turns out that the cause of my problems stem from the windows firewall
being on on the server computer. It apears that my code was hanging while it
waited for the tcp/ip to timeout before it gave up and decided to use named
pipes. I guess 2.0 must try tcp before named pipes - I'm surprised this
isn't documented anywhere though!

Show quote

"Peter Chapman" wrote:

> It us undoubtably the connection open as I have timed that bit independantly
> as well. I added the code to ensure that the connection was really open in
> both cases.
>
> "Robbe Morris [C# MVP]" wrote:
>
> > I have not noticed that at all. And, you are timing far more than
> > the connection.Open. Are you sure that is where the slowness resides?
> >
> > --
> > Robbe Morris - 2004-2006 Microsoft MVP C#
> > Earn money answering .NET questions
> > http://www.eggheadcafe.com/forums/merit.asp
> >
> >
> >
> >
> >
> > "Peter Chapman" <PeterChap***@discussions.microsoft.com> wrote in message
> > news:0A8F9A25-40FB-40A1-92C1-9DC614723FAB@microsoft.com...
> > > Hi
> > >
> > > I have been using the SqlConnection class in version 1.1 for a while with
> > > no
> > > problems. I have now converted my code across to version 2.0 and the open
> > > is
> > > now incredibly slow. The exact same code takes 20 seconds to open the
> > > connection when compiled using 2.0 and opens almost instantly when
> > > compiled
> > > using 1.1. I have also tried using OleDb instead of SqlClient and this
> > > works
> > > fine in 2.0 opening the connection almost immediately.
> > >
> > > DateTime startTime = DateTime.Now;
> > > System.Data.SqlClient.SqlConnection connection = new
> > > System.Data.SqlClient.SqlConnection();
> > > connection.ConnectionString = "Data Source=Server1; Initial
> > > Catalog=Master;
> > > Integrated Security=SSPI; Connect Timeout=100;";
> > > connection.Open();
> > > System.Data.SqlClient.SqlCommand command = new
> > > System.Data.SqlClient.SqlCommand();
> > > command.Connection = connection;
> > > command.CommandText = "sp_who";
> > > command.CommandType = CommandType.StoredProcedure;
> > > System.Data.SqlClient.SqlDataReader dataReader = command.ExecuteReader();
> > > dataReader.Close();
> > > connection.Close();
> > > MessageBox.Show("Time = " + (DateTime.Now - startTime).ToString());
> > >
> > > Thanks in advance
> > > Peter
> >
> >
> >