Calling store procedure taking long time from C# VS2013 IDE

[Sudip_inn]

i have store procedure when i am calling it from SSMS then it is taking 4/5 second but when calling the same store procedure by C# code then it is taking 20 Second. reason not clear. i use stop watch class to record time taken by calling store procedure

below code where i am calling SP and record the time

stopwatch_sp.Start();

CompSS.Business.DashboardData.FetchData("USP_Data_withCommentFlag", tickerMain, clientcode, "", domain);

stopwatch_sp.Stop();
TimeSpan timeSpan1 = stopwatch_sp.Elapsed;
string timetaken = string.Format("{0}h {1}m {2}s {3}ms", timeSpan1.Hours, timeSpan1.Minutes, timeSpan1.Seconds, timeSpan1.Milliseconds);
ViewBag.TimeElapsed = timetaken;
ViewBag.FromCache = "Calling SP";


public static System.Data.DataTable FetchData(string spName, string ticker, string Code, string fullbroker, string type)
{
string connetionString = null;
SqlConnection connection;
SqlDataAdapter adapter;
SqlCommand command = new SqlCommand();
SqlParameter param;
DataTable dt = new DataTable();

connetionString = ConfigurationSettings.AppSettings["XXXX"];
string irmodels = ConfigurationSettings.AppSettings["irmodeltype"];
string zirt = ConfigurationSettings.AppSettings["zirttype"];
string zhft = ConfigurationSettings.AppSettings["zhfttype"];
connection = new SqlConnection(connetionString);
SqlConnection conn = new SqlConnection();
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spName;
command.CommandTimeout = 0;
param = new SqlParameter("@Ticker", ticker);
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);

param = new SqlParameter("@ClientCode", Code);
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);

if (fullbroker.Trim() == "")
{
param = new SqlParameter("@withAllBrokers", DBNull.Value);
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
}
else
{
param = new SqlParameter("@withAllBrokers", fullbroker.Trim());
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
}

adapter = new SqlDataAdapter(command);

//var dataReader = command.ExecuteReader();
//var dataTable = new DataTable();
//dataTable.Load(dataReader);

adapter.Fill(dt);
connection.Close();
DataTable filteredData = new DataTable();
if ((dt.Rows.Count > 0) && (type == irmodels))
{
filteredData = WebApplication1.Business.CommonFunction.GetActualPeriods(dt);
}
else
{
filteredData = dt;
}
return dt;

}


i do not understand the reason why SP calling taking 5 second when called from SSMS but it take 20 second when calling same SP from c# code. please guide me where i am making the mistake. thanks

Continue reading...