深圳智汇创想科技有限公司

1. 问题描述分析

  从5.16--5.18 日的阿里云 erp web 日志里捞出的，排名第3多的超时sql

  慢的原因是用到了in 子查询

2.解决方案

 将In 子查询改有临时表后inner join 查询， 

drop table #temp02
create table #temp02(
id int primary key
)

insert into #temp02 SELECT CONVERT(INT, item)
                              FROM   Charsplitforin('6692638,20190501,32067060,49810169,54048314,64289035,69772186,70729952,70778085,70782549,70946567,71149507,71460565,71737714,72786890,72898583,72900408,72969325,73100771,73805348,74119883,74119975,74280276,74355667,74361820,74365314,74371656,74580495,74606973,77221084,77222986,83653617,83688048,83767304,84447097,84493176,84620592,85129100,85987186,86058252,86058264,86780359,86808170,87126102,87248302,87393377,88682192,88973694,89511341,90144096,92579979,92941716', ',')

SELECT CustomerID,
       OrderSourceID,
       SKU,
       OrderSourceSKU,
       ListingBasicID,
       SiteCode,
       ListingProductID,
       IsVariation,
       ParentListingProductID
FROM   RB_Ebay_Listing_Product_V2 (NOLOCK)
inner join #temp02 on RB_Ebay_Listing_Product_V2.ListingBasicID=#temp02.id
WHERE  CustomerID = 1
       AND OrderSourceID = 6898
       --AND ListingBasicID IN (SELECT CONVERT(INT, item)
       --                      FROM   Charsplitforin('6692638,20190501,32067060,49810169,54048314,64289035,69772186,70729952,70778085,70782549,70946567,71149507,71460565,71737714,72786890,72898583,72900408,72969325,73100771,73805348,74119883,74119975,74280276,74355667,74361820,74365314,74371656,74580495,74606973,77221084,77222986,83653617,83688048,83767304,84447097,84493176,84620592,85129100,85987186,86058252,86058264,86780359,86808170,87126102,87248302,87393377,88682192,88973694,89511341,90144096,92579979,92941716', ','))

3.超时sql

SQL:SELECT CustomerID,
OrderSourceID,
SKU,
OrderSourceSKU,
ListingBasicID,
SiteCode,
ListingProductID,
IsVariation,
ParentListingProductID
FROM RB_Ebay_Listing_Product_V2 (NOLOCK)
WHERE CustomerID = @CustomerID
AND OrderSourceID = @OrderSourceID
AND ListingBasicID IN (SELECT CONVERT(INT, item) FROM Charsplitforin(@ListingBasicIDs, ','))
PARAMS:@CustomerID = 1 ,@orderSourceID = 9660 ,@ListingBasicIDs = 33038420,33038619,36295496,36295548,38261153,72803020,73569763,73746286,74118383,74325594,77577566,77631555,77714918,77853200,77854855,77855474,78637516,79328756,79464522,79496686,79920386,80596572,80761669,80945672,82478841,82819837,83270569,83274136,83326458,83792368,84973573,85986991,86322603,86486128,86518495,86590751,88350303,88601104,91254020,91668410,92090068 , MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。