1.问题分析：
count () 查询，去掉不必要的查询字段，函数计算，left join
2.解决方案：
优化后的sql: 去掉不必要的查询字段，函数计算，left join，union all 查询从300+ 秒到 37秒查询出结果
SELECT COUNT(1) FROM (
SELECT
tab.SKU,
tab.AllStockActive
FROM (
SELECT
a.SKU,
AllStockActive=(SELECT TOP 1 ProductVitalityType FROM RB_Product_SalesStatus s WHERE s.SKU=a.SKU AND s.CustomerID=a.CustomerID) --全仓活
FROM View_RB_ProductChina_WareHouse_Table AS a --智能备货分析
WHERE a.CustomerID=1 AND a.SKU!='' AND a.SKU IS NOT null
UNION ALL
SELECT
b.SKU
FROM View_RB_ProductOverseas_WareHouse_Table AS b
WHERE b.CustomerID=1 AND b.SKU!='' AND b.SKU IS NOT null
) AS tab WHERE 1=1 AND AllStockActive=4
) AS PagerTable
--PARAMS:@CustomerID = 1 ,@AllStockActive = 4 ,
3. 原查询sql 超时：
SELECT COUNT(1) FROM (
SELECT
tab.SKU,
tab.ClientSKU,
tab.ProductGroupSKU,
tab.WareHouseID,
tab.ImageUrl,
tab.ProductNameCN,
ClassNameCn=ISNULL(( CASE
WHEN tab.ClassID1 = tab.ClassID
AND tab.ClassID2 = 0 THEN c1.ClassNameCn
WHEN tab.ClassID1 != tab.ClassID2
AND tab.ClassID = tab.ClassID2 THEN c1.ClassNameCn + ' >> ' + c2.ClassNameCn
WHEN tab.ClassID1 = tab.ClassID2
AND tab.ClassID != tab.ClassID2 THEN c1.ClassNameCn + ' >> ' + c3.ClassNameCn
ELSE c1.ClassNameCn + ' >> ' + c2.ClassNameCn + ' >> ' + c3.ClassNameCn
END ), ''), --产品类别
tab.CGNUM,
tab.MidwayProductNum,
tab.ProductNum,
tab.RawMaterialOutNum,
tab.AllGoodNum,
tab.GoodNum,
tab.kcjyNum,
tab.AllStockActive, --全仓活跃度
tab.AvgDailySales,
tab.WeekSaleNum,
tab.TwoWeekSaleNum,
tab.MonthSaleNum,
tab.ThreeMonthSaleNum,
tab.LibraryOfAge,
tab.LastInOperateTime,
tab.LastOutOperateTime,
tab.AmountBalancePrice,
tab.UpdateTime,
tab.ProductProperty,
tab.WareHouseName,
IsBuildSubsidyPlan=ISNULL((
SELECT TOP 1 1 FROM RB_SellerSKU_SubsidyPlan AS sp
INNER JOIN RB_SubsidyPlan_WareHouse AS wh
ON sp.CustomerID=wh.CustomerID AND SubsidyPlanID=sp.ID
WHERE sp.SKU=tab.SKU AND sp.WareHouseID=tab.WareHouseID
),0),
sppo.ProductNum AS SubsidyProductNum
FROM (
SELECT
a.SKU,
a.ClientSKU,
a.ProductGroupSKU,
a.WareHouseID,
a.ImageUrl, --图片地址
a.ProductNameCN, --产品中文名称
a.CGNUM, --需求数
0 as MidwayProductNum, --运输途中数
a.ProductNum, --未配货预出数
a.RawMaterialOutNum,--加工原料需求数
a.AllGoodNum, --当前库存
a.GoodNum, --可用库存
ISNULL(a.kcjyNum,0) AS kcjyNum, --库存结余
a.AvgDailySales, --本仓估算日销量
a.WeekSaleNum, --一周销量
a.TwoWeekSaleNum, --两周销量
a.MonthSaleNum, --30天销量
a.ThreeMonthSaleNum, --90天销量
a.Kl AS LibraryOfAge, --库龄
a.LastInOperateTime, --最后入库时间
a.LastOutOperateTime, --最后出库时间
a.jyPrice AS AmountBalancePrice, --结余金额
a.UpdateTime, --库存更新时间
a.ProductProperty, --产品侵权风险
a.ClassID,
a.ClassID1,
a.ClassID2,
CustomerID,
AllStockActive=(SELECT TOP 1 ProductVitalityType FROM RB_Product_SalesStatus s WHERE s.SKU=a.SKU AND s.CustomerID=a.CustomerID), --全仓活跃度
a.WareHouseName
FROM View_RB_ProductChina_WareHouse_Table AS a --智能备货分析
WHERE a.CustomerID=@CustomerID AND a.SKU!='' AND a.SKU IS NOT null
UNION ALL
SELECT
b.SKU,
b.ClientSKU,
b.ProductGroupSKU,
b.WareHouseID,
b.ImageUrl, --图片地址
b.ProductNameCN, --产品中文名称
b.CGNUM, --需求数
b.MidwayProductNum, --运输途中数
b.ProductNum, --未配货预出数
0 AS RawMaterialOutNum,--加工原料需求数
b.AllGoodNum, --当前库存
b.GoodNum, --可用库存
ISNULL(b.kcjyNum,0) AS kcjyNum, --库存结余
b.AvgDailySales, --本仓估算日销量
b.WeekSaleNum, --一周销量
b.TwoWeekSaleNum, --两周销量
b.MonthSaleNum, --30天销量
b.ThreeMonthSaleNum, --90天销量
b.Kl AS LibraryOfAge, --库龄
b.LastInOperateTime, --最后入库时间
b.LastOutOperateTime, --最后出库时间
b.jyPrice AS AmountBalancePrice, --结余金额
b.UpdateTime, --库存更新时间
b.ProductProperty, --产品侵权风险
b.ClassID,
b.ClassID1,
b.ClassID2,
b.CustomerID,
AllStockActive=(SELECT TOP 1 ProductVitalityType FROM RB_Product_SalesStatus s WHERE s.SKU=b.SKU AND s.CustomerID=b.CustomerID), --全仓活跃度
wh.WareHouseName
FROM View_RB_ProductOverseas_WareHouse_Table AS b
LEFT JOIN RB_WareHouse wh ON b.CustomerID=wh.CustomerID AND b.WareHouseID=wh.ID
WHERE b.CustomerID=@CustomerID AND b.SKU!='' AND b.SKU IS NOT null
) AS tab
LEFT JOIN
(
SELECT SKU,WareHouseID,SUM(ProductNum)ProductNum
FROM RB_SellerSKU_SubsidyPlan_PlaceOrder AS spo
GROUP BY SKU,WareHouseID
)AS sppo ON tab.SKU=sppo.SKU AND sppo.WareHouseID=tab.WareHouseID
LEFT JOIN RB_Product_Class AS c1 WITH(nolock)
ON tab.CustomerID = c1.CustomerID AND tab.ClassID1 = c1.ID
LEFT JOIN RB_Product_Class AS c2 WITH(nolock)
ON tab.CustomerID = c2.CustomerID AND tab.ClassID2 = c2.ID
LEFT JOIN RB_Product_Class AS c3 WITH(nolock)
ON tab.CustomerID = c3.CustomerID AND tab.ClassID = c3.ID
WHERE 1=1
AND AllStockActive=@AllStockActive ) AS PagerTable
PARAMS:@CustomerID = 1 ,@AllStockActive = 4 ,
参考解决方案优化