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

1. 问题描述，分析

    阿里云日志 erp web  超时sql 第9名，每天出现100多次，分析发现子查询使用了In（）元素很多，导致的查询超时

2.解决方案

将子查询in 改造为临时表或表值参数后join,

3. 超时超时sql

select 
CustomerID,OrderSourceID,ItemID
from 
RB_Ebay_ItemID_ShipStrategy_V2 (nolock)
where CustomerID=@CustomerID
and OrderSourceID=@OrderSourceID
and ItemID in(SELECT ITEM FROM CharSplitForIn(@ItemIDs,','))
PARAMS:@CustomerID = 1 ,@OrderSourceID = 10227 ,@ItemIDs = 134120815974,134120815983,134120815985,134120815992,134120815994,134120815996,134120815993,134120815997,134120815998,134120816001,134120816005,134120816006,134120816008,134120816014,134120816015,134120816016,134120816019,134120816020,134120816021,134120816022,134120816023,134120816024,134120816026,134120816028,134120816030,134120816033,134120816039,134120816047,134120816063,134120816067,134120816069,134120816071,134120816072,134120816074,134120816075,134120816076,134120816086,134120816088,134120816091,134120816092,134120816094,134120816096,134120816097,134120816101,134120816104,134120816107,134120816108,134120816121,134120816124,134120816126,134120816130,134120816132,134120816136,134120816137,134120816139,134120816141,134120816145,134120816147,134120816148,134120816151,134120816152,134120816153,134120816157,134120816158,134120816159,134120816156,134120816166,134120816168,134120816169,134120816170,134120816173,134120816174,134120816175,134120816176,134120816180,134120816182,134120816184,134120816185,134120816186,134120816191,134120816192,134120816195,134120816193,134120816200,134120816201,134120816202,134120816207,134120816208,134120816209,134120816210,134120816212,134120816211,134120816213,134120816218,134120816220,134120816221,134120816224,134120816226,134120816228,134120816233,134120816239,134120816241,134120816251,134120816252,134120816254,134120816255,134120816250,134120816253,134120816258,134120816259,134120816261,134120816262,134120816263,134120816264,134120816265,134120816266,134120816267,134120816270,134120816275,134120816277,134120816281,134120816278,134120816282,134120816286,134120816287,134120816296,134120816299,134120816302,134120816303,134120816309,134120816311,134120816312,134120816314,134120816316,134120816317,134120816318,134120816319,134120816320,134120816323,134120816325,134120816327,134120816331,134120816333,134120816336,134120816337,134120816339,134120816342,134120816344,134120816346,134120816348,134120816349,134120816352,134120816353,134120816354,134120816355,134120816356,134120816357,134120816358,134120816359,134120816360,134120816365,134120816366,134120816368,134120816369,134120816370,134120816373,134120816374,134120816376,134120816375,134120816380,134120816381,134120816383,134120816384,134120816387,134120816388,134120816389,134120816391,134120816395,134120816396,134120816397,134120816401,134120816403,134120816405,134120816410,134120816411,134120816414,134120816416,134120816413,134120816415,134120816419,134120816423,134120816424,134120816429,134120816431,134120816432,134120816435,134120816437,134120816438,134120816439,134120816440 , MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。