任务描述

1.问题描述

  批量更新RB_Amazon_Product_Base多个字段超时优化,没DBError_data.txt文件里每天出现多次


2.问题分析

   批量更新导致超时,


3.解决方案

检查当前的批量更新数量是多少,减少批量更新的数量;


4.批量更新语句,不同于之前的更新一个字段,这个是更新多个字段:

UPDATE [RB_Amazon_Product_Base]
SET    [IsOffShelf] = @IsOffShelf_1,
       [UpdateTime] = @UpdateTime_1,
       [IsVariation] = @IsVariation_1,
       [VariantGroup] = @VariantGroup_1,
       [ImageUrl] = @ImageUrl_1,
       [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_1,
       [TemplateID] = @TemplateID_1,
       [ASINorItemID] = @ASINorItemID_1,
       [BrowsePathByID] = @BrowsePathByID_1,
       [AddAdminID] = @AddAdminID_1,
       [AddAdminName] = @AddAdminName_1,
       [ProductName] = @ProductName_1,
       [ListingPrice] = @ListingPrice_1,
       [PreDiscountPrice] = @PreDiscountPrice_1,
       [DiscountSalesPrice] = @DiscountSalesPrice_1,
       [DiscountStartTime] = @DiscountStartTime_1,
       [DiscountEndTime] = @DiscountEndTime_1,
       [TranslateStatus] = @TranslateStatus_1,
       [OnShelfMode] = @OnShelfMode_1,
       [BrandAddTitle] = @BrandAddTitle_1,
       [ConstSeconds] = @ConstSeconds_1,
       [ShelfRemark] = @ShelfRemark_1
WHERE  [CustomerID] = @CustomerID_1
       AND [OrderSourceSKUID] = @OrderSourceSKUID_1
       AND [OrderSourceID] = @OrderSourceID_1
       AND [OrderSourceSKU] = @OrderSourceSKU_1

UPDATE [RB_Amazon_Product_Base]
SET    [IsOffShelf] = @IsOffShelf_2,
       [UpdateTime] = @UpdateTime_2,
       [IsVariation] = @IsVariation_2,
       [VariantGroup] = @VariantGroup_2,
       [ImageUrl] = @ImageUrl_2,
       [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_2,
       [TemplateID] = @TemplateID_2,
       [ASINorItemID] = @ASINorItemID_2,
       [BrowsePathByID] = @BrowsePathByID_2,
       [AddAdminID] = @AddAdminID_2,
       [AddAdminName] = @AddAdminName_2,
       [ProductName] = @ProductName_2,
       [ListingPrice] = @ListingPrice_2,
       [PreDiscountPrice] = @PreDiscountPrice_2,
       [DiscountSalesPrice] = @DiscountSalesPrice_2,
       [DiscountStartTime] = @DiscountStartTime_2,
       [DiscountEndTime] = @DiscountEndTime_2,
       [TranslateStatus] = @TranslateStatus_2,
       [OnShelfMode] = @OnShelfMode_2,
       [BrandAddTitle] = @BrandAddTitle_2,
       [ConstSeconds] = @ConstSeconds_2,
       [ShelfRemark] = @ShelfRemark_2
WHERE  [CustomerID] = @CustomerID_2
       AND [OrderSourceSKUID] = @OrderSourceSKUID_2
       AND [OrderSourceID] = @OrderSourceID_2
       AND [OrderSourceSKU] = @OrderSourceSKU_2


历史记录  
  1. 2022-04-26 10:00:56, 由 陈少军 创建。
  2. 2022-04-26 11:25:46, 由 陈少军 指派给 罗友军。
    修改了 预计剩余,旧值为 "0",新值为 "2"。

    3.解决方案

    检查当前的批量更新数量是多少,减少批量更新的数量;

  3. 2022-04-26 13:33:41, 由 罗友军 指派给 huangtiekun。
    修改了 指派给,旧值为 "luoyoujun",新值为 "huangtiekun"。
  4. 2022-05-04 11:31:41, 由 huangtiekun 完成。
    修改了 总计消耗,旧值为 "0",新值为 "0.5"。
    修改了 指派给 ,旧值为 "huangtiekun",新值为 "chenshaojun"。
    修改了 实际完成,旧值为 "",新值为 "2022-05-04 11:26:15"。
    修改了 任务状态,旧值为 "wait",新值为 "done"。
    修改了 实际开始,旧值为 "0000-00-00 00:00:00",新值为 "2022-05-04 11:31:41"。
    修改了 预计剩余,旧值为 "2",新值为 "0"。
    修改了 由谁完成,旧值为 "",新值为 "huangtiekun"。

    解决方案:每次更新条数减到原来的1/4;先上上去试下,看效果是否明显


  5. 2022-05-06 14:52:19, 由 陈少军 编辑。
    修改了 任务描述,区别为:
    011- UPDATE [RB_Amazon_Product_Base]SET [IsOffShelf] = @IsOffShelf_1, [UpdateTime] = @UpdateTime_1, [IsVariation] = @IsVariation_1, [VariantGroup] = @VariantGroup_1, [ImageUrl] = @ImageUrl_1, [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_1, [TemplateID] = @TemplateID_1, [ASINorItemID] = @ASINorItemID_1, [BrowsePathByID] = @BrowsePathByID_1, [AddAdminID] = @AddAdminID_1, [AddAdminName] = @AddAdminName_1, [ProductName] = @ProductName_1, [ListingPrice] = @ListingPrice_1, [PreDiscountPrice] = @PreDiscountPrice_1, [DiscountSalesPrice] = @DiscountSalesPrice_1, [DiscountStartTime] = @DiscountStartTime_1, [DiscountEndTime] = @DiscountEndTime_1, [TranslateStatus] = @TranslateStatus_1, [OnShelfMode] = @OnShelfMode_1, [BrandAddTitle] = @BrandAddTitle_1, [ConstSeconds] = @ConstSeconds_1, [ShelfRemark] = @ShelfRemark_1WHERE [CustomerID] = @CustomerID_1 AND [OrderSourceSKUID] = @OrderSourceSKUID_1 AND [OrderSourceID] = @OrderSourceID_1 AND [OrderSourceSKU] = @OrderSourceSKU_1UPDATE [RB_Amazon_Product_Base]SET [IsOffShelf] = @IsOffShelf_2, [UpdateTime] = @UpdateTime_2, [IsVariation] = @IsVariation_2, [VariantGroup] = @VariantGroup_2, [ImageUrl] = @ImageUrl_2, [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_2, [TemplateID] = @TemplateID_2, [ASINorItemID] = @ASINorItemID_2, [BrowsePathByID] = @BrowsePathByID_2, [AddAdminID] = @AddAdminID_2, [AddAdminName] = @AddAdminName_2, [ProductName] = @ProductName_2, [ListingPrice] = @ListingPrice_2, [PreDiscountPrice] = @PreDiscountPrice_2, [DiscountSalesPrice] = @DiscountSalesPrice_2, [DiscountStartTime] = @DiscountStartTime_2, [DiscountEndTime] = @DiscountEndTime_2, [TranslateStatus] = @TranslateStatus_2, [OnShelfMode] = @OnShelfMode_2, [BrandAddTitle] = @BrandAddTitle_2, [ConstSeconds] = @ConstSeconds_2, [ShelfRemark] = @ShelfRemark_2WHERE [CustomerID] = @CustomerID_2 AND [OrderSourceSKUID] = @OrderSourceSKUID_2 AND [OrderSourceID] = @OrderSourceID_2 AND [OrderSourceSKU] = @OrderSourceSKU_2
    011+ UPDATE [RB_Amazon_Product_Base]SET [IsOffShelf] = @IsOffShelf_1, [UpdateTime] = @UpdateTime_1, [IsVariation] = @IsVariation_1, [VariantGroup] = @VariantGroup_1, [ImageUrl] = @ImageUrl_1, [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_1, [TemplateID] = @TemplateID_1, [ASINorItemID] = @ASINorItemID_1, [BrowsePathByID] = @BrowsePathByID_1, [AddAdminID] = @AddAdminID_1, [AddAdminName] = @AddAdminName_1, [ProductName] = @ProductName_1, [ListingPrice] = @ListingPrice_1, [PreDiscountPrice] = @PreDiscountPrice_1, [DiscountSalesPrice] = @DiscountSalesPrice_1, [DiscountStartTime] = @DiscountStartTime_1, [DiscountEndTime] = @DiscountEndTime_1, [TranslateStatus] = @TranslateStatus_1, [OnShelfMode] = @OnShelfMode_1, [BrandAddTitle] = @BrandAddTitle_1, [ConstSeconds] = @ConstSeconds_1, [ShelfRemark] = @ShelfRemark_1WHERE [CustomerID] = @CustomerID_1 AND [OrderSourceSKUID] = @OrderSourceSKUID_1 AND [OrderSourceID] = @OrderSourceID_1 AND [OrderSourceSKU] = @OrderSourceSKU_1UPDATE [RB_Amazon_Product_Base]SET [IsOffShelf] = @IsOffShelf_2, [UpdateTime] = @UpdateTime_2, [IsVariation] = @IsVariation_2, [VariantGroup] = @VariantGroup_2, [ImageUrl] = @ImageUrl_2, [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_2, [TemplateID] = @TemplateID_2, [ASINorItemID] = @ASINorItemID_2, [BrowsePathByID] = @BrowsePathByID_2, [AddAdminID] = @AddAdminID_2, [AddAdminName] = @AddAdminName_2, [ProductName] = @ProductName_2, [ListingPrice] = @ListingPrice_2, [PreDiscountPrice] = @PreDiscountPrice_2, [DiscountSalesPrice] = @DiscountSalesPrice_2, [DiscountStartTime] = @DiscountStartTime_2, [DiscountEndTime] = @DiscountEndTime_2, [TranslateStatus] = @TranslateStatus_2, [OnShelfMode] = @OnShelfMode_2, [BrandAddTitle] = @BrandAddTitle_2, [ConstSeconds] = @ConstSeconds_2, [ShelfRemark] = @ShelfRemark_2WHERE [CustomerID] = @CustomerID_2 AND [OrderSourceSKUID] = @OrderSourceSKUID_2 AND [OrderSourceID] = @OrderSourceID_2 AND [OrderSourceSKU] = @OrderSourceSKU_2
    012+
    011- <p>UPDATE [RB_Amazon_Product_Base]<br />SET [IsOffShelf] = @IsOffShelf_1,<br /> [UpdateTime] = @UpdateTime_1,<br /> [IsVariation] = @IsVariation_1,<br /> [VariantGroup] = @VariantGroup_1,<br /> [ImageUrl] = @ImageUrl_1,<br /> [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_1,<br /> [TemplateID] = @TemplateID_1,<br /> [ASINorItemID] = @ASINorItemID_1,<br /> [BrowsePathByID] = @BrowsePathByID_1,<br /> [AddAdminID] = @AddAdminID_1,<br /> [AddAdminName] = @AddAdminName_1,<br /> [ProductName] = @ProductName_1,<br /> [ListingPrice] = @ListingPrice_1,<br /> [PreDiscountPrice] = @PreDiscountPrice_1,<br /> [DiscountSalesPrice] = @DiscountSalesPrice_1,<br /> [DiscountStartTime] = @DiscountStartTime_1,<br /> [DiscountEndTime] = @DiscountEndTime_1,<br /> [TranslateStatus] = @TranslateStatus_1,<br /> [OnShelfMode] = @OnShelfMode_1,<br /> [BrandAddTitle] = @BrandAddTitle_1,<br /> [ConstSeconds] = @ConstSeconds_1,<br /> [ShelfRemark] = @ShelfRemark_1<br />WHERE [CustomerID] = @CustomerID_1<br /> AND [OrderSourceSKUID] = @OrderSourceSKUID_1<br /> AND [OrderSourceID] = @OrderSourceID_1<br /> AND [OrderSourceSKU] = @OrderSourceSKU_1<br /><br />UPDATE [RB_Amazon_Product_Base]<br />SET [IsOffShelf] = @IsOffShelf_2,<br /> [UpdateTime] = @UpdateTime_2,<br /> [IsVariation] = @IsVariation_2,<br /> [VariantGroup] = @VariantGroup_2,<br /> [ImageUrl] = @ImageUrl_2,<br /> [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_2,<br /> [TemplateID] = @TemplateID_2,<br /> [ASINorItemID] = @ASINorItemID_2,<br /> [BrowsePathByID] = @BrowsePathByID_2,<br /> [AddAdminID] = @AddAdminID_2,<br /> [AddAdminName] = @AddAdminName_2,<br /> [ProductName] = @ProductName_2,<br /> [ListingPrice] = @ListingPrice_2,<br /> [PreDiscountPrice] = @PreDiscountPrice_2,<br /> [DiscountSalesPrice] = @DiscountSalesPrice_2,<br /> [DiscountStartTime] = @DiscountStartTime_2,<br /> [DiscountEndTime] = @DiscountEndTime_2,<br /> [TranslateStatus] = @TranslateStatus_2,<br /> [OnShelfMode] = @OnShelfMode_2,<br /> [BrandAddTitle] = @BrandAddTitle_2,<br /> [ConstSeconds] = @ConstSeconds_2,<br /> [ShelfRemark] = @ShelfRemark_2<br />WHERE [CustomerID] = @CustomerID_2<br /> AND [OrderSourceSKUID] = @OrderSourceSKUID_2<br /> AND [OrderSourceID] = @OrderSourceID_2<br /> AND [OrderSourceSKU] = @OrderSourceSKU_2<br /></p><div><br /></div>
    011+ <p>UPDATE [RB_Amazon_Product_Base]<br />SET [IsOffShelf] = @IsOffShelf_1,<br /> [UpdateTime] = @UpdateTime_1,<br /> [IsVariation] = @IsVariation_1,<br /> [VariantGroup] = @VariantGroup_1,<br /> [ImageUrl] = @ImageUrl_1,<br /> [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_1,<br /> [TemplateID] = @TemplateID_1,<br /> [ASINorItemID] = @ASINorItemID_1,<br /> [BrowsePathByID] = @BrowsePathByID_1,<br /> [AddAdminID] = @AddAdminID_1,<br /> [AddAdminName] = @AddAdminName_1,<br /> [ProductName] = @ProductName_1,<br /> [ListingPrice] = @ListingPrice_1,<br /> [PreDiscountPrice] = @PreDiscountPrice_1,<br /> [DiscountSalesPrice] = @DiscountSalesPrice_1,<br /> [DiscountStartTime] = @DiscountStartTime_1,<br /> [DiscountEndTime] = @DiscountEndTime_1,<br /> [TranslateStatus] = @TranslateStatus_1,<br /> [OnShelfMode] = @OnShelfMode_1,<br /> [BrandAddTitle] = @BrandAddTitle_1,<br /> [ConstSeconds] = @ConstSeconds_1,<br /> [ShelfRemark] = @ShelfRemark_1<br />WHERE [CustomerID] = @CustomerID_1<br /> AND [OrderSourceSKUID] = @OrderSourceSKUID_1<br /> AND [OrderSourceID] = @OrderSourceID_1<br /> AND [OrderSourceSKU] = @OrderSourceSKU_1<br /><br />UPDATE [RB_Amazon_Product_Base]<br />SET [IsOffShelf] = @IsOffShelf_2,<br /> [UpdateTime] = @UpdateTime_2,<br /> [IsVariation] = @IsVariation_2,<br /> [VariantGroup] = @VariantGroup_2,<br /> [ImageUrl] = @ImageUrl_2,<br /> [RecommendedBrowseNodeName] = @RecommendedBrowseNodeName_2,<br /> [TemplateID] = @TemplateID_2,<br /> [ASINorItemID] = @ASINorItemID_2,<br /> [BrowsePathByID] = @BrowsePathByID_2,<br /> [AddAdminID] = @AddAdminID_2,<br /> [AddAdminName] = @AddAdminName_2,<br /> [ProductName] = @ProductName_2,<br /> [ListingPrice] = @ListingPrice_2,<br /> [PreDiscountPrice] = @PreDiscountPrice_2,<br /> [DiscountSalesPrice] = @DiscountSalesPrice_2,<br /> [DiscountStartTime] = @DiscountStartTime_2,<br /> [DiscountEndTime] = @DiscountEndTime_2,<br /> [TranslateStatus] = @TranslateStatus_2,<br /> [OnShelfMode] = @OnShelfMode_2,<br /> [BrandAddTitle] = @BrandAddTitle_2,<br /> [ConstSeconds] = @ConstSeconds_2,<br /> [ShelfRemark] = @ShelfRemark_2<br />WHERE [CustomerID] = @CustomerID_2<br /> AND [OrderSourceSKUID] = @OrderSourceSKUID_2<br /> AND [OrderSourceID] = @OrderSourceID_2<br /> AND [OrderSourceSKU] = @OrderSourceSKU_2</p>
    012+ <div><br /></div>


    5.5日上线,今天查看日志,


    从之前的1天3000多个,变为大半天的 394个(00:---15:00),这394个可能是死锁等操作引起的,后面会处理死锁的问题,等明天在观察统计一下

  6. 2022-07-16 17:09:17, 由 zhengqiao 关闭。
    修改了 任务状态,旧值为 "done",新值为 "closed"。
    修改了 指派给 ,旧值为 "chenshaojun",新值为 "closed"。
    修改了 由谁关闭,旧值为 "",新值为 "zhengqiao"。
    修改了 关闭时间,旧值为 "0000-00-00 00:00:00",新值为 "2022-07-16 17:09:17"。
    修改了 关闭原因,旧值为 "",新值为 "done"。
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2022-07-16 17:09:17
任务类型 后端
任务标签 初始
开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给 刘灿 黄金龙
由谁创建 陈少军 于 2022-04-26 10:00:56
由谁完成 huangtiekun 于 2022-05-04 11:26:15
由谁取消 暂无
由谁关闭 zhengqiao 于 2022-07-16 17:09:17
关闭原因 已完成
最后编辑 zhengqiao 于 2022-07-16 17:09:17
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 0.5工时
预计剩余 0工时
预计开始 2022-04-26
实际开始 2022-05-04
截止日期 2022-05-03