Stored Procedure is slow, takes 3 times longer in code than SSMS

Question

I am facing a problem related “Cannot find table 0”. Initially I have no idea to find the root problem of this exception. Then I came to know that this problem arose due to the error “Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding”. (i.e) The execution of the Stored procedure in the SQL server 2005 takes time more than 3 seconds (default query timeout is 3 seconds). So I executed the same stored procedure with same parameters in SSMS (SQL Sever Management Studio). But it took only 1 second.

In the middle of time, we run the same source and SQL code in another server (Backup server). But there is no error. (i.e) The whole process (I have a do while loop which loops 40 times , consisting 4 for loops which loops approximately 60 times.Each time it will access 3 stored procedures) took 30 minutes in my system but in backup server only 3 minutes has been taken which means there is no timeout. But all the source are same. So now I came to an end that there is a technical issue involving.

I tried the following things in my source.
•   In Asp.net, I added the “SQLCommand Timeout = 0”. But it is a failure.
•   I used “SET ArithAbort ON” and “With Recompile”. It is also a failure.
•   Then I used “Parameter Sniffing”. (i.e) I used local variables in stored procedures. But all went in a wrong direction.

The Ado.net code for accessing the stored procedure in asp.net is like the following:

 Public Function retds1(ByVal SPName As String, ByVal conn As SqlConnection, Optional ByVal ParameterValues() As Object = Nothing) As DataSet
    dconn = New SqlConnection(ConfigurationManager.ConnectionStrings("webriskpro").ConnectionString)
    Try
        sqlcmd = New SqlCommand
        ds = New DataSet
        If dconn.State = ConnectionState.Open Then dconn.Close()
        sqlcmd = New SqlCommand(SPName, dconn)
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandTimeout = 0
        dconn.Open()
        SqlCommandBuilder.DeriveParameters(sqlcmd)
        If Not ParameterValues Is Nothing Then
            For i As Integer = 1 To ParameterValues.Length
                sqlcmd.Parameters(i).Value = ParameterValues(i - 1)
            Next
        End If
        da = New SqlDataAdapter(sqlcmd)
        da.SelectCommand.CommandTimeout = 0
        da.Fill(ds)
    Catch ex As Exception
        send_prj_err2mail(ex, SPName, "")
    Finally
        dconn.Close()
    End Try
    Return ds
End Function

The execution plan of the error giving procedure is

Hope you guys understood my problem. Please get me back with some ideas!!

Answer 1

I think the exception is because of the connection opening time.

You need to specify the timeout for the connection or delete the pooling=false from the connection string.

Give like this,

<add name="db1" connectionString="Data Source=your server;Initial Catalog=dbname;User ID=sa;Password=*****;connection timeout=600;"/>

The default size of the pooling is 100. If you would like to change you then you can.

Since in the backup server there will be no applications running, the speed of the system will be high and there will be no interrupts.

But in your local system, you might have worked on some other applications.So that could be one of the reason. The next time when you run, close all the opened application then check in the sql profiler.

Since the same code is working fine in the backup server, i dont think the loops(60*40 times) will not be the reason for slowing down.

Best of luck!!