[步骤]
[21:27:12] -------------------------------------------------------------
CODE:N2127123
Error:11_-2_0_-2146232060
SQL:DECLARE @BatchSize INT = 1000;
DECLARE @Deleted INT = 1;
WHILE @Deleted > 0
BEGIN
DELETE TOP (@BatchSize) a
FROM [RB_OrderInfo_SBL_Analysis_Monitor_Log] a WITH(NOLOCK)
INNER JOIN dbo.RB_OrderInfo oi WITH(NOLOCK)
ON a.CustomerID = oi.CustomerID AND a.OrderCode = oi.OrderCode
INNER JOIN dbo.RB_WareHouse wh WITH(NOLOCK)
ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID
WHERE a.CustomerID = @CustomerID
AND (wh.WareHouseType <> @WareHouseType OR oi.OrderStatus = @OrderStatus);
SET @Deleted = @@ROWCOUNT;
-- 降低系统负载
WAITFOR DELAY '00:00:00.100';
END
PARAMS:@CustomerID = 1 ,@WareHouseType = 60 ,@OrderStatus = 2 ,
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
在 RB.DAL.DBOperator.ExecuteSql(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.ExecuteSql(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms)
在 RB.DAL.RB_OrderInfo_SBL_Analysis_Monitor.DeleteLogByIsNotZHWMSOrZuoFei(Int32 customerID)
在 Biz.Irobotbox.Order.OrderInfoSBLAnalysisMonitorService.ExecteDelete(Int32 customerID)
在 ChinaDownLoadImage.Task.Order.Task_OrderInfoSBLAnalysisMonitorService.Execute()
在 RB.Utility.SystemTask.<doTask>b__43_0()
在 System.Threading.Tasks.Task.Execute()
在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
在 System.Threading.ThreadPoolWorkQueue.Dispatch()
查询时间长，容易造成阻塞
[结果]
[期望]
先查询返回需要删除的ID 然后在通过ID 删除，已缓减阻塞的时长
