Timeout in .NET but not in Management Studio

  • Thread starter Thread starter nok
  • Start date Start date
N

nok

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
 
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)
____________________________________________________________________________________________
 
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;"/>
<!--add name="dbDsn" providerName="System.Data.SqlClient"
connectionString="server=s-ekdb1.int.lio.se;database=dlpe;uid=d_dlekonomi_u1;pwd=yyy;"/-->
</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();
}
 
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;"/>
<!--add name="dbDsn" providerName="System.Data.SqlClient"
connectionString="server=s-ekdb1.int.lio.se;database=dlpe;uid=d_dlekonomi_u1;pwd=yyy;"/-->
  </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();
        }
Click to expand...

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.
 
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!

[email protected] said:
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;"/>
<!--add name="dbDsn" providerName="System.Data.SqlClient"
connectionString="server=s-ekdb1.int.lio.se;database=dlpe;uid=d_dlekonomi_u1;pwd=yyy;"/-->
</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();
}
Click to expand...

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.
Click to expand...
 
We have some parametrized query containing aggregate operations, isnull, etc. which have caused randomically strong delay in our web application execution. Index rebuilding solved the problem but it was unpredicatable when a rebuild was needed. Index rebuilding could no be the (only)solution. Analyzing with Sql Profiler the query executions we discovered that the same query executed in Management Studio was a 1000 (!!!) factor faster then executed by .NET SQL Client. ( It operated 12 thousands reads instead of 71 millions! )

Setting ARITHABORT ON solved the problem.
 
Back
Top