sql stored procedure calling from .net

Question

HI,

I have a sql stored procedure, which i am calling from my asp.net (2.0) code by using sqlCom.ExecuteNonQuery(), but it tooks around 60 to 100 second to complete, and while i execute the same SP from sql query tool runs in 3 to 4 seconds.

Please help on this.

Edit - code from comments:

public int ExecuteNonQuery(string strSpName, DbParameter[] parameterValues) 
{ 
    CreateConnection(); 
    SqlCommand sqlCom = new SqlCommand(); 
    if (strSpName == null || strSpName.Length == 0) 
       throw new ArgumentNullException("strSpName"); 
    int i = 0; 
    sqlCom.Connection = _sqlConn;
    if (_blnIsTransEnabld == true) 
       sqlCom.Transaction = _sqlT; 
    sqlCom.CommandType = System.Data.CommandType.StoredProcedure; 
    sqlCom.CommandText = strSpName; 
    sqlCom.CommandTimeout = _sqlConn.ConnectionTimeout; 
    return sqlCom.ExecuteNonQuery(); 

}

HI, I am having performnace issue with SP while calling from c# code and same sp complete in 3 seconds while execute from SQL Query — Yogendra Bhardwaj, Jul 12 '10 at 11:01
public int ExecuteNonQuery(string strSpName, DbParameter[] parameterValues) { CreateConnection(); SqlCommand sqlCom = new SqlCommand(); if (strSpName == null || strSpName.Length == 0) throw new ArgumentNullException("strSpName"); int i = 0; sqlCom.Connection = _sqlConn; if (_blnIsTransEnabld == true) sqlCom.Transaction = _sqlT; sqlCom.CommandType = System.Data.CommandType.StoredProcedure; sqlCom.CommandText = strSpName; sqlCom.CommandTimeout = _sqlConn.ConnectionTimeout; return sqlCom.ExecuteNonQuery(); — Yogendra Bhardwaj, Jul 12 '10 at 11:09

score 1 · Answer 1 · edited May 23 '17 at 12:26

1

Could this SO question about query being fast in SQL but slow as a SP be the problem?

edited May 23 '17 at 12:26

Community

1
1

answered Jul 12 '10 at 10:51

cjk

45,739
9
81
112

HI, I am having performnace issue with SP while calling from c# code and same sp complete in 3 seconds while execute from SQL Query – Yogendra Bhardwaj Jul 12 '10 at 11:02

score 1 · Answer 2 · answered Jul 12 '10 at 10:51

1

This is often a symptom of having out of date statistics. Suggest you rebuild statistics:

exec sp_updatestats

answered Jul 12 '10 at 10:51

Mitch Wheat

295,962
43
465
541

score 1 · Answer 3 · edited May 23 '17 at 10:32

I would suggest not updating statistics or making any alterations to the stored procedure until you have gathered the execution plans as this will drop the execution plan in the cache and foil any attempts to investigate further whether that was the actual issue.

The drop of the plan may lead to you thinking the issue is solved but if the problem is parameter sniffing it will likely reappear.

First get the execution plans for the SSMS and C# ones by following the advice in this answer.

Very different execution times of SQL query in C# and SQL Server Management Studio

Once you've got those saved can you update your question with the XML for both of them?

Edit: Actually probably better would be to use SQL profiler to capture Showplan XML Statistics Profile events and get the actual execution plan for the C# one. Don't leave the profiler trace running for any longer than the bare minimum if it is a production server.

score 0 · Answer 4 · answered Jul 13 '10 at 13:12

First of all, looking at the code I hope you have something in place to close the connection you are using here.

As for your problem, any chance there is another query pending on a transaction that has issued locks on the data you are accessing? That would explain the long execution time; if the data you are accessing is locked by some other query, execution will wait for the locks to be released or timeout.

To determine if there is a locking issue you can use SQL Server Management Studio. Open Management / Activiy Monitor to get a list of pending locks.

To prevent locking issues make sure you commit or rollback all pending transactions as soon as possible. There are several ways to resolve deadlocking. Usually you can prevent it all together for example by reordering queries in a transaction. If that is not an option you may want to change the isolation level of the transactions or use table hints.

sql stored procedure calling from .net

4 Answers4