1. 问题描述分析
从5.16--5.18 日的阿里云 erp web 日志里捞出的，排名第4多的超时sql,1 天500次超时
慢的原因是用到了in 子查询
2.解决方案
将In 子查询改有临时表后inner join 查询，
3.超时sql
SELECT elb.CustomerID,
elb.OrderSourceID,
elb.ListingBasicID,
elb.SiteCode,
elb.UpdateStatus,
elb.ListingBasicID AS ID,
elb.ItemID
FROM RB_Ebay_Listing_Basic_V2 elb (nolock)
LEFT JOIN RB_Ebay_Listing_Shipping_V2 els (nolock)
ON elb.CustomerID = els.CustomerID
AND elb.OrderSourceID = els.OrderSourceID
AND elb.ListingBasicID = els.ListingBasicID
AND elb.SiteCode = els.SiteCode WHERE elb.ListingType = 8 AND elb.OnlineState = 1 AND els.SiteCountry = 'CN'
AND elb.CustomerID = @CustomerID AND elb.OrderSourceID = @orderSourceID
AND elb.ListingBasicID in(SELECT CONVERT(INT, item) FROM Charsplitforin(@ListingBasicIDs, ','))
PARAMS:@CustomerID = 1 ,@orderSourceID = 10235 ,@ListingBasicIDs = 68218081,68491192,70007304,72722908,72775695,73455294,73528184,73912775,75452282,75475655,75506764,75529122,76065625,76081797,76128069,76529236,77033051,77831699,78226479,78828247,78943473,78964530,78964668,79045781,79056123,79091732,80298735,80895893,85928087,87028817,87080380,87680491,87865995,87993806,88101974,88451654,88545914,88557626,88933784,88933900,88985103,88994642,89006024,89012729,89015268,89022093,89271165,89808319,89979435,90259979,90585362,90587665,90590273,91983888
参考解决方案 优化
排名第4多的超时sql,1 天500次超时，尽快改造