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

1
votes

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?

1
asp.netsql-servertimeout
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
@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. – Flo
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
@SeanLange LOL! Ok, good point and taken :) I tried the command timeout instead connection timeout and it now works. – Flo

1 Answers

2
votes

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