任务描述

1。 问题分析描述

      从阿里云日志 超时日志里捞出来的，   用到了SKU IN  子查询且元素比较多，查询比较蔓

       


2.解决方案

   将SKU IN 改造有临时表或表值参数后join 这种方式查询，速度会快很多，之前改造过很多case. 

优化前： select
select docId from tab1 where word in (select word from tab1 where docId=123) group by docId limit 1000;
优化后：
select docId from (select word from tab1 where docId=123) as t2 join tab1 t on t.word=t2.word 


3.超时SQL

SELECT CustomerID,
                                        SKU,
                                        ClientSKU,
                                        PackWeight,
                                        GrossWeight,
                                        LastSupplierPrice,
                                        LastBuyPrice,
                                        UnitShipFee,
                                        IsRawMaterial,
                                        LastBuyShipFee,
                                        DeleteStatus
                                FROM   RB_Product (nolock)
                                WHERE  CustomerID = @CustomerID
                                        AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' ) 
PARAMS:@CustomerID = 1 ,
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。 

历史记录  
  1. 2022-05-26 15:12:28, 由 陈少军 创建。
  2. 2022-05-26 15:12:48, 由 陈少军 指派给 何建宝。
    修改了 预计剩余，旧值为 "0"，新值为 "2"。
    参考解决方案优化
  3. 2022-07-08 09:49:57, 由 夏宇 移动，之前为 "#626 系统管理部 - 系统服务优化 "。
  4. 2022-07-25 10:26:36, 由 夏宇 编辑。
    修改了 所属迭代，旧值为 "637"，新值为 "626"。
    修改了 任务描述，区别为：
    009- SELECT CustomerID, SKU, ClientSKU, PackWeight, GrossWeight, LastSupplierPrice, LastBuyPrice, UnitShipFee, IsRawMaterial, LastBuyShipFee, DeleteStatus FROM RB_Product (nolock) WHERE CustomerID = @CustomerID AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )PARAMS:@CustomerID = 1 ,MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
    009+ SELECT CustomerID, SKU, ClientSKU, PackWeight, GrossWeight, LastSupplierPrice, LastBuyPrice, UnitShipFee, IsRawMaterial, LastBuyShipFee, DeleteStatus FROM RB_Product (nolock) WHERE CustomerID = @CustomerID AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )PARAMS:@CustomerID = 1 ,MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
    009- <p>SELECT CustomerID,<br /> SKU,<br /> ClientSKU,<br /> PackWeight,<br /> GrossWeight,<br /> LastSupplierPrice,<br /> LastBuyPrice,<br /> UnitShipFee,<br /> IsRawMaterial,<br /> LastBuyShipFee,<br /> DeleteStatus<br /> FROM RB_Product (nolock)<br /> WHERE CustomerID = @CustomerID<br /> AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )<br />PARAMS:@CustomerID = 1 ,<br />MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。<br /></p>
    009+ <p>SELECT CustomerID,<br /> SKU,<br /> ClientSKU,<br /> PackWeight,<br /> GrossWeight,<br /> LastSupplierPrice,<br /> LastBuyPrice,<br /> UnitShipFee,<br /> IsRawMaterial,<br /> LastBuyShipFee,<br /> DeleteStatus<br /> FROM RB_Product (nolock)<br /> WHERE CustomerID = @CustomerID<br /> AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )<br />PARAMS:@CustomerID = 1 ,<br />MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。</p>
  5. 2022-10-21 10:24:23, 由 陈少军 编辑。
    修改了 开发组 ，旧值为 ""，新值为 "1"。
    修改了 任务描述，区别为：
    007-
    007+ 优化前： selectselect docId from tab1 where word in (select word from tab1 where docId=123) group by docId limit 1000;优化后：select docId from (select word from tab1 where docId=123) as t2 join tab1 t on t.word=t2.word
    008- 3.超时SQL
    008+
    009- SELECT CustomerID, SKU, ClientSKU, PackWeight, GrossWeight, LastSupplierPrice, LastBuyPrice, UnitShipFee, IsRawMaterial, LastBuyShipFee, DeleteStatus FROM RB_Product (nolock) WHERE CustomerID = @CustomerID AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )PARAMS:@CustomerID = 1 ,MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
    009+ 3.超时SQL
    010+ SELECT CustomerID, SKU, ClientSKU, PackWeight, GrossWeight, LastSupplierPrice, LastBuyPrice, UnitShipFee, IsRawMaterial, LastBuyShipFee, DeleteStatus FROM RB_Product (nolock) WHERE CustomerID = @CustomerID AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )PARAMS:@CustomerID = 1 ,MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
    007- <p><br /></p>
    007+ <p><span><span style="color:#333333;"><span style="color:#333333;">优化前： select</span><br /><span style="color:#333333;">select docId from tab1 where word in (select word from tab1 where docId=123) group by docId limit 1000;</span><br /><span style="color:#333333;">优化后：</span><br /><span style="color:#333333;">select docId from (select word from tab1 where docId=123) as t2 join tab1 t on t.word=t2.word </span></span><br /></span></p>
    008- <p>3.超时SQL</p>
    008+ <p><br /></p>
    009- <p>SELECT CustomerID,<br /> SKU,<br /> ClientSKU,<br /> PackWeight,<br /> GrossWeight,<br /> LastSupplierPrice,<br /> LastBuyPrice,<br /> UnitShipFee,<br /> IsRawMaterial,<br /> LastBuyShipFee,<br /> DeleteStatus<br /> FROM RB_Product (nolock)<br /> WHERE CustomerID = @CustomerID<br /> AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )<br />PARAMS:@CustomerID = 1 ,<br />MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。</p>
    009+ <p>3.超时SQL</p>
    010+ <p>SELECT CustomerID,<br /> SKU,<br /> ClientSKU,<br /> PackWeight,<br /> GrossWeight,<br /> LastSupplierPrice,<br /> LastBuyPrice,<br /> UnitShipFee,<br /> IsRawMaterial,<br /> LastBuyShipFee,<br /> DeleteStatus<br /> FROM RB_Product (nolock)<br /> WHERE CustomerID = @CustomerID<br /> AND SKU IN ( '4270566','4304464','4238624','4330153','4344246','4365989','4366050','4361479','4353294','4353356','4367342','4367539','4305854','4304527','4359313','4375552','4329370','4324693','4300280','4291153','4285796','4283513','4281534','4271222','4270363','4239797','4303225','4358115','4262070','4262110','4262122','4312899','4387059','4363965','4372656','4284353','4250811','4381014','4272640','4267928','4255819','4352406','4282373','4352262','4383722','4382682','4325342','4235236','4303334','4253728','4298056','4268500','4302645','4234486','4274424','4262280','4235205','4370190','4377021','4382813','4232495','4381342','4359011','4354365','4361389','4257499','4309328','4330024','4324052','4346621','4301901','4298591','4333849','4360702','4383658','4336595','4364599','4361987','4361864','4361594','4383009','4373457','4377597','4350068','4369399','4369333','4234494','4304375','4269940','4232695','4302046','4280459','4269765','4235430','4293853','4248381' )<br />PARAMS:@CustomerID = 1 ,<br />MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。</p>
  6. 2022-10-26 11:03:37, 由 何建宝 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "2"。
    修改了 指派给 ，旧值为 "hejianbao"，新值为 "chenshaojun"。
    修改了 实际完成，旧值为 ""，新值为 "2022-10-26 11:02:58"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-10-26 11:03:37"。
    修改了 预计剩余，旧值为 "2"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "hejianbao"。

    已将SKU参数先存入临时表，再使用in查询

  7. 2022-10-26 11:03:50, 由 何建宝 编辑。
    修改了 开发组，旧值为 "1"，新值为 "6"。
  8. 2023-10-31 14:53:12, 由 周杰 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 14:53:12"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2023-10-31 14:53:12
任务类型 后端
任务标签 初始
开发组 敏捷开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给
由谁创建 陈少军 于 2022-05-26 15:12:28
由谁完成 何建宝 于 2022-10-26 11:02:58
由谁取消 暂无
由谁关闭 周杰 于 2023-10-31 14:53:12
关闭原因 已完成
最后编辑 周杰 于 2023-10-31 14:53:12
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 2工时
预计剩余 0工时
预计开始 2022-05-26
实际开始 2022-10-26
截止日期 2022-06-02