[步骤]
172.23.43.160
139.9.60.64
hwy-zh-gz-iis-erpweb02
D:\Irobotbox\Log\DBE...9.txt
1782710861
content:
[13:27:37] -------------------------------------------------------------
CODE:N13273759
Error:11_-2_0_-2146232060
SQL:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY LastUpdateTime ASC) AS [PageIndex] ,* FROM (SELECT paoi.ID,
paoi.OrderCode,paoi.AddAdminID,
paoi.AddAdminName,
paoi.AddTime,
paoi.AbnormalRegisterType,
paoi.SKU,
paoi.SupplierID,
paoi.WarehouseID,
paoi.SingleNo,
paoi.SingleDetailID,
paoi.P_Code,
paoi.TrackingNumber,
paoi.PackageWeight,
paoi.AbnormalLink,
paoi.AbnormalType,
paoi.AbnormalReason,
paoi.AbnormalNumber,
paoi.AbnormalDesc,
paoi.AbnormalWarehouseID,
paoi.AbnormalPositionID,
paoi.HandleMethodID,
paoi.ProcedueID,
paoi.ConfirmTime,
paoi.NodeInProcessing,
paoi.OrderStatus,
paoi.OvertimeTag,
paoi.CompleteTime,
paoi.Remark,
paoi.PurchaseAdminID,
paoi.MerchandiserAdminID,
paoi.PutonAdminID,
paoi.IsCompleteNormal,
paoi.SyncToSupplierStatus,
paoi.SupplierReplyHandleMethodID,
paoi.SupplierReplyRemark,
p.ProductNameCN,
p.ImageUrl AS ProductImageUrl,
wp.PositionCode,
paar.OverStandardValue,
ps.SupplierName,
paoi.AbnormalReasonID,
paoi.NodeInProcessing AS NodeID,
ps.Phone AS Telephone,
paoi.ProcessAbnormalPerson,
paoi.LastUpdateTime,
ISNULL(p.ID,0) AS ProductID,
paoi.AlibaRefundOrderNo,
paoi.AlibaRefundOrderStatus,
p.ProductState,
paoi.RefundAmount,
pl.IsSample,
pl.IsFirstOrder,
pl.IsFirstSupplierOrder,
pl.IsFirstOrderWithinYear,
isnull(paoi.IsOptimizeProjectType1,0) IsOptimizeProjectType1,
isnull(paoi.IsOptimizeProjectType2,0) IsOptimizeProjectType2,
psi.ClaimAmount,
paoi.IsReminder,
ps.SupplierSource,
paoi.DaySaleNum,
paoi.MonthSaleNum
FROM RB_ProcurementAbnormal_OrderInfo paoi WITH ( NOLOCK ) LEFT JOIN RB_Product(NOLOCK) p
ON p.CustomerID = paoi.CustomerID AND p.SKU = paoi.SKU
LEFT JOIN RB_WareHouse_Position(NOLOCK) wp
ON wp.CustomerID = paoi.CustomerID AND wp.ID = paoi.AbnormalPositionID
LEFT JOIN RB_Procurement_Detail_SingleList pds(NOLOCK)
ON paoi.SingleNo = pds.Single_NO AND paoi.CustomerID = pds.CustomerID
LEFT JOIN RB_Procurement_Shipping_Insurance psi(NOLOCK)
ON psi.Foreignkey = pds.ID
LEFT JOIN RB_Procurement_List pl (NOLOCK)
ON paoi.P_Code = pl.P_Code AND paoi.CustomerID = pl.CustomerID
LEFT JOIN RB_ProcurementAbnormal_AbnormalReasonSetting paar(NOLOCK)
ON paar.ID = paoi.AbnormalReasonID AND paar.CustomerID=paoi.CustomerID
LEFT JOIN RB_Product_Supplier(NOLOCK) ps
ON ps.CustomerID = paoi.CustomerID AND ps.ID = paoi.SupplierID WHERE paoi.CustomerID=1 AND paoi.OrderCode IN (select distinct OrderCode from RB_ProcurementAbnormal_OrderInfo_RelationNode with(nolock) where Id=paoi.NodeInProcessing AND NodeStatus in (select cast(item as int) from CharSplitForIn(@NodeStatusList2,','))) AND paoi.AbnormalReasonID in (select cast(item as int) from CharSplitForIn(@AbnormalReasonList,',')) AND paoi.OrderStatus in (select cast(item as int) from CharSplitForIn(@OrderStatusList,',')) AND paoi.HandleMethodID in (select cast(item as int) from CharSplitForIn(@HandleMethodIDList,',')) AND paoi.OrderCode IN (select distinct OrderCode from RB_ProcurementAbnormal_OrderInfo_RelationNode with(nolock) where Id = paoi.NodeInProcessing and PendingDepartmentId in (select cast(item as int) from CharSplitForIn(@DepartmentIdList,','))) AND paoi.WarehouseID IN(select cast(item as int) from CharSplitForIn(@WarehouseIDList,','))) AS TempTable) AS PagerTable
WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
PARAMS:@NodeStatusList2 = 2 ,@AbnormalReasonList = 10,12,15,23,24,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,74,75,76,77,78,79,80,81,82,83,93,94,20,50,51,56,61,62,70,91,92 ,@OrderStatusList = 2 ,@HandleMethodIDList = 2,4,6,12,28,36,41,42,43,44,45,46,51,52,53 ,@DepartmentIdList = 518,104,129,588,63,67,517,60,521,588,518 ,@WarehouseIDList = 5312 ,
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.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
在 RB.DALExtendV3.RB_ProcurementAbnormal_OrderInfoExtendV3.GetProcedurePager(CM_IncomingGoodsWarehouseParam parameters, Int32& recordCount)
在 PandaBuy.ERP.Web.IrobotBox.ProcurementAbnormal.IncomingGoodsWarehouseList.Page_Load(Object sender, EventArgs e)
在 System.Web.UI.Control.OnLoad(EventArgs e)
在 System.Web.UI.Control.LoadRecursive()
在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest()
在 System.Web.UI.Page.ProcessRequest(HttpContext context)
在 ASP.irobotbox_procurementabnormal_incominggoodswarehouselist_aspx.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)
[结果]
[期望]
看看从业务角度去拆分SQL
该sql查询较为好性能的表是RB_ProcurementAbnormal_OrderInfo_RelationNode
该表作为条件被in了两次，将两个条件合并且改为inner join的形式后速度由20秒以上变为5秒以内，
但此更改需要更改的地方比较多，耗时较长，需要下个迭代出需求进行处理