CODEWITHSUNDEEP
c#sql-server
Yogendra Bhardwaj
Yogendra Bhardwaj

Reputation: 95

sql stored procedure calling from .net

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(); 

}

Upvotes: 0

Views: 1064

Answers (4)

Marnix van Valen
Marnix van Valen

Reputation: 13673

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

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.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300489

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

exec sp_updatestats

Upvotes: 1

cjk
cjk

Reputation: 46415

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

Upvotes: 1

Related Questions