long running sql procedure hangs website

Question

I have an MVC 4 web application that calls the business layer which calls a data layer method ExecuteSP asynchronously. The data layer method ExecuteSP calls a long running stored procedure like this:

public class MainDataLayer
{
    private Database database;  //Enterprise library 5 
    private DbCommand dbc;
    private List<SPParams> spParams;

    public MainDataLayer()
    {
        _database = DatabaseFactory.CreateDatabase("strConn");

        //set multiple sp params like the one below
         _spParams.Add(new SPParams() { ParamName = "@pID", ParamValue = ID }); 
    }

    public DataSet ExecuteSP()
    {

        dbc = database.GetStoredProcCommand('long_running_sp');
        dbc.CommandTimeout = 300;

        foreach (SPParams p in spParams)
        {
            dbc.Parameters.Add(new SqlParameter(p.ParamName, p.ParamValue));
        }

        DataSet ds = _database.ExecuteDataSet(dbc);

        return ds;
}
}


//this class is used in the class above for setting the sp parameters
public class SPParams
{
    public string ParamName { get; set; }
    public object ParamValue { get; set; }

}

The long running stored procedure has a few inserts and a few updates and the final time out call as below to simulate a long running process.

WAITFOR DELAY '0:02:00'

The problem is when I call this stored procedure thru my web application deployed on my test server from client pc A, it hangs all further calls from the same client or any other user calling the webpage until the 2 minutes in the stored procedure have elapsed. When I run the stored procedure directly from sql management studio and run another sql statement side by side, the stored procedure takes 2 minutes to execute and the other sql statement executes immediately. It doesnt look like there is something on the database side that I need to change. What could be the problem with my code?

Thanks

Use an `AsyncController` instead? You can also check on its status while it's still executing using something like this: http://stackoverflow.com/questions/2927284/need-an-asp-net-mvc-long-running-process-with-user-feedback — Brad Christie, May 14 '13 at 14:49
Seems like sql-server is using different execution plans when you run the query directly versus being called from the website, you might need to do some digging with the profiler to see the actually generated queries and use something like "OPTION FORCE ORDER" to enforce a specific execution plan, besides I think the problem lies in the sql code not in the .net-code calling it — DrCopyPaste, May 14 '13 at 15:04
It's hard to comment why based on this, but I suspect you have bad indexing. Look at the query plan, fix the stored procedure — gbn, May 14 '13 at 15:07
Hi gbn, if the indexing was bad, it should have had the same effect when running from the sql client side, right? There the second sql in another window executes quickly without waiting for the long running sql to finish in the first window... — user20358, May 14 '13 at 15:26
"if the indexing was bad, it should have had the same effect when running from the sql client side, right?" NO! sql server will try to figure out the most efficient execution plan itself depending on the data being used, thats why you should take a look at the actual queries being executed in the profiler and then try something like FORCE ORDER to enforce what you think is happening already ;) — DrCopyPaste, May 15 '13 at 09:45

long running sql procedure hangs website

0 Answers0