Timeout in .NET but not in Management Studio

I have a SQL Server 2005 database which I use from ASP.NET 2.0.
I invoke stored procedures using SqlConnection, SqlCommand and SqlDataReader
from System.Data.SqlClient.

When I invoke a SP from SQL ServerManagement Studio it executes in 3 seconds
and returns 4000 rows.

When the same procedure is invoked from SqlCommand.ExecuteReader it takes
more than 30 seconds an sometimes renders a timeout in .NET.
In Profiler the invocations of the SP:s look exactly the same.

If I trace Scans they behave differently though. The time difference lies
within the Scans but also the patterns of Scan:Started - Scan:Stopped differ.
In my connectionStrings the providerName is System.Data.SqlClient.

What can make SQL Server execute the query in two different ways? They seem
to be getting different execution plans.

How can I make .NET use the faster way?

Regards Ove

William
Fri Feb 08 12:51:37 CST 2008

Let's see the code you're using to execute the query via SqlClient. Sure,
the execution plan is a function of the parameters passed. Are they the same
both times?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"nok" <nok@discussions.microsoft.com> wrote in message
news:A32FC3A3-F1F2-4E93-9A54-F3D989795D7C@microsoft.com...
>I have a SQL Server 2005 database which I use from ASP.NET 2.0.
> I invoke stored procedures using SqlConnection, SqlCommand and
> SqlDataReader
> from System.Data.SqlClient.
>
> When I invoke a SP from SQL ServerManagement Studio it executes in 3
> seconds
> and returns 4000 rows.
>
> When the same procedure is invoked from SqlCommand.ExecuteReader it takes
> more than 30 seconds an sometimes renders a timeout in .NET.
> In Profiler the invocations of the SP:s look exactly the same.
>
> If I trace Scans they behave differently though. The time difference lies
> within the Scans but also the patterns of Scan:Started - Scan:Stopped
> differ.
> In my connectionStrings the providerName is System.Data.SqlClient.
>
> What can make SQL Server execute the query in two different ways? They
> seem
> to be getting different execution plans.
>
> How can I make .NET use the faster way?
>
> Regards Ove

nok
Mon Feb 11 02:10:00 CST 2008

Hello,
Here is some more background on my problem

Connection strings, for local machine and remote

<connectionStrings>
<add name="dbDsn" providerName="System.Data.SqlClient"
connectionString="server=OVEKERWXPP2\SQL2005;database=dl2;uid=d_dlek_u1;pwd=xxxx;"/>

</connectionStrings>

(I have used the approach SqlDataReader = SqlCommand..ExecuteReader();
with the same result too)
The time is spent in adapter.Fill().
A) I invoke the code using the SQL Server local to my development machine.
It executes in 3 secs returning 4000 rows.
B) I invoke the code using the remote SQL Server.
The query takes more than 30 sec for 400 rows..
C) I invoke the Stored Procedure from SQL 2005 Mangement Studio. The Query
executes in 3 secs on both the local
and the remote server.

The query texts look identical in SQL Server Profiler. As I mentioned
they yield different Scan patterns.
My first thought was that I missed something in the input parameter typing
so that eg a string would be interpeted as
as an int causing tedious conversions and scans.

exec GetRRSelOrgShowKonto
@inPe='207',@inOrgFrom='00000',@inOrgTom='99999',@inDatumfrom='200801',@inDatumtom='200806',@inExtInt='a',@inBudget='a'

This is my code simplified

SqlConnection conn;
SqlCommand cmd = null;
DataTableReader dr;

conn = new SqlConnection(DbConnectionString);
try
{
conn.Open();
cmd = new SqlCommand("GetRRSelOrgShowKonto", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 90;
cmd.Parameters.Add(new SqlParameter("@inPe", SqlDbType.Char,3));
cmd.Parameters["@inPe"].Value = InPe;
cmd.Parameters.Add(new SqlParameter("@inOrgFrom",
SqlDbType.Char, 5));
cmd.Parameters["@inOrgFrom"].Value = InOrgFrom;
cmd.Parameters.Add(new SqlParameter("@inOrgTom", SqlDbType.Char,
5));
cmd.Parameters["@inOrgTom"].Value = InOrgTom;
cmd.Parameters.Add(new SqlParameter("@inDatumfrom",
SqlDbType.Char, 6));
cmd.Parameters["@inDatumfrom"].Value = InBokdatFrom;
cmd.Parameters.Add(new SqlParameter("@inDatumtom",
SqlDbType.Char, 6));
cmd.Parameters["@inDatumtom"].Value = InBokdatTom;
cmd.Parameters.Add(new SqlParameter("@inExtInt", SqlDbType.Char,
1));
cmd.Parameters["@inExtInt"].Value = InExtInt;
cmd.Parameters.Add(new SqlParameter("@inBudget", SqlDbType.Char,
1));
cmd.Parameters["@inBudget"].Value = InBudget;
Response.Write("Starting " + DateTime.Now.ToString());
DataSet data = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(data);
Response.Write("Finished Fill" + DateTime.Now.ToString());
dr = data.CreateDataReader();
Response.Write("Finished CreateDataReader" +
DateTime.Now.ToString());
while (dr.Read())
{
// Use the data ... (String)dr["konto"] etc.
}
}
catch (Exception ex)
{
// Ends up in a .NET timeout here if i decrease the TimeOut setting
}
finally
{
conn.Close();
}

nok
Mon Feb 11 02:38:00 CST 2008

Sorry that's 4000, typing err
> B) I invoke the code using the remote SQL Server.
> The query takes more than 30 sec for 400 rows..

me
Thu Feb 21 11:05:15 CST 2008

On Feb 11, 3:10=A0am, nok <n...@discussions.microsoft.com> wrote:
> Hello,
> Here is some more background on my problem
>
> Connection strings, for local machine and remote
>
> <connectionStrings>
> <add name=3D"dbDsn" providerName=3D"System.Data.SqlClient"
> connectionString=3D"server=3DOVEKERWXPP2\SQL2005;database=3Ddl2;uid=3Dd_dl=
ek_u1;pwd=3Dxxxx;"/>
> 
> =A0 </connectionStrings>
>
> =A0 (I have used the approach SqlDataReader =3D SqlCommand..ExecuteReader(=
);
> =A0 with the same result too)
> =A0 The time is spent in adapter.Fill().
> =A0 A) I invoke the code using the SQL Server local to my development mach=
ine.
> =A0 It executes in 3 secs returning 4000 rows.
> =A0 B) I invoke the code using the remote SQL Server.
> =A0 The query takes more than 30 sec for 400 rows..
> =A0 C) I invoke the Stored Procedure from SQL 2005 Mangement Studio. The Q=
uery
> executes in 3 secs on both the local
> =A0 and the remote server.
>
> =A0 The query texts =A0look identical in SQL Server Profiler. As I mention=
ed
> they yield different Scan patterns.
> =A0 My first thought was that I missed something in the input parameter ty=
ping
> so that eg a string would be interpeted as
> =A0 as an int causing tedious conversions and scans.
>
> =A0 exec GetRRSelOrgShowKonto
> @inPe=3D'207',@inOrgFrom=3D'00000',@inOrgTom=3D'99999',@inDatumfrom=3D'200=
801',@inDatumtom=3D'200806',@inExtInt=3D'a',@inBudget=3D'a'
>
> =A0 =A0 This is my code simplified
>
> =A0 =A0 =A0 =A0 SqlConnection conn;
> =A0 =A0 =A0 =A0 SqlCommand cmd =3D null;
> =A0 =A0 =A0 =A0 DataTableReader dr;
>
> =A0 =A0 =A0 =A0 conn =3D new SqlConnection(DbConnectionString);
> =A0 =A0 =A0 =A0 try
> =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 conn.Open();
> =A0 =A0 =A0 =A0 =A0 =A0 cmd =3D new SqlCommand("GetRRSelOrgShowKonto", con=
n);
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.CommandType =3D CommandType.StoredProcedure;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.CommandTimeout =3D 90;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inPe", SqlDb=
Type.Char,3));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inPe"].Value =3D InPe;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inOrgFrom",
> SqlDbType.Char, 5));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inOrgFrom"].Value =3D InOrgFrom;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inOrgTom", S=
qlDbType.Char,
> 5));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inOrgTom"].Value =3D InOrgTom;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inDatumfrom"=
,
> SqlDbType.Char, 6));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inDatumfrom"].Value =3D InBokdatF=
rom;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inDatumtom",=

> SqlDbType.Char, 6));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inDatumtom"].Value =3D InBokdatTo=
m;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inExtInt", S=
qlDbType.Char,
> 1));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inExtInt"].Value =3D InExtInt;
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters.Add(new SqlParameter("@inBudget", S=
qlDbType.Char,
> 1));
> =A0 =A0 =A0 =A0 =A0 =A0 cmd.Parameters["@inBudget"].Value =3D InBudget;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Response.Write("Starting "=
+ DateTime.Now.ToString());
> =A0 =A0 =A0 =A0 =A0 =A0 DataSet data =3D new DataSet();
> =A0 =A0 =A0 =A0 =A0 =A0 SqlDataAdapter adapter =3D new SqlDataAdapter(cmd)=
;
> =A0 =A0 =A0 =A0 =A0 =A0 adapter.Fill(data);
> =A0 =A0 =A0 =A0 =A0 =A0 Response.Write("Finished Fill" + DateTime.Now.ToSt=
ring());
> =A0 =A0 =A0 =A0 =A0 =A0 dr =3D data.CreateDataReader();
> =A0 =A0 =A0 =A0 =A0 =A0 Response.Write("Finished CreateDataReader" +
> DateTime.Now.ToString());
> =A0 =A0 =A0 =A0 =A0 =A0 while (dr.Read())
> =A0 =A0 =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 // Use the data ... (String)dr["ko=
nto"] etc.
> =A0 =A0 =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 catch (Exception ex)
> =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 // Ends up in a .NET timeout here if i decrease the Ti=
meOut setting
> =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 finally
> =A0 =A0 =A0 =A0 {
> =A0 =A0 =A0 =A0 =A0 =A0 conn.Close();
> =A0 =A0 =A0 =A0 }

Not sure if it is your problem or not, but I was having this same
exact behaviour: same stored procedure, different execute times
from .NET and Management Studio. Using Profiler I was able to narrow
it down to the ARITHABORT setting. It is OFF by default from .NET and
ON from Management Studio. When the setting was ON the execution time
was 1 second and when it was OFF the execution time was 47 seconds.
Not sure how, but it seemed to have something to do with my use of
table variables in the stored procedure. Once I changed the stored
procedure to use a temp table instead, the execution time was the same
regardless of the ARITHABORT setting.

nok
Thu Feb 28 10:05:04 CST 2008

Since last time I wrote I have added some indexes and added more specific
procedures too depending on the combination of input parameters. The problem
is
not so obvious in the application anymore.
I think your explanation is very likely to be correct.
My procedure contains some SUM() statements so this would be a place for
the ARITHABORT setting to make the handling of each row different.
A default .NET app would have the ARITHABORT off forcingSQL Server to
be prepared to take care of oveflows etc and still deliver a result while
Mgmt Studio would
interrupt the query on an overflow?
Thank you!

"me@nathanfrank.com" wrote:

> On Feb 11, 3:10 am, nok <n...@discussions.microsoft.com> wrote:
> > Hello,
> > Here is some more background on my problem
> >
> > Connection strings, for local machine and remote
> >
> > <connectionStrings>
> > <add name="dbDsn" providerName="System.Data.SqlClient"
> > connectionString="server=OVEKERWXPP2\SQL2005;database=dl2;uid=d_dlek_u1;pwd=xxxx;"/>
> > 
> > </connectionStrings>
> >
> > (I have used the approach SqlDataReader = SqlCommand..ExecuteReader();
> > with the same result too)
> > The time is spent in adapter.Fill().
> > A) I invoke the code using the SQL Server local to my development machine.
> > It executes in 3 secs returning 4000 rows.
> > B) I invoke the code using the remote SQL Server.
> > The query takes more than 30 sec for 400 rows..
> > C) I invoke the Stored Procedure from SQL 2005 Mangement Studio. The Query
> > executes in 3 secs on both the local
> > and the remote server.
> >
> > The query texts look identical in SQL Server Profiler. As I mentioned
> > they yield different Scan patterns.
> > My first thought was that I missed something in the input parameter typing
> > so that eg a string would be interpeted as
> > as an int causing tedious conversions and scans.
> >
> > exec GetRRSelOrgShowKonto
> > @inPe='207',@inOrgFrom='00000',@inOrgTom='99999',@inDatumfrom='200801',@inDatumtom='200806',@inExtInt='a',@inBudget='a'
> >
> > This is my code simplified
> >
> > SqlConnection conn;
> > SqlCommand cmd = null;
> > DataTableReader dr;
> >
> > conn = new SqlConnection(DbConnectionString);
> > try
> > {
> > conn.Open();
> > cmd = new SqlCommand("GetRRSelOrgShowKonto", conn);
> > cmd.CommandType = CommandType.StoredProcedure;
> > cmd.CommandTimeout = 90;
> > cmd.Parameters.Add(new SqlParameter("@inPe", SqlDbType.Char,3));
> > cmd.Parameters["@inPe"].Value = InPe;
> > cmd.Parameters.Add(new SqlParameter("@inOrgFrom",
> > SqlDbType.Char, 5));
> > cmd.Parameters["@inOrgFrom"].Value = InOrgFrom;
> > cmd.Parameters.Add(new SqlParameter("@inOrgTom", SqlDbType.Char,
> > 5));
> > cmd.Parameters["@inOrgTom"].Value = InOrgTom;
> > cmd.Parameters.Add(new SqlParameter("@inDatumfrom",
> > SqlDbType.Char, 6));
> > cmd.Parameters["@inDatumfrom"].Value = InBokdatFrom;
> > cmd.Parameters.Add(new SqlParameter("@inDatumtom",
> > SqlDbType.Char, 6));
> > cmd.Parameters["@inDatumtom"].Value = InBokdatTom;
> > cmd.Parameters.Add(new SqlParameter("@inExtInt", SqlDbType.Char,
> > 1));
> > cmd.Parameters["@inExtInt"].Value = InExtInt;
> > cmd.Parameters.Add(new SqlParameter("@inBudget", SqlDbType.Char,
> > 1));
> > cmd.Parameters["@inBudget"].Value = InBudget;
> > Response.Write("Starting " + DateTime.Now.ToString());
> > DataSet data = new DataSet();
> > SqlDataAdapter adapter = new SqlDataAdapter(cmd);
> > adapter.Fill(data);
> > Response.Write("Finished Fill" + DateTime.Now.ToString());
> > dr = data.CreateDataReader();
> > Response.Write("Finished CreateDataReader" +
> > DateTime.Now.ToString());
> > while (dr.Read())
> > {
> > // Use the data ... (String)dr["konto"] etc.
> > }
> > }
> > catch (Exception ex)
> > {
> > // Ends up in a .NET timeout here if i decrease the TimeOut setting
> > }
> > finally
> > {
> > conn.Close();
> > }
>
> Not sure if it is your problem or not, but I was having this same
> exact behaviour: same stored procedure, different execute times
> from .NET and Management Studio. Using Profiler I was able to narrow
> it down to the ARITHABORT setting. It is OFF by default from .NET and
> ON from Management Studio. When the setting was ON the execution time
> was 1 second and when it was OFF the execution time was 47 seconds.
> Not sure how, but it seemed to have something to do with my use of
> table variables in the stored procedure. Once I changed the stored
> procedure to use a temp table instead, the execution time was the same
> regardless of the ARITHABORT setting.
>

Timeout in .NET but not in Management Studio

Re: Timeout in .NET but not in Management Studio by William

Re: Timeout in .NET but not in Management Studio by nok

Re: Timeout in .NET but not in Management Studio by nok

Re: Timeout in .NET but not in Management Studio by me

Re: Timeout in .NET but not in Management Studio by nok