BUG #40364 转FBA备货情况报告导出性能缓慢阻塞导出队列优化 - 技术中心-需求池

重现步骤

[性能问题描述] 导出缓慢阻塞队列

--DECLARE @CustomerID INT = 1
--DECLARE SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@OrderSourceID,',') NVARCHAR(4000) = 21231
--DECLARE @Start_FirstTime DATETIME = '2023-03-01'
--DECLARE @End_FirstTime DATETIME = '2023-04-12'
--DECLARE @Start_WaitTime DATETIME = '2022-03-01'
--DECLARE @End_WaitTime DATETIME = '2023-04-01'
--DECLARE SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@WaitToFbaType,',') DATETIME = 1

IF OBJECT_ID(N'tempdb..#SearchBaseID',N'U') IS NOT NULL
BEGIN
DROP TABLE #SearchBaseID
END
SELECT base.ID,base.CustomerID,base.OrderSourceSKUID,base.ASINorItemID,base.OrderSourceSKU,base.SKU,base.OrderSourceCountry,
lc.WaitTransferFbaTime
INTO #SearchBaseID
FROM RB_Amazon_Product_Base base(NOLOCK) LEFT JOIN RB_Amazon_Product_Base_Local lc(NOLOCK) ON base.ID=lc.ProductBaseID
WHERE base.CustomerID=@CustomerID
AND base.OrderSourceID IN(SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@OrderSourceID,','))
AND 1=1
AND 1=1
AND base.WaitToFbaType IN(SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@WaitToFbaType,','))
AND base.IsDelete=0
AND base.OrderSourceSKUID>0
AND base.IsFBA=0
AND lc.WaitTransferFbaTime>=@Start_WaitTime
AND lc.WaitTransferFbaTime<@End_WaitTime

IF OBJECT_ID(N'tempdb..#ListingAsinNumber',N'U') IS NOT NULL
BEGIN
DROP TABLE #ListingAsinNumber
END
SELECT base.ASINorItemID,base.OrderSourceCountry,COUNT(base.ID) AS Number
INTO #ListingAsinNumber
FROM RB_Amazon_Product_Base base(NOLOCK) INNER JOIN (
SELECT CustomerID,ASINorItemID,OrderSourceCountry
FROM #SearchBaseID
GROUP BY CustomerID,ASINorItemID,OrderSourceCountry
) parent
ON base.CustomerID=parent.CustomerID AND base.ASINorItemID=parent.ASINorItemID AND base.OrderSourceCountry=parent.OrderSourceCountry
WHERE base.IsFBA=1 AND parent.ASINorItemID<>''
GROUP BY base.ASINorItemID,base.OrderSourceCountry

IF OBJECT_ID(N'tempdb..#StockAsinOrderInfo',N'U') IS NOT NULL
BEGIN
DROP TABLE #StockAsinOrderInfo
END
SELECT *
INTO #StockAsinOrderInfo
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime
FROM(
SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK)
ON oi.CustomerID=oss.CustomerID
AND oi.OrderSourceID=oss.OrderSourceID
AND ol.SellerSKU=oss.OrderSourceSKU
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID]
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND ISNULL(ol.SellerSKU,'')=''
UNION ALL
SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN]
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND ISNULL(ol.SellerSKU,'')<>''
) T1
) T
WHERE T.RowIndex=1

IF OBJECT_ID(N'tempdb..#WaitAsinStock',N'U') IS NOT NULL
BEGIN
DROP TABLE #WaitAsinStock
END
SELECT *
INTO #WaitAsinStock
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime
FROM(
SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK)
ON oi.CustomerID=oss.CustomerID
AND oi.OrderSourceID=oss.OrderSourceID
AND ol.SellerSKU=oss.OrderSourceSKU
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND oi.AddTime >= sbi.WaitTransferFbaTime
AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime)
AND ISNULL(ol.SellerSKU,'')=''
UNION ALL
SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND oi.AddTime>= sbi.WaitTransferFbaTime
AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime)
AND ISNULL(ol.SellerSKU,'')<>''
) T1
) T

--sheet1
SELECT pb.OrderSourceSkuOrderTime AS '渠道SKU首单时间',
po.WaitTransferFbaTime AS '待转FBA时间',
os.OrderSourceName AS '来源渠道',
os.OrderSourceCountry AS '市场',
pb.ASINorItemID AS 'ASIN',
CASE WHEN ISNULL(was.OrderCode,'')='' THEN '否' ELSE '是' END AS '待转FBA时间顺延30天是否已备货（同市场同ASIN)',
CASE
WHEN ISNULL(was.OrderCode,'')=''
THEN ''
ELSE (
STUFF((SELECT ','+OrderCode
FROM #WaitAsinStock
WHERE [ASIN]=pb.ASINorItemID AND OrderSourceCountry=pb.OrderSourceCountry AND RowIndex<=5
FOR XML PATH('')),1,1,'')
)
END AS '待转FBA时间顺延30天内备库单号（同市场同ASIN)',
pb.SKU,
pb.OrderSourceSKU AS 'SellSKU',
CASE WHEN pb.IsOffShelf=0 THEN '在线' WHEN pb.IsOffShelf=1 THEN '下架' WHEN pb.IsOffShelf=2 THEN '售完' WHEN pb.IsOffShelf=3 THEN '草稿' WHEN pb.IsOffShelf=4 THEN '刊登中' WHEN pb.IsOffShelf=5 THEN '刊登失败' ELSE '' END AS '在线状态',
CASE WHEN pb.IsActive=1 THEN '活跃' ELSE '呆滞' END '活跃状态',
( SELECT TOP 1 CASE WHEN LEN(cc.ClassNameCn)>0 THEN cc.ClassNameCn+'>>'+bb.ClassNameCn+'>>'+aa.ClassNameCn
WHEN LEN(bb.ClassNameCn) > 0 THEN bb.ClassNameCn + '>>' + aa.ClassNameCn
ELSE aa.ClassNameCn END
FROM RB_Product_Class aa(NOLOCK)
LEFT JOIN RB_Product_Class bb(NOLOCK) ON aa.parentid=bb.id AND aa.parentid>0
LEFT JOIN RB_Product_Class cc(NOLOCK) ON bb.parentid=cc.id AND bb.parentid>0
WHERE aa.ID =p.ClassID
) AS '产品类别',
ISNULL(abt.BrowseNodeName,'') AS '平台类目',
CASE SellerType WHEN 0 THEN '跟卖' WHEN 1 THEN '自主上架' ELSE '' END AS '销售类型',
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN bt.BusinessTeamName
ELSE bt1.BusinessTeamName
END)
ELSE bt2.BusinessTeamName
END AS '销售小组（一级）',
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN ''
ELSE bt.BusinessTeamName
END )
ELSE bt1.BusinessTeamName
END AS '销售小组（二级）',
pb.BusinessUserName AS '销售人员',
pb.AddAdminName AS '上架人员',
pb.PublishTime AS '上架时间',
pb.ProductNameCN AS '产品中文名称',
pb.AvgDailySales AS '估算销量',
pb.todaysaleNum AS '今天销量',
pb.zsaleNum AS '昨天销量',
pb.qsaleNum AS '前天销量',
pb.sqsaleNum AS '上前销量',
pb.WeekSaleNum AS '一周销量',
pb.TwoWeekSaleNum AS '两周销量',
pb.msaleNum AS '30天销量',
pb.ThreeMonthSaleNum AS '90天销量',
(CASE WHEN pb.MonthAvgProfits IS NOT NULL THEN CONVERT(NVARCHAR, pb.MonthAvgProfits * 100)+'%' ELSE '' END) AS '月平均利润率',
CASE p.ProductState
WHEN 0 THEN '正常'
WHEN 1 THEN '起批量'
WHEN 2 THEN '停产'
WHEN 3 THEN '锁定'
WHEN 4 THEN '暂时缺货'
WHEN 5 THEN '清库'
END AS '产品状态',
CASE WHEN p.WithBattery=0 THEN '未选择' WHEN p.WithBattery=1 THEN '普货' WHEN p.WithBattery=2 THEN '纯电池' WHEN p.WithBattery=3 THEN '带内置电池' WHEN p.WithBattery=4 THEN '带外置电池' WHEN p.WithBattery=5 THEN '含纽扣电池' WHEN p.WithBattery=6 THEN '液体' WHEN p.WithBattery=7 THEN '粉末' WHEN p.WithBattery=8 THEN '打火机' WHEN p.WithBattery=9 THEN '刀具_剪刀_仿真枪' WHEN p.WithBattery=10 THEN '带磁物品' WHEN p.WithBattery=11 THEN '其他禁运物品' WHEN p.WithBattery=12 THEN '木质类_竹质类' WHEN p.WithBattery=13 THEN '膏状' WHEN p.WithBattery=14 THEN '易碎' WHEN p.WithBattery=15 THEN '动物羽毛类' WHEN p.WithBattery=16 THEN '大功率电池' WHEN p.WithBattery=17 THEN '电子烟' WHEN p.WithBattery=18 THEN '移动电源' WHEN p.WithBattery=19 THEN '可拆卸电池' WHEN p.WithBattery=20 THEN '厨具' WHEN p.WithBattery=21 THEN '电机_马达' WHEN p.WithBattery=22 THEN '超体积普' WHEN p.WithBattery=23 THEN '超体积非普' WHEN p.WithBattery=24 THEN '手机' WHEN p.WithBattery=25 THEN '平衡车' WHEN p.WithBattery=26 THEN '液体笔类' WHEN p.WithBattery=27 THEN '纺织品' WHEN p.WithBattery=28 THEN '植物种子' WHEN p.WithBattery=29 THEN '深圳普货' WHEN p.WithBattery=30 THEN '成人用品' WHEN p.WithBattery=31 THEN '无' WHEN p.WithBattery=32 THEN '眼镜' WHEN p.WithBattery=33 THEN '口红' WHEN p.WithBattery=34 THEN '手表' WHEN p.WithBattery=35 THEN '香水' WHEN p.WithBattery=36 THEN '纯液体' WHEN p.WithBattery=37 THEN '医疗设备' WHEN p.WithBattery=38 THEN '人造羽毛' WHEN p.WithBattery=39 THEN '内置锂金属电池' WHEN p.WithBattery=40 THEN '配套干电池' WHEN p.WithBattery=41 THEN '厨房刀具' WHEN p.WithBattery=42 THEN '军工刀具' WHEN p.WithBattery=45 THEN '无线充' WHEN p.WithBattery=46 THEN '原木' WHEN p.WithBattery=47 THEN '面具类' WHEN p.WithBattery=48 THEN '金属高密度' WHEN p.WithBattery=49 THEN '防疫用品' WHEN p.WithBattery=50 THEN '酒精' WHEN p.WithBattery=51 THEN '酒精棉' WHEN p.WithBattery=52 THEN '纸巾' ELSE '' END AS '产品物流属性',
(CASE WHEN pb.IsEFN=1 THEN '是' ELSE '否' END ) AS '是否EFN产品',
STUFF(
( SELECT ','+sh.LableName
FROM RB_Amazon_Lable sh(NOLOCK)
WHERE sh.CustomerID=pb.CustomerID AND sh.OrderSourceSKUID=pb.OrderSourceSKUID
FOR XML PATH('')
), 1, 1, '') AS '标签',
CASE pb.WaitToFbaType WHEN 1 THEN '待转FBA' WHEN 2 THEN '不转FBA'WHEN 3 THEN '已转FBA' ELSE '' END AS '是否转FBA',
btf.Reason as '不转FBA原因',
STUFF((
SELECT ','+HolidaysName
FROM (
SELECT sh.CustomerID,ah.OrderSourceSKUID,sh.HolidaysName
FROM RB_SalesSactorHolidays sh(NOLOCK) INNER JOIN RB_Amazon_Holiday ah(NOLOCK)
ON sh.CustomerID=ah.CustomerID AND sh.ID=ah.HolidayID
)t
WHERE CustomerID=pb.CustomerID AND OrderSourceSKUID=pb.OrderSourceSKUID
FOR XML PATH('')
), 1, 1, '') as '节日标识',
CASE WHEN la.ASINorItemID IS NOT NULL THEN '是' ELSE '否' END AS '是否有FBAlisting',
ISNULL(la.Number,0) AS 'FBAlisting数量',
CASE WHEN ISNULL(soi.OrderCode,'')='' THEN '否' ELSE '是' END AS '是否备货',
ISNULL(soi.OrderCode,'') AS '备货单号',
CASE ISNULL(soi.OrderState,-1) WHEN -1 THEN '' WHEN 0 THEN '否' ELSE '是' END AS '备货单号是否已发货'
FROM RB_Amazon_Product_Base pb(NOLOCK)
INNER JOIN #SearchBaseID baseId(NOLOCK) ON baseId.CustomerID=pb.CustomerID AND baseId.ID=pb.ID
LEFT JOIN RB_Amazon_Product_Base_Label pl(NOLOCK) ON pb.ID=pl.ProductBaseID
LEFT JOIN RB_Amazon_Product_Base_Local po(NOLOCK) ON pb.ID=po.ProductBaseID
LEFT JOIN RB_OrderSource os(NOLOCK) ON pb.OrderSourceID=os.ID
LEFT JOIN RB_Product p (NOLOCK) ON p.CustomerID=pb.CustomerID AND p.SKU=pb.SKU
LEFT JOIN RB_Admin ad(NOLOCK) ON ad.ID=os.SaleAdminID
LEFT JOIN RB_Department dp(NOLOCK) ON dp.ID=ad.DepartmentID
LEFT JOIN RB_BusinessTeam bt(NOLOCK) ON bt.Id=pb.BusinessTeam AND bt.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt1(NOLOCK) ON bt1.id=bt.BusinessTeamParentID AND bt1.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt2(NOLOCK) ON bt2.id=bt1.BusinessTeamParentID AND bt2.CustomerID=pb.CustomerID AND bt1.BusinessTeamName <>''
LEFT JOIN RB_Amazon_NotToFbaReason btf(NOLOCK) ON btf.CustomerID=pb.CustomerID AND pb.NotToFbaReasonId=btf.ID
LEFT JOIN #ListingAsinNumber la(NOLOCK) ON la.ASINorItemID=pb.ASINorItemID AND la.OrderSourceCountry=pb.OrderSourceCountry
LEFT JOIN #StockAsinOrderInfo soi(NOLOCK) ON soi.[ASIN]=pb.ASINorItemID AND soi.OrderSourceCountry=pb.OrderSourceCountry
LEFT JOIN #WaitAsinStock was(NOLOCK) ON was.[ASIN]=pb.ASINorItemID AND was.OrderSourceCountry=pb.OrderSourceCountry AND was.RowIndex=1
LEFT JOIN E_Product_Amazon_BrowseTree abt(NOLOCK) ON abt.CustomerID=pb.CustomerID AND abt.SKU=pb.SKU AND abt.IsMain=1 AND abt.IsDelete=1

--Sheet2 销售人员维度统计
SELECT
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN bt.BusinessTeamName
ELSE bt1.BusinessTeamName
END)
ELSE bt2.BusinessTeamName
END AS '销售小组（一级）',
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN ''
ELSE bt.BusinessTeamName
END )
ELSE bt1.BusinessTeamName
END AS '销售小组（二级）',
pb.BusinessUserName AS '销售人员',
COUNT(pb.ID) AS 'listing条数',
SUM(CASE WHEN soi.OrderCode IS NULL THEN 0 ELSE 1 END) AS '已备货listing条数',
CAST(CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),SUM(CASE WHEN soi.OrderCode IS NULL THEN 0 ELSE 1 END))/CONVERT(DECIMAL(18,2),COUNT(pb.ID)) * 100) AS VARCHAR(10))+'%' AS 'FBA备货率',
SUM(CASE WHEN was.OrderCode IS NULL THEN 0 ELSE 1 END) AS '待转时间顺延30天已备货listing数量',
CAST(CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),SUM(CASE WHEN was.OrderCode IS NULL THEN 0 ELSE 1 END))/CONVERT(DECIMAL(18,2),COUNT(pb.ID)) * 100) AS VARCHAR(10))+'%' AS '待转时间顺延30天已备货率'
FROM RB_Amazon_Product_Base pb(NOLOCK)
INNER JOIN #SearchBaseID baseId(NOLOCK) ON baseId.CustomerID=pb.CustomerID AND baseId.ID=pb.ID
LEFT JOIN RB_OrderSource os(NOLOCK) ON pb.OrderSourceID=os.ID
LEFT JOIN RB_Admin ad(NOLOCK) ON ad.ID=os.SaleAdminID
LEFT JOIN RB_Department dp(NOLOCK) ON dp.ID=ad.DepartmentID
LEFT JOIN RB_BusinessTeam bt(NOLOCK) ON bt.Id=pb.BusinessTeam AND bt.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt1(NOLOCK) ON bt1.id=bt.BusinessTeamParentID AND bt1.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt2(NOLOCK) ON bt2.id=bt1.BusinessTeamParentID AND bt2.CustomerID=pb.CustomerID AND bt1.BusinessTeamName <>''
LEFT JOIN #StockAsinOrderInfo soi ON soi.[ASIN]=pb.ASINorItemID AND soi.OrderSourceCountry=pb.OrderSourceCountry
LEFT JOIN #WaitAsinStock was(NOLOCK) ON was.[ASIN]=pb.ASINorItemID AND was.OrderSourceCountry=pb.OrderSourceCountry AND was.RowIndex=1
GROUP BY
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN bt.BusinessTeamName
ELSE bt1.BusinessTeamName
END)
ELSE bt2.BusinessTeamName
END,
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN ''
ELSE bt.BusinessTeamName
END )
ELSE bt1.BusinessTeamName
END,
pb.BusinessUserName

--Sheet3 销售小组维度统计
SELECT
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN bt.BusinessTeamName
ELSE bt1.BusinessTeamName
END)
ELSE bt2.BusinessTeamName
END AS '销售小组（一级）',
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN ''
ELSE bt.BusinessTeamName
END )
ELSE bt1.BusinessTeamName
END AS '销售小组（二级）',
COUNT(pb.ID) AS 'listing条数',
SUM(CASE WHEN soi.OrderCode IS NULL THEN 0 ELSE 1 END) AS '已备货listing条数',
CAST(CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),SUM(CASE WHEN soi.OrderCode IS NULL THEN 0 ELSE 1 END))/CONVERT(DECIMAL(18,2),COUNT(pb.ID)) * 100) AS VARCHAR(10))+'%' AS 'FBA备货率',
SUM(CASE WHEN was.OrderCode IS NULL THEN 0 ELSE 1 END) AS '待转时间顺延30天已备货listing数量',
CAST(CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),SUM(CASE WHEN was.OrderCode IS NULL THEN 0 ELSE 1 END))/CONVERT(DECIMAL(18,2),COUNT(pb.ID)) * 100) AS VARCHAR(10))+'%' AS '待转时间顺延30天已备货率'
FROM RB_Amazon_Product_Base pb(NOLOCK)
INNER JOIN #SearchBaseID baseId(NOLOCK) ON baseId.CustomerID=pb.CustomerID AND baseId.ID=pb.ID
LEFT JOIN RB_OrderSource os(NOLOCK) ON pb.OrderSourceID=os.ID
LEFT JOIN RB_Admin ad(NOLOCK) ON ad.ID=os.SaleAdminID
LEFT JOIN RB_Department dp(NOLOCK) ON dp.ID=ad.DepartmentID
LEFT JOIN RB_BusinessTeam bt(NOLOCK) ON bt.Id=pb.BusinessTeam AND bt.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt1(NOLOCK) ON bt1.id=bt.BusinessTeamParentID AND bt1.CustomerID=pb.CustomerID
LEFT JOIN RB_BusinessTeam bt2(NOLOCK) ON bt2.id=bt1.BusinessTeamParentID AND bt2.CustomerID=pb.CustomerID AND bt1.BusinessTeamName <>''
LEFT JOIN #StockAsinOrderInfo soi ON soi.[ASIN]=pb.ASINorItemID AND soi.OrderSourceCountry=pb.OrderSourceCountry
LEFT JOIN #WaitAsinStock was(NOLOCK) ON was.[ASIN]=pb.ASINorItemID AND was.OrderSourceCountry=pb.OrderSourceCountry AND was.RowIndex=1
GROUP BY
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN bt.BusinessTeamName
ELSE bt1.BusinessTeamName
END)
ELSE bt2.BusinessTeamName
END,
CASE ISNULL(bt2.BusinessTeamName,'')
WHEN ''
THEN (CASE ISNULL(bt1.BusinessTeamName,'')
WHEN '' THEN ''
ELSE bt.BusinessTeamName
END )
ELSE bt1.BusinessTeamName
END

DROP TABLE #SearchBaseID
DROP TABLE #ListingAsinNumber
DROP TABLE #WaitAsinStock
PARAMS:@OrderSourceID = 0,15322,17134,12815,14512,15323,16177,16749,17262,17640,17319,17265,17264,17263,17914,17912,16750,9885,10859,11702,17133,13223,15302,16475,15600,16471,16767,6943,9857,10058,13331,12813,13585,14510,15318,16187,16739,9859,10294,11602,12804,14603,15310,16184,16742,6674,9861,10295,11605,12807,14606,15313,16181,16745,6673,9860,10296,11604,12806,14605,15312,16183,16744,6671,9858,10293,11601,12803,14602,15309,16185,16741,6675,9862,11603,12805,14604,15311,16179,16743,18094,18103,18124,18127,18129,8017,10285,10284,13332,12812,13586,14511,15319,16186,16740,9535,9863,10398,11607,12810,14607,15314,16182,16746,11166,17227,11606,12808,14608,15315,16178,16748,13690,15320,16174,10485,10538,17228,11608,12809,14609,15316,16180,16747,15321,16176,11550,11551,17226,13168,18011,15447,15382,16396,17229,6942,9856,10057,13330,12811,13584,14509,15317,16188,16738,3000 ,@Start_WaitTime = 2023-08-01 ,@End_WaitTime = 2023-08-24 23:59:59 ,@WaitToFbaType = 1,2,3 ,@CustomerId = 1 ,

[分析过程]

监控发现卡在下面语句建议整改
SELECT *
INTO #WaitAsinStock
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime
FROM(
SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK)
ON oi.CustomerID=oss.CustomerID
AND oi.OrderSourceID=oss.OrderSourceID
AND ol.SellerSKU=oss.OrderSourceSKU
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND oi.AddTime >= sbi.WaitTransferFbaTime
AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime)
AND ISNULL(ol.SellerSKU,'')=''
UNION ALL
SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL
WHERE oi.IsStockOrder=1
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND oi.AddTime>= sbi.WaitTransferFbaTime
AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime)
AND ISNULL(ol.SellerSKU,'')<>''
) T1
) T

[期望结果] 在语句中都加入 AND oi.CustomerID=@CustomerId 保证其使用已有索引，2 指定两处类型不统一的地方指定起类型转换

sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID]) 和 sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN])

SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi with(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK)
ON oi.CustomerID=oss.CustomerID
AND oi.OrderSourceID=oss.OrderSourceID
AND ol.SellerSKU=oss.OrderSourceSKU
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID])
WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND ISNULL(ol.SellerSKU,'')=''
UNION ALL
SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime
FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK)
ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode
INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID
INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN])
WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId
AND os.OrderSourceType=1
AND oi.OrderStatus NOT IN(-1,2)
AND ol.ProductNum+ol.ProductNumUpdate>0
AND ISNULL(ol.SellerSKU,'')<>''

历史记录

2023-08-24 17:02:35, 由 袁慧斌 创建。
2023-08-24 17:05:15, 由 袁慧斌 编辑。
修改了 重现步骤，区别为：
004- 监控发现卡在下面语句建议整改SELECT *INTO #WaitAsinStockFROM( SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime FROM( SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime >= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime>= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')<>'' ) T1) T
004+ 监控发现卡在下面语句建议整改SELECT *INTO #WaitAsinStockFROM( SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime FROM( SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime >= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime>= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')<>'' ) T1) T
005- ~~[期望结果] 在语句中都加入AND oi.CustomerID=@CustomerId 保证其使用已有索引，2 指定两处类型不统一的地方指定起类型转换~~
005+ [期望结果] 在语句中都加入AND oi.CustomerID=@CustomerId 保证其使用已有索引，2 指定两处类型不统一的地方指定起类型转换
009- SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi with(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')<>''
009+ SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi with(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')<>''

004- 监控发现卡在下面语句建议整改 SELECT * INTO #WaitAsinStock FROM( SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime FROM( SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime >= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime>= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')<>'' ) T1 ) T
004+ 监控发现卡在下面语句建议整改 SELECT * INTO #WaitAsinStock FROM( SELECT ROW_NUMBER() OVER(PARTITION BY [ASIN],OrderSourceCountry ORDER BY AddTime ASC ) AS RowIndex,OrderCode,[Asin],OrderState,OrderSourceCountry,AddTime FROM( SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = oss.[ASINorItemID] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime >= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = ol.[ASIN] AND sbi.WaitTransferFbaTime IS NOT NULL WHERE oi.IsStockOrder=1 AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND oi.AddTime>= sbi.WaitTransferFbaTime AND oi.AddTime< DATEADD(DAY,30,sbi.WaitTransferFbaTime) AND ISNULL(ol.SellerSKU,'')<>'' ) T1 ) T
005- ~~[期望结果] 在语句中都加入AND oi.CustomerID=@CustomerId 保证其使用已有索引，2 指定两处类型不统一的地方指定起类型转换~~
005+ [期望结果] 在语句中都加入AND oi.CustomerID=@CustomerId 保证其使用已有索引，2 指定两处类型不统一的地方指定起类型转换
009- SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi with(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')<>''
009+ SELECT oi.OrderCode,oss.[ASINorItemID] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi with(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN RB_Product_OrderSourceSKU oss(NOLOCK) ON oi.CustomerID=oss.CustomerID AND oi.OrderSourceID=oss.OrderSourceID AND ol.SellerSKU=oss.OrderSourceSKU INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),oss.[ASINorItemID]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')='' UNION ALL SELECT oi.OrderCode,ol.[ASIN] AS [Asin],oi.OrderState,os.OrderSourceCountry,oi.AddTime FROM RB_OrderInfo oi(NOLOCK) INNER JOIN RB_OrderList ol(NOLOCK) ON ol.CustomerID=oi.CustomerID AND ol.OrderCode=oi.OrderCode INNER JOIN RB_OrderSource os(NOLOCK) ON os.CustomerID=oi.CustomerID AND os.ID=oi.OrderSourceID INNER JOIN #SearchBaseID sbi ON sbi.CustomerID=oi.CustomerID AND sbi.ASINorItemID = Convert(nvarchar(50),ol.[ASIN]) WHERE oi.IsStockOrder=1 AND oi.CustomerID=@CustomerId AND os.OrderSourceType=1 AND oi.OrderStatus NOT IN(-1,2) AND ol.ProductNum+ol.ProductNumUpdate>0 AND ISNULL(ol.SellerSKU,'')<>''
2023-08-24 17:08:13, 由 李乐同 指派给 甘振豪。
修改了 指派给，旧值为 "liletong"，新值为 "ganzhenhao"。
2023-08-31 18:16:09, 由 甘振豪 解决，方案为 性能问题 。
修改了 解决方案，旧值为 ""，新值为 "performance"。
修改了 解决日期，旧值为 ""，新值为 "2023-08-31 18:15:40"。
修改了 指派给 ，旧值为 "ganzhenhao"，新值为 "yuanhuibin"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "ganzhenhao"。

已修改sql
2023-09-01 11:27:41, 由 袁慧斌 关闭。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "yuanhuibin"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "yuanhuibin"。
修改了 关闭日期，旧值为 ""，新值为 "2023-09-01 11:27:41"。
2023-11-17 09:34:36, 由刘超编辑。
修改了 所属模块，旧值为 "1748"，新值为 "1850"。
2023-11-17 09:53:51, 由刘超编辑。
修改了 所属模块，旧值为 "1850"，新值为 "1863"。

基本信息
迭代/需求/任务

所属项目	技术中心-需求池
所属模块	系统运维性能优化
所属计划
所属迭代
Bug类型
严重程度	严重
优先级	P0
Bug状态	已关闭
业务部门
影响功能
开发责任人
测试责任人
解决时效	0
激活次数	0
激活日期
是否确认	是
当前指派	Closed 于 2023-09-01 11:27:41
截止日期	0000-00-00
上线时间
上线时效
操作系统
浏览器
关键词
抄送给

所属迭代
相关需求
相关任务

Bug的一生
其他相关

由谁创建	袁慧斌于 2023-08-24 17:02:34
影响环境	577
由谁解决	甘振豪于 2023-08-31 18:15:40
解决版本
解决方案	性能问题
由谁关闭	袁慧斌于 2023-09-01 11:27:41
最后修改	刘超于 2023-11-17 09:53:48

相关Bug