locked
System.Data.SqlClient.SqlException: Timeout expired. with connection timeout set to 10000 RRS feed

  • Question

  •  

    I am running a stored procedure that takes less than a second to execute inside of SQL server 2005.  Then when I call it for display inside of a vb.net 2005 datagrid on a small date range the results take about five seconds to display in the grid.  When I try to execute the stored procedure on a slightly longer range inside of the program it fails with the error message:

     

     System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation o the server is not responding.  This error normally occurs because the connection timeout is not set correctly however my dataadaptor uses a connection string with a timeout of 10000.  My connection string can be seen below:

     

    Private UtilitiesConnection As New SqlClient.SqlConnection( _

    "Server=192.168.3.xxx;Database=xxxxx;User ID=xxxxxx;Password=xxxxx;Trusted_Connection=False; MultipleActiveResultSets=True; Connection Timeout=10000;")

     

    The query quies a very small amount of data.  In the past when I ran into this issue I was able to recompile the query and the data would load correctly for a very short period of time.  This work around is no longer working.

     

    The timeout message appears within ten seconds of running the query.  I’ve got about a 1000 stored procedures inside this program and none of the other calls produce this error.

     

    The code for the filling of the datagrid is below:

     

                Try

                    dgResults.BindingContext(dgResults.DataSource).EndCurrentEdit()

                Catch ExceptionMsg As Exception

                End Try

                Dim dataAdapter As SqlClient.SqlDataAdapter

                If (RadioButtonRange.Checked) Then

                    dataAdapter = New SqlClient.SqlDataAdapter(String.Format("S_StoredProc1 '{0}', '{1}'", _

                    DateTimePickerStartDate.Value.ToString("MM/dd/yyyy"), _

                    DateTimePickerEndDate.Value.ToString("MM/dd/yyyy")), connection.returnConnection)

                    Dim dataSet As New DataSet

                    dataAdapter.Fill(DataSet, "Billing1")

                    dgResults.DataSource = dataSet.Tables("Billing1")

    • Edited by David00001 Wednesday, August 12, 2009 8:03 PM data confidentiality
    • Moved by Martin Xie - MSFT Monday, August 17, 2009 10:14 AM Move it to ADO.NET forum for better support. (From:Visual Basic General)
    Wednesday, August 12, 2009 8:00 PM

Answers

  • I guess that executing this particular stored procedure from your code somehow forces SQL Server to build new query execution plan that is not efficient as it was when you executed it from inside of SQL Server Management Studio. As a start I would recommend to use named parameters when you call stored procedure instead of using string (as you do it right now). It will allow caching of query execution plan on server side. With the current way you call SP, most likely plan will be re-built each time you call stored procedure. Second, I would pass date values as dates, not as strings. It will provide SQL Server provider with the information about parameters types and should improve performance as well. Following link shows how to call parameterized stored procedures from the code 
     
    http://support.microsoft.com/kb/308049
    Val Mazur (MVP) http://www.xporttools.net
    Monday, August 17, 2009 10:35 AM

All replies

  •  

     This error normally occurs because the connection timeout is not set correctly however my dataadaptor uses a connection string with a timeout of 10000. 


    Actually, I think the ConnectionString's Timeout property only applies to establishing the connection to the server.  Try setting the Commmand object's Timeout property.  I've seen this issue a few times in the forum and this seems to always be the solution.
    Wednesday, August 12, 2009 8:17 PM
  • Thanks for pointing out the difference that helps a lot.  I added dataAdapter.SelectCommand.CommandTimeout = 10000.  Inside Visual Studio 2005 the query takes 20 seconds to respond where as it is inside of SQL I don't wait at all.  What can cause a difference like this?  I am only returning 8 rows with 8 columns.  I am using floats, varchar up to 50 characters, and some nulls.  I’ve got other queries that return far more data than this and do not produce an error.

    Wednesday, August 12, 2009 8:27 PM
  • That's a good question.  I suspect there are many factors that could influence this, so I'll recommend that you post the question in the ADO.NET forum because this is really not a VB.NET question...  and you're much more likely to get an informed response there.  Here's the link...

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads


    Wednesday, August 12, 2009 10:47 PM
  • I guess that executing this particular stored procedure from your code somehow forces SQL Server to build new query execution plan that is not efficient as it was when you executed it from inside of SQL Server Management Studio. As a start I would recommend to use named parameters when you call stored procedure instead of using string (as you do it right now). It will allow caching of query execution plan on server side. With the current way you call SP, most likely plan will be re-built each time you call stored procedure. Second, I would pass date values as dates, not as strings. It will provide SQL Server provider with the information about parameters types and should improve performance as well. Following link shows how to call parameterized stored procedures from the code 
     
    http://support.microsoft.com/kb/308049
    Val Mazur (MVP) http://www.xporttools.net
    Monday, August 17, 2009 10:35 AM