vb.net 特定数据库上的 DataAdapter.fill(dataset) 超时异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14119081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DataAdapter.fill(dataset) timeout exception on a specific database
提问by Gert Hermans
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 :
在 VB.NET 应用程序 (VS2005) 中，我通过 SQLDataAdapter 调用存储过程。在我的本地数据库上一切正常。如果我在另一个数据库上执行此操作，则会出现超时异常。这是代码：
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.
我已经直接在数据库上执行了存储过程，并且运行良好且速度很快。我还尝试将 cmd 的 CommandTimeout 属性设置为 0。当我这样做时，存储过程会被执行，但这需要很长时间。同样，如果我在本地数据库上工作，则没有问题。
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?
关于可能是什么原因的任何想法？
采纳答案by Gert Hermans
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.
结果证明这是一个用于查询优化的参数嗅探问题。我需要将 OPTION(RECOMPILE) 添加到创建存储过程的 sql 中。
the issue is explained here:
这个问题在这里解释：
And here:
和这里：
