3

I have a stored procedure that is being executed from a legacy ASP.NET WebForms application that is timing out. The CommandTimeout property has been set to 6 minutes. The stored procedure queries a table with 15 million rows in it and joins to other tables.... so it is slow. However, it only returns 20 rows, so network performance is not the issue here.

var data = new DataSet();

using (var connection = GetConnection())
using(var command = connection.CreateCommand())
using(var dataAdapter = new SqlDataAdapter(command))
{
   command.CommandTimeout = 360;
   command.CommandType = CommandType.StoredProcedure;
   command.CommandText = "praGetTradesForProductIdReassignment";
   command.Parameters.AddWithValue("@int4_TradeId", (object)tradeId ?? DBNull.Value);
   command.Parameters.AddWithValue("@int4_FeedId", (object)feedId ?? DBNull.Value);
   command.Parameters.AddWithValue("@int4_SystemId", (object)systemId ?? DBNull.Value);
   command.Parameters.AddWithValue("@int4_TradeCode", (object)tradeCode ?? DBNull.Value);
   command.Parameters.AddWithValue("@int4_ProductID", (object)productId ?? DBNull.Value);
   command.Parameters.AddWithValue("@sortColumn", sortColumn);
   command.Parameters.AddWithValue("@sortDirection", sortDirection);
   command.Parameters.AddWithValue("@pageIndex", pageIndex);
   command.Parameters.AddWithValue("@pageSize", pageSize);

   dataAdapter.Fill(data);                
}

return data;

When I execute the same stored procedure, with the same parameters from within SQL Server Management Studio, it completes successfully after about 50 seconds.

I've run a SQL Trace using Profiler which shows that the ADO.NET execution is taking way longer than the SQL Server Management Studio one (50 vs 356 seconds).

  <Events>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>
      <Column id="1" name="TextData">exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20</Column>
      <Column id="13" name="Duration">49946289</Column>
    </Event>
    <Event id="10" name="RPC:Completed">
      <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
      <Column id="1" name="TextData">exec praGetTradesForProductIdReassignment @int4_TradeId=NULL,@int4_FeedId=NULL,@int4_SystemId=NULL,@int4_TradeCode=NULL,@int4_ProductID=NULL,@sortColumn=N'',@sortDirection=0,@pageIndex=0,@pageSize=20</Column>
      <Column id="13" name="Duration">356352721</Column>
    </Event>
  </Events>

Why does the ADO.NET execution take so much longer than the SSMS execution?

The purpose of this question is not to improve the performance of the stored procedure, but rather to determine the reason for the difference in performance between an invocation via ADO.NET vs SSMS.

John Mills
  • 10,020
  • 12
  • 74
  • 121
  • Look at the execution plan. It might vary based on the `SET ... ON/OFF` options you have. SSMS has its own defaults. – ta.speot.is Jul 07 '15 at 06:43
  • what is the connection timeout value given for that database ? – Tharif Jul 07 '15 at 06:45
  • Profiling your query will help you to find out the bottleneck... and performance will depend on how you perform your joins.... Those are not really performant, and can lock a lot of data. Personally I prefer to create a master query with only data I need to calculate and set the result in a temporary table. Then once the query is done I create the data set with one multiple join, or by update my tmp table with joining data one by one ... – Laurent Lequenne Jul 07 '15 at 06:48
  • Just a guess, maybe network is slow between web and DB servers? Are you running SSMS on DB server? – cyberj0g Jul 07 '15 at 06:53
  • @ta.speot.is how do I check the execution plan via ADO.NET? I only know how to look at it when run from SSMS. – John Mills Jul 07 '15 at 22:20
  • Have you seen this? http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – Joel Coehoorn Jul 08 '15 at 03:40
  • Perhaps my question wasn't very clear. I've updated it. I'm not trying to understand how to improve the performance of the stored procedure, I'm trying to understand why the performance of the stored procedure is different depending on how it is invoked. – John Mills Jul 08 '15 at 03:40

1 Answers1

6

First of all, It is important to know that ADO.NET and SSMS (SQL Server Management Studio) are different entities. Hence, You should consider that there are certain behavioral/operational changes in query execution.

Part 1 : Why SQL query is running faster in SSMS than ADO.NET?

When you run any query in SSMS, it always creates a query plan for it. The query plan is executed with various optimization as well as indexing of searches result. You could imagine some sort of caching is involved in this process.

Also, When Microsoft developed both SSMS and ADO.NET, not all the parameters are same in both. You will find the settings for parameters like ANSI_NULLS, ARITHABORT is not same as in SSMS against ADO.NET

If you want to make sure that your execution of SQL Query is as per SSMS itself, please ensure that settings in both cases is same.

Part 2 : What can you try out?

There are couple of ways you can try out to fix your problem.

Enable ARITHABORT Parameter : The settings for ATITHABORT is different for ADO.NET & SSMS. You can try to turn on ARITHABORT in your query and execute. 

SET ARITHABORT ON

Enable ANSI_NULLS : Change the settings for ANSI_NULLS to Enabled in your query-text.

SET ANSI_NULLS ON

In similar ways, you can make settings in SSMS and ADO.NET alike resulting into much close execution results.

Part 3 : Parameter Sniffing

Last but not the least, It also important to understand the structure of your queries. You can optimize the SQL query by using the local variables in stored procedure, making use of of cursors wherever possible and so on.

Part 4 : References

  1. Article from Erland Sommarskog

  2. Parameter Sniffing

Hope it helps...All the best!!

Ajinkya
  • 91
  • 8