1.问题描述
工作时间，RB_Ebay_Listing_Queue_V2 更新 报很多慢sql
2.问题分析
in 查询里只查询queueid, 没有用到left 表里的字段，可以去掉，测试查询的结果是一样的，减少联表的查询，提高速度，1秒出结果
IN (
select top (200) Q.QueueID
FROM RB_Ebay_Listing_Queue_V2 Q WITH (NOLOCK)
INNER JOIN RB_Ebay_Listing_Basic_V2 B WITH (NOLOCK)
ON B.CustomerID = Q.CustomerID
AND B.ListingBasicID = Q.ListingBasicID
AND B.OrderSourceID = Q.OrderSourceID
AND B.SiteCode = Q.SiteCode
LEFT JOIN RB_Ebay_Listing_Product_V2 P WITH (NOLOCK)
ON Q.ListingBasicID = P.ListingBasicID
AND Q.CustomerID = P.CustomerID
AND P.OrderSourceID = Q.OrderSourceID
AND Q.SiteCode = P.SiteCode
AND P.IsVariation = 0
LEFT JOIN RB_OrderSource OS WITH (NOLOCK)
ON OS.ID = Q.OrderSourceID
AND OS.CustomerID = Q.CustomerID
WHERE Q.QueueState = 0
AND Q.QueueType =6
)
改为： 去掉left join
IN (
select top (200) Q.QueueID
FROM RB_Ebay_Listing_Queue_V2 Q WITH (NOLOCK)
INNER JOIN RB_Ebay_Listing_Basic_V2 B WITH (NOLOCK)
ON B.CustomerID = Q.CustomerID
AND B.ListingBasicID = Q.ListingBasicID
AND B.OrderSourceID = Q.OrderSourceID
AND B.SiteCode = Q.SiteCode
--LEFT JOIN RB_Ebay_Listing_Product_V2 P WITH (NOLOCK)
--ON Q.ListingBasicID = P.ListingBasicID
-- AND Q.CustomerID = P.CustomerID
-- AND P.OrderSourceID = Q.OrderSourceID
-- AND Q.SiteCode = P.SiteCode
-- AND P.IsVariation = 0
--LEFT JOIN RB_OrderSource OS WITH (NOLOCK)
--ON OS.ID = Q.OrderSourceID
-- AND OS.CustomerID = Q.CustomerID
WHERE Q.QueueState = 0
AND Q.QueueType =6
)
3.解决方案
去掉left join 查询的代码
4.慢sql
UPDATE QE
SET QE.LockGuid = '3cac6a5ce66446c2bce1b5fb8939485e', QE.QueueState = 1, QE.ExecTime = getdate()
FROM RB_Ebay_Listing_Queue_V2 QE
WHERE QE.QueueID IN (
select top (200) Q.QueueID
FROM RB_Ebay_Listing_Queue_V2 Q WITH (NOLOCK)
INNER JOIN RB_Ebay_Listing_Basic_V2 B WITH (NOLOCK)
ON B.CustomerID = Q.CustomerID
AND B.ListingBasicID = Q.ListingBasicID
AND B.OrderSourceID = Q.OrderSourceID
AND B.SiteCode = Q.SiteCode
LEFT JOIN RB_Ebay_Listing_Product_V2 P WITH (NOLOCK)
ON Q.ListingBasicID = P.ListingBasicID
AND Q.CustomerID = P.CustomerID
AND P.OrderSourceID = Q.OrderSourceID
AND Q.SiteCode = P.SiteCode
AND P.IsVariation = 0
LEFT JOIN RB_OrderSource OS WITH (NOLOCK)
ON OS.ID = Q.OrderSourceID
AND OS.CustomerID = Q.CustomerID
WHERE Q.QueueState = 0
AND Q.QueueType =6
);