[性能问题描述]
[17:30:33] -------------------------------------------------------------
CODE:N1730333
Error:11_-2_0_-2146232060
SQL:SELECT COUNT(1) FROM ( SELECT total.* FROM (
SELECT RB_Product_Supplier.SupplierType,
RB_Product_Supplier.IsFBABarCode,
RB_Procurement_Detail_SingleList.ID AS xdID,
RB_Procurement_Detail_SingleList.Memo,
RB_Procurement_Detail_SingleList.StepRemark,
RB_Procurement_Detail_SingleList.IsNewFlow,
RB_Procurement_Detail_SingleList.StepType,
RB_Procurement_Detail_SingleList.Single_NO AS Single_NO,
RB_Procurement_Detail_SingleList.AddTime AS AddTime,
RB_Procurement_Detail_SingleList.FinishDate AS FinishDate,
RB_Procurement_Detail_SingleList.ShipAmount AS ShipAmount,
RB_Procurement_Detail_SingleList.StepState AS StepState,
RB_Procurement_Detail_SingleList.SingleAmount AS SingleAmount,
RB_Procurement_Detail_SingleList.TrackingNumber,
RB_Procurement_Detail_SingleList.TrackingNumbers,
RB_Procurement_Detail_SingleList.BuyerID,
RB_Procurement_Detail_SingleList.ProcPayType,
RB_Procurement_Detail_SingleList.TransportType,
RB_Procurement_Detail_SingleList.DeliveryTime,
RB_Procurement_Detail_SingleList.PayState,
RB_Procurement_Detail_SingleList.OrderStatus,
RB_Procurement_Detail_SingleList.EnquiryStates,
RB_Procurement_Detail_SingleList.IsAlibabaSingle,
RB_Procurement_Detail_SingleList.IsTaobaoSingle,
RB_Procurement_Detail_SingleList.IsAutoSubmit,
RB_Procurement_Detail_SingleList.AutoSubmitResultType,
RB_Procurement_Detail_SingleList.IsAuto,
RB_Procurement_Detail_SingleList.AutoSubmitResultText,
RB_Procurement_Detail_SingleList.PrepareBuyTime,
RB_Procurement_Detail_SingleList_ForeignCurrency.Currency,
RB_Procurement_Detail_SingleList_ForeignCurrency.ExchangeRMB,
RB_Procurement_Detail_SingleList_ForeignCurrency.SingleAmountForeign,
RB_Procurement_Detail_SingleList_ForeignCurrency.MinusAmountCurrencyForeign,
RB_Procurement_Detail_SingleList_ForeignCurrency.ShipAmountForeign,
RB_Procurement_Detail_SingleList_ForeignCurrency.PayTotalAmountForeign,
RB_Procurement_Detail_SingleList_ForeignCurrency.PaidAmountForeign,
WareHouseName AS WareHouseName,
RB_Procurement_List.WareHouseID,
RB_Procurement_List.ID,
RB_Procurement_List.FinishStatus,
(select top 1 SupplierSource from RB_Product_Supplier(nolock) where ID = RB_Procurement_Detail_SingleList.SupplierID ) AS SupplierSource,
RB_WareHouse.IsTaxFree,
RB_WareHouse.WareHouseAddress,
RB_WareHouse.WareHouseCode,
RB_WareHouse.WareHouseProvince,
RB_WareHouse.WareHouseCity,
RB_WareHouse.WareHousePostCode,
RB_Procurement_List.AOrderCode,
RB_Procurement_List.P_Code AS P_Code,
RB_Procurement_List.BuyerName AS BuyerName,
RB_Procurement_Detail_SingleList.SupplierName,
RB_Procurement_List.DevelopAdminName AS DevelopAdminName,
RB_Procurement_List.IsUrgent,
RB_Procurement_List.IsFactoryDirectDeliveryOrder,
vNba.NobuyAmount,
ROW_NUMBER() OVER (ORDER BY RB_Procurement_Detail_SingleList.ID DESC) AS RowNo,
RB_Procurement_Detail_SingleList.OrderNo,
RB_Procurement_List.IsSample
FROM RB_Procurement_Detail_SingleList WITH (NOLOCK)
LEFT JOIN RB_Procurement_Detail_SingleList_ForeignCurrency WITH (NOLOCK)
ON RB_Procurement_Detail_SingleList_ForeignCurrency.Single_NO = RB_Procurement_Detail_SingleList.Single_NO
AND RB_Procurement_Detail_SingleList_ForeignCurrency.CustomerID = RB_Procurement_Detail_SingleList.CustomerID
LEFT JOIN RB_Procurement_List WITH (NOLOCK)
ON RB_Procurement_List.P_Code = RB_Procurement_Detail_SingleList.P_Code
AND RB_Procurement_List.CustomerID = RB_Procurement_Detail_SingleList.CustomerID
LEFT JOIN RB_Product_Supplier WITH (NOLOCK)
ON RB_Procurement_List.SupplierID = RB_Product_Supplier.ID
AND RB_Procurement_List.CustomerID = RB_Product_Supplier.CustomerID
LEFT JOIN E_Website (NOLOCK)
ON E_Website.CustomerID = RB_Procurement_List.CustomerID
LEFT JOIN RB_WareHouse (NOLOCK)
ON RB_Procurement_List.WareHouseID = RB_WareHouse.ID
AND RB_Procurement_List.CustomerID = RB_WareHouse.CustomerID
outer apply( SELECT COUNT(1) AS NobuyAmount
FROM ( SELECT pdsd.ActualBuyNum AS SingleNum ,
buy.ActualBuyNum ,
pdsd.ActualBuyNum - ISNULL(buy.ActualBuyNum, 0) AS NobuyNum
FROM RB_Procurement_Detail_SingleList_Detail pdsd
WITH ( NOLOCK )
LEFT JOIN RB_Procurement_Detail_SingleList pds WITH(NOLOCK) ON pds.CustomerID = pdsd.CustomerID
AND pds.ID = pdsd.ListID
LEFT JOIN ( SELECT pdb.Single_NO ,
pdb.CustomerID ,
pdbd.SKU ,
pdbd.DetailID ,
SUM(pdbd.ActualBuyNum) AS ActualBuyNum
FROM RB_Procurement_Detail_BuyList_Detail pdbd ( NOLOCK )
LEFT JOIN dbo.RB_Procurement_Detail_BuyList pdb ( NOLOCK ) ON pdb.CustomerID = pdbd.CustomerID
AND pdbd.ListNo = pdb.ListNo
GROUP BY pdb.Single_NO ,
pdb.CustomerID ,
pdbd.SKU ,
pdbd.DetailID
) buy ON buy.CustomerID = pdsd.CustomerID
AND buy.DetailID = pdsd.DetailID
AND buy.SKU = pdsd.SKU
AND buy.Single_NO = pds.Single_NO
WHERE pds.Single_NO = RB_Procurement_Detail_SingleList.Single_NO
) t
WHERE t.NobuyNum > 0
)vNba
WHERE RB_Procurement_List.CustomerID = 1
and RB_Procurement_Detail_SingleList.StepState=9 and EXISTS(select ID from RB_Product_Supplier(nolock) where ID = RB_Procurement_Detail_SingleList.SupplierID and SupplierSource = 0) AND RB_Procurement_Detail_SingleList.BuyerID IN (1013) and RB_Procurement_Detail_SingleList.AddTime >= '2023-08-03 00:00:00'
and RB_Procurement_Detail_SingleList.AddTime <= '2023-11-03 23:59:59' and RB_Procurement_Detail_SingleList.IsNewFlow=1 and RB_Procurement_List.IsSample =1
) total WHERE total.NobuyAmount>0 ) AS PagerTable
PARAMS:
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.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.ExecuteScalar()
在 RB.DAL.DBOperator.GetSingle(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.GetSingle(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
在 RB.DALExtend.RB_Procurement_DetailExtend.QueryPager(String SQLString, String strOrder, Int32 pageIndex, Int32 pageSize, Int32& recordCount, Int32 times, Boolean IsWriteDB, SqlParameter[] cmdParms)
在 RB.DALExtend.RB_Procurement_DetailExtend.QueryPager(String SQLString, String strOrder, Int32 pageIndex, Int32 pageSize, Int32& recordCount, Boolean IsWriteDB)
在 RB.DALExtend.RB_Procurement_DetailExtend.GetPagerListAll(RB_Procurement_Detail_PSParameters parameters, Int32& recordCount, Boolean IsWriteDB)
在 PandaBuy.ERP.Web.IrobotBox.ProcurementV2.Procurement.ProcurementBuyList.TableBind()
在 PandaBuy.ERP.Web.IrobotBox.ProcurementV2.Procurement.ProcurementBuyList.Page_Load(Object sender, EventArgs e)
在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(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.ProcessRequestWithNoAssert(HttpContext context)
在 System.Web.UI.Page.ProcessRequest(HttpContext context)
在 ASP.irobotbox_procurementv2_procurement_procurementbuylist_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.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
-------------------------------------------------------------
[分析过程]
outer apply 模块 黄色部分的关联 只用了 Single_NO 字段，无法应用索引 建议 把 CustomerID 关联也添加上去
[期望结果] 在关联 上 添加 and pds.CustomerID=RB_Procurement_Detail_SingleList.CustomerID 提升效率