任务描述

1. 问题描述，分析

    count 查询里，查询了不必要的字段，导致查询慢


2. 解决方案

去掉不必要的字段查询，提高查询速度，保留 R.CustomerID，其他字段去掉


优化后的sql:


SELECT Count(*)
FROM   (SELECT R.CustomerID
               --O.OrderSourceType,
               --R.OrderSourceID,
               --R.OrderSourceSKU,
               --R.SKU,
               --R.SalesDay,
               --Sum(R.SalesNum) AS TotalSalesNum
        FROM   RB_Report_OrderSourceSKUDay R (nolock)
               INNER JOIN RB_OrderSource O (nolock)
                       ON R.OrderSourceID = O.ID
                          AND R.CustomerID = O.CustomerID
        WHERE  R.CustomerID = 1
               AND R.SalesDay >= '2022-04-24 00:00:00'
               AND R.SalesDay < '2022-05-05 00:00:00'
        GROUP  BY R.CustomerID,
                  O.OrderSourceType,
                  R.OrderSourceID,
                  R.OrderSourceSKU,
                  R.SKU,
                  R.SalesDay) T
--',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'
--558685


3 原始sql:

exec sp_executesql N'SELECT COUNT(*) FROM (
SELECT R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay,SUM(R.SalesNum) as TotalSalesNum
FROM RB_Report_OrderSourceSKUDay R (nolock) 
INNER JOIN RB_OrderSource O (nolock)  ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID
WHERE R.CustomerID=@p1 AND R.SalesDay>=@p2 AND R.SalesDay<@p3
GROUP BY R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay
) T',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'

历史记录  
  1. 2022-05-04 12:36:40, 由 陈少军 创建。
  2. 2022-05-04 12:36:58, 由 陈少军 指派给 程伟。
    修改了 预计剩余，旧值为 "0"，新值为 "2"。
    参考解决方案 修改
  3. 2022-05-04 12:38:32, 由 陈少军 编辑。
    修改了 指派给 ，旧值为 "chengwei"，新值为 "xuruifeng"。
    修改了 任务描述，区别为：
    009- SELECT Count(*)FROM (SELECT R.CustomerID --O.OrderSourceType, --R.OrderSourceID, --R.OrderSourceSKU, --R.SKU, --R.SalesDay, --Sum(R.SalesNum) AS TotalSalesNum FROM RB_Report_OrderSourceSKUDay R (nolock) INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID WHERE R.CustomerID = 1 AND R.SalesDay &gt;= '2022-04-24 00:00:00' AND R.SalesDay &lt; '2022-05-05 00:00:00' GROUP BY R.CustomerID, O.OrderSourceType, R.OrderSourceID, R.OrderSourceSKU, R.SKU, R.SalesDay) T--',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'--558685
    009+ SELECT Count(*)FROM (SELECT R.CustomerID --O.OrderSourceType, --R.OrderSourceID, --R.OrderSourceSKU, --R.SKU, --R.SalesDay, --Sum(R.SalesNum) AS TotalSalesNum FROM RB_Report_OrderSourceSKUDay R (nolock) INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID WHERE R.CustomerID = 1 AND R.SalesDay &gt;= '2022-04-24 00:00:00' AND R.SalesDay &lt; '2022-05-05 00:00:00' GROUP BY R.CustomerID, O.OrderSourceType, R.OrderSourceID, R.OrderSourceSKU, R.SKU, R.SalesDay) T--',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'--558685
    010- 3 原始sql:
    010+
    011- exec sp_executesql N'SELECT COUNT(*) FROM ( SELECT R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay,SUM(R.SalesNum) as TotalSalesNum FROM RB_Report_OrderSourceSKUDay R (nolock) INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID WHERE R.CustomerID=@p1 AND R.SalesDay&gt;=@p2 AND R.SalesDay&lt;@p3 GROUP BY R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay ) T',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'
    011+ 3 原始sql:
    012+ exec sp_executesql N'SELECT COUNT(*) FROM ( SELECT R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay,SUM(R.SalesNum) as TotalSalesNum FROM RB_Report_OrderSourceSKUDay R (nolock) INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID WHERE R.CustomerID=@p1 AND R.SalesDay&gt;=@p2 AND R.SalesDay&lt;@p3 GROUP BY R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay ) T',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'
    009- <p>SELECT Count(*)<br />FROM (SELECT R.CustomerID<br /> --O.OrderSourceType,<br /> --R.OrderSourceID,<br /> --R.OrderSourceSKU,<br /> --R.SKU,<br /> --R.SalesDay,<br /> --Sum(R.SalesNum) AS TotalSalesNum<br /> FROM RB_Report_OrderSourceSKUDay R (nolock)<br /> INNER JOIN RB_OrderSource O (nolock)<br /> ON R.OrderSourceID = O.ID<br /> AND R.CustomerID = O.CustomerID<br /> WHERE R.CustomerID = 1<br /> AND R.SalesDay &gt;= '2022-04-24 00:00:00'<br /> AND R.SalesDay &lt; '2022-05-05 00:00:00'<br /> GROUP BY R.CustomerID,<br /> O.OrderSourceType,<br /> R.OrderSourceID,<br /> R.OrderSourceSKU,<br /> R.SKU,<br /> R.SalesDay) T<br />--',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'<br />--558685<br /></p><div><br /></div>
    009+ <p>SELECT Count(*)<br />FROM (SELECT R.CustomerID<br /> --O.OrderSourceType,<br /> --R.OrderSourceID,<br /> --R.OrderSourceSKU,<br /> --R.SKU,<br /> --R.SalesDay,<br /> --Sum(R.SalesNum) AS TotalSalesNum<br /> FROM RB_Report_OrderSourceSKUDay R (nolock)<br /> INNER JOIN RB_OrderSource O (nolock)<br /> ON R.OrderSourceID = O.ID<br /> AND R.CustomerID = O.CustomerID<br /> WHERE R.CustomerID = 1<br /> AND R.SalesDay &gt;= '2022-04-24 00:00:00'<br /> AND R.SalesDay &lt; '2022-05-05 00:00:00'<br /> GROUP BY R.CustomerID,<br /> O.OrderSourceType,<br /> R.OrderSourceID,<br /> R.OrderSourceSKU,<br /> R.SKU,<br /> R.SalesDay) T<br />--',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'<br />--558685</p>
    010- 3 原始sql:
    010+ <div><br /></div>
    011- <p>exec sp_executesql N'SELECT COUNT(*) FROM (<br /><span> </span>SELECT R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay,SUM(R.SalesNum) as TotalSalesNum<br /><span> </span>FROM RB_Report_OrderSourceSKUDay R (nolock)<br /><span> </span>INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID<br /><span> </span>WHERE R.CustomerID=@p1 AND R.SalesDay&gt;=@p2 AND R.SalesDay&lt;@p3<br /><span> </span>GROUP BY R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay<br /><span> </span>) T',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'<br /></p>
    011+ 3 原始sql:
    012+ <p>exec sp_executesql N'SELECT COUNT(*) FROM (<br /><span> </span>SELECT R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay,SUM(R.SalesNum) as TotalSalesNum<br /><span> </span>FROM RB_Report_OrderSourceSKUDay R (nolock)<br /><span> </span>INNER JOIN RB_OrderSource O (nolock) ON R.OrderSourceID = O.ID AND R.CustomerID = O.CustomerID<br /><span> </span>WHERE R.CustomerID=@p1 AND R.SalesDay&gt;=@p2 AND R.SalesDay&lt;@p3<br /><span> </span>GROUP BY R.CustomerID,O.OrderSourceType,R.OrderSourceID,R.OrderSourceSKU,R.SKU,R.SalesDay<br /><span> </span>) T',N'@p1 bigint,@p2 datetimeoffset(7),@p3 datetimeoffset(7)',@p1=1,@p2='2022-04-24 00:00:00 +08:00',@p3='2022-05-05 00:00:00 +08:00'</p>
    应该是许瑞丰的，不对，找我
  4. 2022-05-04 12:38:59, 由 陈少军 指派给 许瑞丰。
    参考解决方案修改
  5. 2022-05-13 18:49:56, 由 许瑞丰 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "3"。
    修改了 指派给 ，旧值为 "xuruifeng"，新值为 "chenshaojun"。
    修改了 实际完成，旧值为 ""，新值为 "2022-05-13 18:47:55"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-05-13 18:49:56"。
    修改了 预计剩余，旧值为 "2"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "xuruifeng"。

    ERP无法找到此sql语句，是否临时请求或者独立项目，


    目前ERP用到此表的功能是渠道产品销量报表V2

    此功能不存在这个问题。已做过优化

  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-05-04 12:36:40
由谁完成 许瑞丰 于 2022-05-13 18:47:55
由谁取消 暂无
由谁关闭 zhengqiao 于 2022-07-16 17:09:17
关闭原因 已完成
最后编辑 zhengqiao 于 2022-07-16 17:09:17
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 3工时
预计剩余 0工时
预计开始 2022-05-04
实际开始 2022-05-13
截止日期 2022-05-11