content:[14:41:53] -------------------------------------------------------------
CODE:N14415321
Error:11_-2_0_-2146232060
SQL:DECLARE TmpSKUFromES TABLE (SKU VARCHAR(50));
INSERT INTO TmpSKUFromES (SKU) VALUES (13542805 );
DROP TABLE IF EXISTS tempCustomRoleNewSKU;
create table tempCustomRoleNewSKU
(
SKU varchar(50) primary key
);
insert into tempCustomRoleNewSKU select distinct SKU from (
SELECT a.SKU FROM
dbo.RB_PlatformAuth_Sku (nolock) a
WHERE a.CustomerId=1 AND a.DeptFullPath in({0}_{73}_{85}_{550},{0}_{73}_{85}_{767})
group by a.SKU
UNION ALL
SELECT a.SKU FROM
E_Product_Amazon_BrowseTree (nolock) a
JOIN dbo.RB_PlatformAuth_Class b WITH(NOLOCK) ON a.BrowsePathByID LIKE b.BrowsePathByID+%
WHERE a.CustomerId=1 AND a.IsDelete=0 AND a.IsMain=1 AND b.DeptFullPath in({0}_{73}_{85}_{550},{0}_{73}_{85}_{767})
group by a.SKU
UNION ALL
SELECT a.SKU FROM
RB_Product_Label (nolock) a
JOIN dbo.RB_PlatformAuth_LabelInfo (nolock) b ON a.LabelID=b.ID
JOIN dbo.RB_PlatformAuth_LabelRight (nolock) d ON d.LabelID =b.ID OR d.LabelID=b.ParentID
WHERE a.CustomerId=1 AND a.IsDeleted=0 AND d.DeptFullPath in({0}_{73}_{85}_{550},{0}_{73}_{85}_{767})
) t where 1=1 ;
DROP TABLE IF Exists tempskuinfo; create table tempskuinfo
(
SKU varchar(50),
ID int,
index idx_sku(SKU),
index idx_ID(ID)
)
insert into tempskuinfo(SKU,ID) SELECT P.SKU,P.ID FROM dbo.RB_Product (NOLOCK) p LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse(NOLOCK) WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.SKU IN (Select item from CharSplitForIn(13542805,?)) AND Exists(select 1 from tempCustomRoleNewSKU where p.SKU = tempCustomRoleNewSKU.SKU) CREATE TABLE ProductSKUS(SKU VARCHAR(50)); CREATE INDEX idx_sku_productskus on ProductSKUS(SKU); INSERT INTO ProductSKUS
SELECT SKU FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM tempskuinfo AS TempTable
) AS PagerTable
WHERE PagerTable.[PageIndex] BETWEEN 1 AND 25;
SELECT
ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM (SELECT p.ID ,
p.CustomerID ,
p.ClientSKU ,
p.SKU ,
p.ProductState ,
p.MinSaleNum ,
p.ImageUrl ,
p.ProductProperty ,
p.WithBattery ,
p.PlugRelate ,
p.PlugRelateType ,
p.ProductGroupSKU ,
p.ProductName ,
p.ProductNameCN ,
p.LastSupplierPrice ,
p.GrossWeight ,
p.UnitShipFee ,
p.LastBuyShipFee ,
p.SalePrice ,
p.PackWeight ,
p.ProductMaterial,
p.IsGroup ,
p.OnlineStatus ,
p.ComeSource AS ComeSource ,
p.HasLawFirmAgent,
p.IsCompliantProduct,
ISNULL(whp.GoodNum, 0) GoodNum ,
ISNULL(whp.AvgDailySales, 0) AvgDailySales ,
ISNULL(wh.WareHouseName,) DefaultLocalWarehouseName,
pa.HasImitationCountry,
pa.RecommendValue ,
pa.AddAdminID ,
pa.AddAdminName ,
pa.AddTime ,
pa.DevelopmentAuditOperatorName ,
pa.ProductDevelopmentAuditTime ,
pa.EditAuditOperatorName ,
pa.ProductEditAuditTime ,
pa.PictureAuditOperatorName ,
pa.ProductPictureAuditTime ,
pa.ProductPicAndTextAuditUserName ,
pa.ProductPicAndTextAuditTime ,
pa.IsAmazonSale ,
pa.NoSalePlatform ,
pa.NoSaleCountry ,
pa.DevelopStatus ,
pa.DevelopAdminID ,
pa.DevelopAdminName ,
pa.DevelopTime ,
pa.DevelopRefuseReason ,
pa.DevelopRefuseTime ,
pa.AssignDevelopAdminID ,
pa.AssignDevelopAdminName ,
pa.AssignDevelopTime ,
pa.EditStatus ,
pa.EditAdminID ,
pa.EditAdminName ,
pa.EditTime ,
pa.EditRefuseTime ,
pa.ToImageMemo ,
pa.ImageStatus ,
pa.ImageAdminID ,
pa.ImageAdminName ,
pa.ImageTime ,
pa.ImageRefuseTime ,
pa.BuyerID ,
pa.BuyerName ,
pa.BuyerSetTime ,
pa.CheckStatus ,
pa.CheckReason ,
pa.CheckAdminID ,
pa.CheckAdminName ,
pa.CheckTime ,
pa.OnlineTime ,
pa.OfflineTime ,
pa.OfflineAdminID ,
pa.OfflineAdminName ,
p.TaxPoint ,
p.TaxRate ,
p.IsRawMaterial ,
ISNULL(ProductBadNum, 0) ProductBadNum ,
ISNULL(NumError, 0) NumError ,
p.ProductType ,
ISNULL(p.IsFBABarCode, 0) IsFBABarCode ,
ISNULL(p.IsRecommendOcean, 0) AS IsRecommendOcean ,
pd.DeclarationPriceRate ,
p.IsCheckDeclarationCode ,
p.IsCheckTransport ,
p.LastBuyPrice ,
pa.PriceSpecialistOperatTime ,
ISNULL(p.DefaultLocalWarehouse, 0) AS DefaultLocalWareHouse ,
p.DevelopType ,
p.AppointDevelopType ,
p.PictureSource ,
p.PreclassStatus ,
p.PreclassGoodsCode ,
p.ImitatingBrandRemark,
p.ProductStateRemarks,
p.ClassID1,
p.ClassID2,
p.ClassID,
p.BeginTime,
p.EndTime,
p.ProdutcArrivalDate,
p.AutoChooseKeyWords,
p.IsCompliantRemark,
p.AccurateKeyword,
ISNULL(pa.PhotographerId,0) PhotographerId,
ISNULL(pa.PhotographerName,) PhotographerName,
pa.PhotographerTime,
pa.WrapDesignerId,
pa.WrapDesignerName,
pa.WrapDesignerTime,
pa.ImageDesignerId,
pa.ImageDesignerName,
pa.ImageDesignerTime,
pa.IntellectualPropertyRightsAdminID,
pa.IntellectualPropertyRightsAdminName,
pa.IntellectualPropertyRightsTime,
pa.RiskControlEnquiryAdminId,
pa.RiskControlEnquiryAdminName,
pa.RiskControlEnquiryTime,
ISNULL(p.EditNum,0) EditNum,
p.IsNeedCeCertification,
p.TypeOfTorts,
pa.RiskLevelType,
pa.RiskLevelRemark,
pa.IsRiskAllPlatformSites,
isnull(pa.MainCategoryCheckStatus,0) PreCheckStatus,
p.IsAdultsOnly,
pa.AsinAuditStatus,
pa.AsinAuditAdminName,
pa.AsinAuditTime,
pa.KeyWordAuditStatus,
pa.KeyWordAuditAdminName,
pa.KeyWordAuditTime,
pa.IsAmazonUsWhiteList,
pa.HasPattern,
pa.FirstPicAndTextAuditAdminId,
pa.FirstPicAndTextAuditUserName,
pa.FirstPicAndTextAuditTime,
pa.FirstEditAuditOperatorId,
pa.FirstEditAuditOperatorName,
pa.FirstEditAuditTime,
p.SimilarCode
FROM (SELECT * FROM dbo.RB_Product (NOLOCK) WHERE SKU IN (SELECT * FROM ProductSKUS(NOLOCK))) p
LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU
AND pa.CustomerID = p.CustomerID
LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU
AND whp.CustomerID = p.CustomerID
AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse(NOLOCK) WHERE whp.WareHouseID =RB_WareHouse.ID
AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1)
LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1
LEFT JOIN ( SELECT CustomerID ,
SKU ,
SUM(BadNum) AS ProductBadNum
FROM RB_Procurement_Detail_BadList WITH ( NOLOCK )
WHERE BadNum > 0
AND DType = 1
GROUP BY CustomerID ,
SKU
) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU
AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID
LEFT JOIN ( SELECT CustomerID ,
SKU ,
SUM(NumError) AS NumError
FROM RB_ProductManageLog WITH ( NOLOCK )
WHERE NumError > 0
GROUP BY CustomerID ,
SKU
) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU
AND p.CustomerID = RB_ProductManageLog.CustomerID
LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID
AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.SKU IN (Select item from CharSplitForIn(13542805,?)) AND Exists(select 1 from tempCustomRoleNewSKU where p.SKU = tempCustomRoleNewSKU.SKU) ) AS TempTable;
SELECT COUNT(1) FROM tempskuinfo AS TempCount;
DROP TABLE IF Exists ProductSKUS;
IF OBJECT_ID(Tempdb..tempCustomRoleNewSKU) IS NOT NULL BEGIN DROP TABLE tempCustomRoleNewSKU; END;
DROP TABLE IF Exists tempskuinfo
PARAMS:
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, TaskCompletionSource1 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.DALExtend.RB_ProductExtend.QueryTemporaryTablePagerNew(String sqlDeclare, String sqlRecycle, String SQLString, String strOrder, Int32 pageIndex, Int32 pageSize, Int32& recordCount, Int32 times, SqlParameter[] parameters)
在 RB.DALExtend.RB_ProductExtend.GetModelListV2(CM_GetProductListParam param, Boolean isOpenNewSearch, Int32& recordCount)
在 PandaBuy.ERP.Web.IrobotBox.Product.ProductListV2.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_product_productlistv2_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)
-------------------------------------------------------------
【根因分析】
1. SQL超长(6822字符)，多表LEFT JOIN嵌套
2. CASE p.DefaultLocalWarehouse在JOIN条件中使用，阻止索引查找
3. EXISTS子查询嵌套在LEFT JOIN条件中
4. 多个LEFT JOIN到大表(RB_WareHouse_Product 1044万行)
【优化建议】
1. 将CASE表达式提取为变量预先计算，避免在JOIN条件中使用CASE
2. EXISTS子查询改为INNER JOIN提升效率
3. 确认RB_WareHouse_Product的(CustomerID,SKU,WareHouseID)索引被命中
4. 考虑分批处理SKU减少单次数据量