BUG #56527 性能优化-产品查询超时小黄鸡 - 技术中心-需求池

重现步骤

[步骤]

CODE:N1003206 Error:11_-2_0_-2146232060 SQL:DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50)); INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0 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 ( 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

历史记录

2025-04-10 10:26:47, 由 袁慧斌 创建。
2025-04-10 10:27:10, 由 李乐同 指派给 何建宝。
修改了 指派给，旧值为 "liletong"，新值为 "hejianbao"。
2025-04-10 10:30:01, 由 何建宝 指派给宋宇。
修改了 指派给，旧值为 "hejianbao"，新值为 "songyu"。
2025-04-10 11:28:11, 由 袁慧斌 编辑。
修改了 解决时效，旧值为 "0"，新值为 "-1"。
修改了 重现步骤，区别为：
002- CODE:N1003206 Error:11_-2_0_-2146232060 SQL:DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50)); INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0 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 ( 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 <span styl
002+ CODE:N1003206 Error:11_-2_0_-2146232060 SQL:DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50)); INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0 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 ( 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

002- CODE:N1003206 Error:11_-2_0_-2146232060 SQL:DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50)); INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0 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 ( 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 CODE:N1003206 Error:11_-2_0_-2146232060 SQL:DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50)); INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0 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 ( 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 

DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50));

INSERT INTO @TmpSKUFromES (SKU) VALUES (' ');

IF OBJECT_ID('Tempdb..#tempBrowseTreeSKU') IS NOT NULL BEGIN DROP TABLE #tempBrowseTreeSKU END

SELECT SKU INTO #tempBrowseTreeSKU FROM E_Product_Amazon_BrowseTree(NOLOCK) pabt JOIN CharSplitForIn('16310091,16310161','-') Temp ON pabt.BrowsePathByID LIKE Temp.item+'%' WHERE pabt.IsDelete=0

CREATE NONCLUSTERED INDEX ix_sku ON [dbo].[#tempBrowseTreeSKU] ([SKU])

CREATE TABLE #ProductSKUS(SKU VARCHAR(50)); CREATE INDEX idx_sku_productskus on #ProductSKUS(SKU); INSERT INTO #ProductSKUS
2025-04-10 11:41:36, 由宋宇解决，方案为 性能问题 。
修改了 解决方案，旧值为 ""，新值为 "performance"。
修改了 解决版本，旧值为 ""，新值为 "trunk"。
修改了 解决日期，旧值为 ""，新值为 "2025-04-10 11:41:08"。
修改了 指派给 ，旧值为 "songyu"，新值为 "yuanhuibin"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "songyu"。

已经按照要求加上索引，等待ERP发版
2025-04-10 14:08:51, 由 陈莉珍 编辑。
修改了 开发责任人，旧值为 ""，新值为 "songyu"。
修改了 测试责任人，旧值为 ""，新值为 "yuanhuibin"。
修改了 解决时效 ，旧值为 "0"，新值为 "1.24"。
修改了 上线时间 ，旧值为 "0000-00-00 00:00:00"，新值为 "2025-04-10 11:50"。
修改了 上线时效 ，旧值为 "-1"，新值为 "1.39"。
2025-04-11 16:30:07, 由 袁慧斌 关闭。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "yuanhuibin"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "yuanhuibin"。
修改了 关闭日期，旧值为 ""，新值为 "2025-04-11 16:30:07"。

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

所属项目	技术中心-需求池
所属模块	供应链产品采购产品库
所属计划
所属迭代
Bug类型
严重程度	一般
优先级	P1
Bug状态	已关闭
业务部门	供应链管理中心
影响功能
开发责任人	宋宇
测试责任人	袁慧斌
解决时效	1.24
激活次数	0
激活日期
是否确认	是
当前指派	Closed 于 2025-04-11 16:30:07
截止日期	0000-00-00
上线时间	2025-04-10 11:50:00
上线时效	1.39
操作系统
浏览器
关键词
抄送给

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

Bug的一生
其他相关

由谁创建	袁慧斌于 2025-04-10 10:26:46
影响环境	生产环境-业务反馈生产环境-线上测试
由谁解决	宋宇于 2025-04-10 11:41:08
解决版本	主干
解决方案	性能问题
由谁关闭	袁慧斌于 2025-04-11 16:30:07
最后修改	袁慧斌于 2025-04-11 16:30:07

相关Bug