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;"/>
> > <!--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();
> > }
>
> 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.
>