超时已过期.操作完成前经过的超时时间，或者服务器未使用Azure SQL数据库进行响应

Question

我使用的是asp.net内核3.1，ADO。NET、Azure SQL数据库。在这种情况下，对服务层进行api调用->数据库层使用存储过程。

Azure SQL定价规范：

通用：Gen5，8 vCores

代码如下：

public dynamic GetData(FiltersDto reportFilters, long typeid)
{
List<DashboardDTO> result = new List<DashboardDTO>();
DataSet ds = new DataSet();
using (var connection = new SqlConnection(_objDBContext.Database.GetDbConnection().ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[TT].[LoadDashboard]";
command.Parameters.Add(new SqlParameter("@ReviewYear", SqlDbType.BigInt)
{Value = reportFilters.ReviewYear});
command.Parameters.Add(new SqlParameter("@Region", SqlDbType.Structured)
{Value = GetTable<int>(reportFilters.Region)});
command.Parameters.Add(new SqlParameter("@TypeId", typeid));
using (var sda = new SqlDataAdapter())
{
sda.SelectCommand = command;
sda.Fill(ds);
}
}
connection.Close();
}
if (ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
// Logic to read and set the data
DashboardDTO dashboard = new dashboardDTO
{
// Fill the dto with data
}result.Add(dashboard);
}
}
return result;
}

我从asp.net核心web api调用上述方法，并收到以下错误：

{
"header": "An unhandled error occurred.",
"type": "SqlException",
"errorcode": 10002,
"message": "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.",
"innerExcepioin": "The wait operation timed out.",
"stackTrace": "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)rn   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)rn   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)rn   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)rn   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()rn   at System.Data.SqlClient.SqlDataReader.get_MetaData()rn   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)rn   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)rn   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)rn   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)rn   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)rn   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)rn   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)rn   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)rn   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)rn"
}

我还发现，平均需要31.37秒才能得到上述错误。

有人能帮助我解决这个问题吗？

Answer 1

您正在达到30s的默认命令超时。

您正在运行的存储过程的执行时间比它长。因此驱动程序放弃执行。

您可以设置自己的命令超时：https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-6.0

command.CommandTimeout = 60; // For 60 seconds for example

您还可以检查存储过程，看看是否可以加快速度。