DataAdapter.fill(dataset) timeout exception on a specific database
I've found a solution.
It turned out to be a parameter sniffing problem for query optimalisation. I needed to add OPTION(RECOMPILE) to the sql that creates the stored procedure.
the issue is explained here:
And here:
Gert Hermans
Updated on June 04, 2022Comments
-
Gert Hermans almost 2 years
In a VB.NET application (VS2005) I am calling a stored procedure through a SQLDataAdapter. On my local database everything works fine. If I do this on another database I get a timeout exception. this is the code :
Public Overloads Shared Function ExecuteDataset( _ ByVal connection As SqlConnection, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray commandParameters() As SqlParameter) As DataSet If (connection Is Nothing) Then Throw New ArgumentNullException("connection") ' Create a command and prepare it for execution Dim cmd As New SqlCommand Dim ds As New DataSet Dim dataAdatpter As SqlDataAdapter Dim mustCloseConnection As Boolean = False dataAdatpter = Nothing PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection) cmd.CommandTimeout = 0 'I get a timeout exception if I leave this out Try ' Create the DataAdapter & DataSet dataAdatpter = New SqlDataAdapter(cmd) ' Fill the DataSet using default values for DataTable names, etc dataAdatpter.Fill(ds) ' Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear() Finally If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose() End Try If (mustCloseConnection) Then connection.Close() ' Return the dataset Return ds End Function ' ExecuteDataset
I've executed the stored procedure directly on the database and that works fine and fast. I've also tried to set the CommandTimeout property of cmd to 0. When I do this the stored procedure gets executed but this takes a very long time. Again if I work on a local db there are no problems.
Here is the stacktrace of the timeout exception
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at KlantenApplicatie.BL.DAL.SQLHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\BitProjects\teamsystem\gerher\KlantenApplicatie.BL.DAL\sqlHelper\SQLHelper.vb:line 545
Any ideas on what might be the cause of this?