从华为慢sql里捞出来的，看似是程伟的，若不对请转个相关的人或我
1.问题描述，分析
count（） 查询，查询了大量字段，导致查询慢，每天出现多次
2.解决方案
去掉不必要的查询字段，测试查询从20秒到优化后的1秒查询出结果
优化后的sq;
SELECT COUNT(1) FROM ( SELECT a.P_Code
--a.ID SingleListID
--a.Single_NO ,
--a.Memo,
--b.Id SingleListDetailId ,
--b.DetailID ProcurementDetailID ,
--d.SupplyCode ,
--b.SKU ,
--c.ClientSKU,
--c.ImageUrl ,
--c.ProductName ,
--c.ProductNameCN ,
--a.AddTime OrderTime ,
--b.ActualBuyNum ,
--e.ShipmentQty ,
--b.ActualProductPrice Price,
--b.EnquirySkuStates,
--wh.WareHouseName
FROM RB_Procurement_Detail_SingleList a ( NOLOCK )
INNER JOIN RB_Procurement_Detail_SingleList_Detail b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND a.ID = b.ListID
INNER JOIN dbo.RB_Product c ( NOLOCK ) ON a.CustomerID = c.CustomerID
AND c.SKU = b.SKU
LEFT JOIN RB_Procurement_List pl(NOLOCK) ON a.P_Code=pl.P_Code AND a.CustomerID=pl.CustomerID
LEFT JOIN RB_WareHouse wh(NOLOCK) ON pl.WareHouseID=wh.ID AND pl.CustomerID=wh.CustomerID
LEFT JOIN dbo.RB_Supplier_Product_Profile d ON a.CustomerID = d.CustomerId
AND d.SupplierId = a.SupplierID
AND b.SKU = d.ProductSku
LEFT JOIN ( SELECT ss.CustomerId ,
ss.SupplierId ,
ss.SingleListID ,
ss.SKU ,
SUM(ss.ShipmentQty) ShipmentQty
FROM dbo.RB_Supplier_Shipment ss ( NOLOCK )
GROUP BY ss.CustomerId ,
ss.SupplierId ,
ss.SingleListID,
ss.SKU
) e ON e.CustomerId = a.CustomerID
AND a.SupplierID = e.SupplierId
AND e.SingleListID = b.ListID
AND e.SKU = b.SKU
WHERE ISNULL(a.IsDelete, 0) = 0
AND c.DeleteStatus = 0 AND ISNULL(b.EnquirySkuStates,0)=0
AND a.CustomerID = 1
AND a.SupplierID = 16339 AND a.StepState =9 and a.AddTime >= '2018-01-01 00:00:00' and b.ActualBuyNum != ISNULL(e.ShipmentQty,0) ) AS PagerTable
--',N'@StartTime nvarchar(50)',@StartTime=N'2018-01-01 00:00:00'
3.原始sql:
exec sp_executesql N'SELECT COUNT(1) FROM ( SELECT a.P_Code ,
a.ID SingleListID ,
a.Single_NO ,
a.Memo,
b.Id SingleListDetailId ,
b.DetailID ProcurementDetailID ,
d.SupplyCode ,
b.SKU ,
c.ClientSKU,
c.ImageUrl ,
c.ProductName ,
c.ProductNameCN ,
a.AddTime OrderTime ,
b.ActualBuyNum ,
e.ShipmentQty ,
b.ActualProductPrice Price,
b.EnquirySkuStates,
wh.WareHouseName
FROM RB_Procurement_Detail_SingleList a ( NOLOCK )
INNER JOIN RB_Procurement_Detail_SingleList_Detail b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND a.ID = b.ListID
INNER JOIN dbo.RB_Product c ( NOLOCK ) ON a.CustomerID = c.CustomerID
AND c.SKU = b.SKU
LEFT JOIN RB_Procurement_List pl(NOLOCK) ON a.P_Code=pl.P_Code AND a.CustomerID=pl.CustomerID
LEFT JOIN RB_WareHouse wh(NOLOCK) ON pl.WareHouseID=wh.ID AND pl.CustomerID=wh.CustomerID
LEFT JOIN dbo.RB_Supplier_Product_Profile d ON a.CustomerID = d.CustomerId
AND d.SupplierId = a.SupplierID
AND b.SKU = d.ProductSku
LEFT JOIN ( SELECT ss.CustomerId ,
ss.SupplierId ,
ss.SingleListID ,
ss.SKU ,
SUM(ss.ShipmentQty) ShipmentQty
FROM dbo.RB_Supplier_Shipment ss ( NOLOCK )
GROUP BY ss.CustomerId ,
ss.SupplierId ,
ss.SingleListID,
ss.SKU
) e ON e.CustomerId = a.CustomerID
AND a.SupplierID = e.SupplierId
AND e.SingleListID = b.ListID
AND e.SKU = b.SKU
WHERE ISNULL(a.IsDelete, 0) = 0
AND c.DeleteStatus = 0 AND ISNULL(b.EnquirySkuStates,0)=0
AND a.CustomerID = 1
AND a.SupplierID = 16339 AND a.StepState =9 and a.AddTime >= @StartTime and b.ActualBuyNum != ISNULL(e.ShipmentQty,0) ) AS PagerTable',N'@StartTime nvarchar(50)',@StartTime=N'2018-01-01 00:00:00'
