zhcxkj-erpapi-dotnet-api ERP读库慢sql ，api并发场景，禁止使用临时表避免IO或内存爆炸，整库卡死： (CustomerId int,Items nvarchar(4000))drop table if exists Tb_Items;
CREATE TABLE Tb_Items (Id varchar(50) PRIMARY KEY);
insert into Tb_Items
exec proc_string_split Items,,;-- 第一个UNION ALL
SELECT wp.SKU,
wh.WareHouseName,
wh.WareHouseType,
wp.WareHouseID,
ISNULL(wpaf.ProcessingNum, 0) ProcessingNum,
wp.OrderSourceSKU,
wp.FNSKU ItemCode,
wp.GoodNum,
wp.LockNum,
ISNULL(wp.OnWayNum, 0) TransitNum,
wh.WareHouseCountry,
wh.AutonomousWareHouseID OrderSourceID,
wp.ID WarehouseProductID,
0 OrderListID,
1 AS StockType
FROM Tb_Items skus
INNER JOIN RB_WareHouse_Product wp WITH (NOLOCK)
ON wp.SKU = skus.Id
AND wp.CustomerID = CustomerId
INNER JOIN RB_WareHouse wh WITH (NOLOCK)
ON wh.ID = wp.WareHouseID
AND wh.CustomerID = wp.CustomerID
AND wh.IsActive = 1
AND wh.WareHouseType NOT IN (1, 3, 60)
LEFT JOIN RB_Warehouse_Product_ActiveInfo wpaf WITH (NOLOCK)
ON wpaf.CustomerID = wp.CustomerID
AND wpaf.SKU = wp.SKU
AND wpaf.WarehouseID = wp.WareHouseID

UNION ALL

-- 第二个UNION ALL
SELECT wpd.RelationSku SKU,
wh.WareHouseName,
wh.WareHouseType,
wp.WareHouseID,
0 ProcessingNum,
wpd.RelationSku OrderSourceSKU,
wpd.FNSKU ItemCode,
wp.GoodNum * wpd.ProductQuantity GoodNum,
wp.LockNum * wpd.ProductQuantity LockNum,
0 TransitNum,
wh.WareHouseCountry,
wpd.ErpChannelId OrderSourceID,
wp.ID WarehouseProductID,
0 OrderListID,
wp.StockType
FROM Tb_Items skus
INNER JOIN RB_WareHouse_ProductDetail wpd WITH (NOLOCK)
ON wpd.ProductSKU = skus.Id
AND wpd.CustomerId = CustomerId
INNER JOIN RB_WareHouse_Product wp WITH (NOLOCK)
ON wp.CustomerID = wpd.CustomerId
AND wp.OrderSourceSKU = wpd.ItemCode
AND wp.StockType IN (2, 3)
-- 添加缺失的WareHouseID关联！
AND wp.WareHouseID = wpd.WarehouseId
INNER JOIN RB_WareHouse wh WITH (NOLOCK)
ON wh.ID = wp.WareHouseID -- 改为通过wp关联，而不是wpd
AND wh.CustomerID = wp.CustomerID
AND wh.IsActive = 1

UNION ALL

-- 第三个UNION ALL
SELECT wpd.RelationSku SKU,
wh.WareHouseName,
wh.WareHouseType,
wp.WareHouseID,
0 ProcessingNum,
wpd.RelationSku OrderSourceSKU,
wpd.FNSKU ItemCode,
wpd.ProductQuantity GoodNum,
wp.LockNum,
0 TransitNum,
wh.WareHouseCountry,
wpd.ErpChannelId OrderSourceID,
wp.ID WarehouseProductID,
0 OrderListID,
ISNULL(wp.StockType, 1) AS StockType
FROM Tb_Items skus
INNER JOIN RB_WareHouse_ProductDetail wpd WITH (NOLOCK)
ON wpd.RelationSku = skus.Id
AND wpd.CustomerId = CustomerId
INNER JOIN RB_WareHouse_Product wp WITH (NOLOCK)
ON wp.CustomerID = wpd.CustomerId
AND wp.FNSKU = wpd.ItemCode
AND wp.WareHouseID = wpd.WarehouseId
INNER JOIN RB_WareHouse wh WITH (NOLOCK)
ON wh.ID = wpd.WarehouseId
AND wh.CustomerID = wp.CustomerID
AND wh.IsActive = 1
AND wh.WareHouseType = 60
WHERE wp.GoodNum > 0
AND ISNULL(wp.StockType, 1) = 1

UNION ALL

-- 第四个UNION ALL
SELECT
wp.SKU SKU,
wh.WareHouseName,
wh.WareHouseType,
wp.WareHouseID,
0 ProcessingNum,
wpd.RelationSku OrderSourceSKU,
wpd.FNSKU ItemCode,
wpd.ProductQuantity GoodNum,
wp.LockNum,
0 TransitNum,
wh.WareHouseCountry,
wpd.ErpChannelId OrderSourceID,
wp.ID WarehouseProductID,
0 OrderListID,
ISNULL(wp.StockType, 1) AS StockType
FROM Tb_Items skus
INNER JOIN RB_WareHouse_Product wp WITH (NOLOCK)
ON wp.SKU = skus.Id
AND wp.CustomerID = CustomerId
AND wp.GoodNum > 0
AND ISNULL(wp.StockType, 1) = 1
INNER JOIN RB_WareHouse wh WITH (NOLOCK)
ON wh.ID = wp.WareHouseID
AND wh.CustomerID = wp.CustomerID
AND wh.IsActive = 1
AND wh.WareHouseType = 60
INNER JOIN RB_WareHouse_ProductDetail wpd WITH (NOLOCK)
ON wpd.CustomerId = wp.CustomerID
AND wpd.WarehouseId = wh.ID
AND wpd.ItemCode = wp.FNSKU
WHERE ISNULL(wpd.RelationSku, ) =
UNION ALL
-- 第五个UNION ALL
SELECT CASE
WHEN ISNULL(ol.GroupSKU, ) = THEN
ol.SKU
ELSE
ol.GroupSKU
END SKU,
wh.WareHouseName,
wh.WareHouseType,
wh.ID WareHouseID,
0 ProcessingNum,
ol.SellerSKU OrderSourceSKU,
FNSKU,
0 GoodNum,
0 LockNum,
0 TransitNum,
wh.WareHouseCountry,
oi.OrderSourceID,
0 WarehouseProductID,
ol.ID OrderListID,
0 AS StockType
FROM Tb_Items skus
INNER JOIN RB_OrderList ol WITH (NOLOCK)
ON ol.SKU = skus.Id
AND ol.CustomerID = CustomerId
AND ol.AmazonDeliveryStatus = 0
INNER JOIN RB_OrderInfo oi WITH (NOLOCK)
ON oi.OrderCode = ol.OrderCode
AND oi.CustomerID = ol.CustomerID
AND oi.OrderStatus = 0
AND oi.IsStockOrder = 1
AND oi.OverDay >= 0
AND oi.AmazonShipmentId IS NULL
INNER JOIN RB_WareHouse wh WITH (NOLOCK)
ON wh.ID = oi.ToWareHouseID
AND wh.CustomerID = oi.CustomerID
AND wh.IsActive = 1
AND wh.WareHouseType NOT IN (1, 3)
INNER JOIN RB_Procurement_Order_Package_OrderDetail opd WITH (NOLOCK)
ON opd.OrderListID = ol.ID
AND opd.CustomerID = ol.CustomerID
INNER JOIN RB_Procurement_Order_Package op WITH (NOLOCK)
ON op.ID = opd.PackageID
AND op.CustomerID = opd.CustomerID
AND op.PackageState = 3 ; drop table if exists Tb_Items; 建议业务拆分 颗粒度 分步 、分批 处理