[步骤]
(@CustomerID int,@SetPurchaseType int,@WhePurchaseType int,@OrderSourceType int,@WareHouseID int)UPDATE RB_OrderList SET PurchaseType = @SetPurchaseType, P_Code_Time = Getdate() FROM RB_OrderList l WITH(nolock) INNER JOIN RB_OrderInfo o WITH(nolock) ON l.OrderCode = o.OrderCode AND l.CustomerID = o.CustomerID INNER JOIN RB_OrderSource s WITH(nolock) ON o.OrderSourceID = s.ID AND o.CustomerID = s.CustomerID WHERE l.PurchaseType = @WhePurchaseType AND l.CustomerID = @CustomerID AND s.OrderSourceType <> @OrderSourceType AND o.WareHouseID = @WareHouseID
[结果]
[期望]
提升性能 减少阻塞
找到了一个之前的 例子
CODE:N1007071
Error:21_596_1_-2146232060
SQL:UPDATE RB_OrderList
SET PurchaseType = @SetPurchaseType,
P_Code_Time = Getdate()
FROM RB_OrderList l WITH(nolock)
INNER JOIN RB_OrderInfo o WITH(nolock)
ON l.OrderCode = o.OrderCode
AND l.CustomerID = o.CustomerID
INNER JOIN RB_OrderSource s WITH(nolock)
ON o.OrderSourceID = s.ID
AND o.CustomerID = s.CustomerID WHERE l.PurchaseType = @WhePurchaseType and l.CustomerID=@CustomerID and (s.OrderSourceType=@OrderSourceType or IsASingleInTheEnd = 1)
AND (o.OrderStatus In(0,4) and o.WareHouseID=@WareHouseID) AND l.AddTime >=@AddTime
PARAMS:@CustomerID = 1 ,@SetPurchaseType = 1 ,@WhePurchaseType = 0 ,@OrderSourceType = 20 ,@WareHouseID = 333 ,@AddTime = 2024/7/11 0:00:00 ,
MESSAGE:Cannot continue the execution because the session is in the kill state.
当前命令发生了严重错误。应放弃任何可能产生的结果。
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.DALExtend.RB_OrderListExtend.UpdateProcurementListPurchaseTypeByIsStock(CM_UpdateProcurementListPurchaseTypeParam param)
在 Biz.Irobotbox.Procurement.ProcurementBuildBKService.ProcurementBuildBK(Int32 customerID, String configProcurementGuid, String procurementGuid, Int32 adminID, String adminName, Int32 warehouseID, String skuAndnum, String messageInfo, String batchId)
在 PandaBuy.ERP.Web.ASHX.IrobotBox.RB_ProcurementHandler.ProcessRequest(HttpContext context)
在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)