Execution Timeout via ASP.NET codebehind that does not occur in SQL Server Management Studio

Question

When running my query directly in SQL Server Management Studio, it works fine and takes about 4 seconds.

When I run it within my ASP.NET app I get:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Even when I make sure I've added a timeout to the connectionstring, see my code:

web.config:

<connectionStrings>
    <add name="conn1" 
         connectionString="data source=(local)\sqlexpress;Initial Catalog=mydb;User Id=myuser;Password=mypwd;" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Code behind:

Public Shared Function GetConnection() As SqlConnection
    Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conn1").ToString)
    Return MyConnection
End Function


Dim myConnection As SqlConnection = GetConnection()
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("conn1").ToString + "Connection Timeout=900" 'timeout in seconds

Why does this behavior differ?

This is the typical sound of parameter sniffing gone bad. sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing The right way to fix this is NOT by increasing the timeout. You need to fix the performance problem. — Sean Lange
– Sean Lange, Commented Oct 13, 2016 at 19:47
@SeanLange: Thanks. But if the performance is bad, why does it run perfectly in MSSQL Management Studio? Your link talks about storedprocs whereas I'm not using those. — Adam
– Adam, Commented Oct 13, 2016 at 19:49
Well I am guessing because you didn't actually share any of your sql. I couldn't possibly know if you are using stored procedures or carrier pigeons. Are you using parameterized queries? Can you share your query so we can help? — Sean Lange
– Sean Lange, Commented Oct 13, 2016 at 19:51
@SeanLange LOL! Ok, good point and taken :) I tried the command timeout instead connection timeout and it now works. — Adam
– Adam, Commented Oct 13, 2016 at 20:03

Win · Accepted Answer · 2016-10-13 19:58:36Z

2

You want CommandTimeout instead of ConnectionTimeout.

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Dim command As New SqlCommand(queryString, connection)
    command.CommandTimeout = 900
    ...
End Using

answered Oct 13, 2016 at 19:58

Win

62.4k13 gold badges106 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execution Timeout via ASP.NET codebehind that does not occur in SQL Server Management Studio

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related