[性能问题描述]导出50分钟都无法完成，阻塞后面系统
select ROW_NUMBER() over(order by a.SKU) '编号', a.ProductGroupSKU '母体ID', a.SKU, a.OrderSourceSKU '渠道SKU', a.ClientSKU '自定义SKU', a.ASINorItemID 'ASIN', a.SalesNum '销售数量', a.WareHouseName '仓库名称', a.GoodNum '库存数量', convert(varchar(4), a.SalesYear) + '年' + convert(varchar(2), a.SalesMonth) + '月' '时间', a.OrderNum '订单数', a.OrderSourceName '订单来源', a.ProductName '产品名称', a.ProductNameCN '产品中文名' from( select ROW_NUMBER() over(PARTITION by a.CustomerID,a.OrderSourceID,a.SKU,a.OrderSourceSKU,a.WareHouseID, a.SalesYear, a.SalesMonth order by a.CustomerID,a.OrderSourceID,a.SKU,a.OrderSourceSKU,a.WareHouseID, a.SalesYear, a.SalesMonth) SortNum, a.ProductGroupSKU, a.SKU, a.OrderSourceSKU, a.ClientSKU, b.ASINorItemID, a.SalesNum, a.WareHouseName, a.GoodNum, a.SalesYear, a.SalesMonth, a.OrderNum, a.OrderSourceName, a.ProductName, a.ProductNameCN from dbo.View_RB_Report_OrderSourceSKUMonth a WITH(NOLOCK) LEFT JOIN dbo.RB_Amazon_Product_Base(NOLOCK) b ON b.CustomerID = a.CustomerID AND b.OrderSourceID = a.OrderSourceID AND b.SKU = a.SKU AND b.OrderSourceSKU = a.OrderSourceSKU AND b.IsOffShelf IN(0, 1, 2) AND b.IsDelete=0 AND b.ASINorItemID != '' where (DATEDIFF(MONTH,CAST(a.SalesYear AS NVARCHAR(4)) + '-' + CAST(a.SalesMonth AS NVARCHAR(2))+'-1','2021-4-1')<=0) AND (DATEDIFF(MONTH,CAST(a.SalesYear AS NVARCHAR(4)) + '-' + CAST(a.SalesMonth AS NVARCHAR(2))+'-1','2022-12-1')>=0) AND (a.OrderSourceID IN(9948)) AND (a.CustomerID=1) AND (a.SalesNum>0) AND (a.OrderSourceID IN (SELECT OrderSourceID FROM RB_OrderSource_SaleAdmin (NOLOCK) WHERE AdminID=5841)) ) a where SortNum = 1
[分析过程]
SQL 执行较慢，无缺失索引，看看能否添加某些业务关键性字段提升效率
[期望结果]提升导出速度，控制在20分钟以内
该导出为老版本渠道产品销量月报告，可使用新版，需评估老版报告是否继续维护。
原因：导出查询使用View_RB_Report_OrderSourceSKUMonth试图，该视图数量2000W+,试图关联表数据量大，难以命中索引。
优化建议：
1、现在用户导出时间，该报错用户操作导出一年多数据。
2、导出查询中试图替换成关联表。