三千万数据汇总查询，求大神效率优化

Seven_Fox 2014-04-28 09:59:42
SELECT
c_store_id + s.c_sname AS 机构 ,
c_adno AS 部门,
c_ccode AS 商品类别,
c_gcode as 商品编码,
t.c_name as 商品名称,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_number_sale else 0 end) as 当期销售数量,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_number_sale end) as 比较期销售数量,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_sale else 0 end) as 当期销售金额,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_sale end) as 比较期销售金额,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_at_sale else 0 end) as 当期销售成本,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_at_sale end) as 比较期销售成本
FROM(
--当期数据
select
c_store_id,
c_adno,
c_ccode,
c_gcode,
t.c_name,
c_dt,
c_number_sale,
c_sale,
c_at_sale
from tbs_d_gds t(nolock)
where t.c_dt >= @StartDate_Cur
and t.c_dt < @EndDate_Cur
AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%' )
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' ))
UNION ALL
--比较期
select
c_store_id,
c_adno,
c_ccode,
c_gcode,
t.c_name,
c_dt,
c_number_sale,
c_sale,
c_at_sale
from tbs_d_gds t(nolock)
where t.c_dt >= @StartDate_Old
and t.c_dt < @EndDate_Old
AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%')
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' ))
)t
LEFT JOIN tb_store s ON t.c_store_id = s.c_id
GROUP BY c_store_id,c_adno,c_ccode,c_gcode,t.c_name,s.c_sname


以上是对数据的查询语句，《tbs_d_gds》表有三千多万的数据，所用到的查询字段，分组字段均有建立索引，并且该表做过表分区处理。。查询数据的时间段为一个月的数据，数据量大概在三百万上下。。先语句执行的时间是17S，想控制在5秒上下。。求大神指导！！！
...全文
562 35 打赏 收藏 转发到动态 举报
写回复
用AI写文章
35 条回复
切换为时间正序
请发表友善的回复…
发表回复
踏平扶桑 2014-04-29
  • 打赏
  • 举报
 回复
我点了一下那个图片。结果提示我这个信息。。。管理员来看看。
Seven_Fox 2014-04-28
  • 打赏
  • 举报
 回复
引用 9 楼 ap0405140 的回复:
[quote=引用 5 楼 Seven_Fox 的回复:] 取出来的三百万数据，是进行汇总处理的。。前台显示的数据在100多行左右。。
不建议先取出三百万数据后再做汇总,可合并为一步完成.[/quote] 这是一个函数，对客户端传过来的参数进行字符串处理的，因为客户端传过来的条件是多选的，多选的个数不会很多，一般情况下，都是在10个以内。
xdashewan 2014-04-28
  • 打赏
  • 举报
 回复
看你的执行计划，似乎开销都在uf_split_string这表嘛，结合你的sql你这表是做关联模糊查询，这表数据量多大，查询结果大概多少条
唐诗三百首 2014-04-28
  • 打赏
  • 举报
 回复
引用 5 楼 Seven_Fox 的回复:
取出来的三百万数据，是进行汇总处理的。。前台显示的数据在100多行左右。。
不建议先取出三百万数据后再做汇总,可合并为一步完成.
xdashewan 2014-04-28
  • 打赏
  • 举报
 回复
引用 7 楼 Seven_Fox 的回复:
语句是做同比分析的，今年和去年的同比分析。
同比分析，建议去年的数据做预处理，存在在统计表里，然后直接取今年数据进行比较
Seven_Fox 2014-04-28
  • 打赏
  • 举报
 回复
引用 3 楼 ap0405140 的回复:
想控制在5秒上下返回300万的记录应该比较难喔, 请问真有必要在前端程序中显示300万条记录吗?看得过来?
语句是做同比分析的，今年和去年的同比分析。
Seven_Fox 2014-04-28
  • 打赏
  • 举报
 回复



这是语句的执行计划
Seven_Fox 2014-04-28
  • 打赏
  • 举报
 回复
引用 2 楼 dotnetstudio 的回复:
先把当期和比较期的数据取出来，然后再用下面的条件过滤 

AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a  where c_store_id like a.c_str+ '%' )
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a  where t.c_gcode like a.c_str+'%' )
取出来的三百万数据，是进行汇总处理的。。前台显示的数据在100多行左右。。
水族杰纶 2014-04-28
  • 打赏
  • 举报
 回复
引用 3 楼 ap0405140 的回复:
想控制在5秒上下返回300万的记录应该比较难喔, 请问真有必要在前端程序中显示300万条记录吗?看得过来?
另外这些汇总数据是分析历史的还是适时的 如果是分析历史的 应该是提前把这些数据在适当的时间提前生成
唐诗三百首 2014-04-28
  • 打赏
  • 举报
 回复
想控制在5秒上下返回300万的记录应该比较难喔, 请问真有必要在前端程序中显示300万条记录吗?看得过来?
网络科技 2014-04-28
  • 打赏
  • 举报
 回复
软件查询计划等优优，上面不少大侠都说得挺好的，我补充下其它的。 如果软件优化很好了，但效果还是不明显的话，那楼主你只能升级服务器硬件了。 比如，加大内存，升级CPU，加大带宽，机械硬盘改固态盘（这个效果一般比较明显，固盘IO效率确实比机械盘高了不少）
KeepSayingNo 2014-04-28
  • 打赏
  • 举报
 回复
先把当期和比较期的数据取出来，然后再用下面的条件过滤 

AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a  where c_store_id like a.c_str+ '%' )
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a  where t.c_gcode like a.c_str+'%' )
xdashewan 2014-04-28
  • 打赏
  • 举报
 回复
执行计划发一下
aqbeyond 2014-04-28
  • 打赏
  • 举报
 回复
引用 7 楼 Seven_Fox 的回复:
[quote=引用 3 楼 ap0405140 的回复:] 想控制在5秒上下返回300万的记录应该比较难喔, 请问真有必要在前端程序中显示300万条记录吗?看得过来?
语句是做同比分析的，今年和去年的同比分析。[/quote] 今年和去年，那意思是不是就是@StartDate_Cur，@EndDate_Cur和@StartDate_Old，@EndDate_Old这两组日期不会出现交叉？如果是的话，那把语句简化试试： SELECT c_store_id + s.c_sname AS 机构 , c_adno AS 部门, c_ccode AS 商品类别, c_gcode as 商品编码, t.c_name as 商品名称, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_number_sale else 0 end) as 当期销售数量, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_number_sale end) as 比较期销售数量, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_sale else 0 end) as 当期销售金额, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_sale end) as 比较期销售金额, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_at_sale else 0 end) as 当期销售成本, sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_at_sale end) as 比较期销售成本 FROM tbs_d_gds t(nolock) LEFT JOIN tb_store s ON t.c_store_id = s.c_id where ((t.c_dt >= @StartDate_Cur and t.c_dt < @EndDate_Cur) OR (t.c_dt >= @StartDate_Old and t.c_dt < @EndDate_Old) AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%') AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%') and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' )) GROUP BY c_store_id,c_adno,c_ccode,c_gcode,t.c_name,s.c_sname
qqaoshi888 2014-04-28
  • 打赏
  • 举报
 回复 
SELECT c_store_id + s.c_sname  AS 机构,
       c_adno                  AS 部门,
       c_ccode                 AS 商品类别,
       c_gcode                 AS 商品编码,
       t.c_name                AS 商品名称,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_number_sale ELSE 0 END
       )                       AS 当期销售数量,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_number_sale END
       )                       AS 比较期销售数量,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_sale ELSE 0 END
       )                       AS 当期销售金额,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_sale END
       )                       AS 比较期销售金额,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_at_sale ELSE 0 END
       )                       AS 当期销售成本,
       SUM(
           CASE 
                WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
           AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_at_sale END
       )                       AS 比较期销售成本
FROM   (
           --当期数据
           SELECT c_store_id,
                  c_adno,
                  c_ccode,
                  c_gcode,
                  t.c_name,
                  c_dt,
                  c_number_sale,
                  c_sale,
                  c_at_sale
           FROM   tbs_d_gds t(NOLOCK)
           WHERE  t.c_dt >= @StartDate_Cur
                  AND t.c_dt < @EndDate_Cur
                  AND EXISTS (
                          SELECT 1
                          FROM   dbo.uf_split_string(@分类, ',') a
                          WHERE  t.c_ccode LIKE a.c_str + '%'
                      )
                  AND EXISTS (
                          SELECT 1
                          FROM   dbo.uf_split_string(@机构代码, ',') a
                          WHERE  c_store_id LIKE a.c_str + '%'
                      )
                  AND (
                          ISNULL(@商品编码, '') = ''
                          OR EXISTS (
                                 SELECT 1
                                 FROM   dbo.uf_split_string(@商品编码, ',') a
                                 WHERE  t.c_gcode LIKE a.c_str + '%'
                             )
                      )
           UNION ALL
           --比较期
           SELECT c_store_id,
                  c_adno,
                  c_ccode,
                  c_gcode,
                  t.c_name,
                  c_dt,
                  c_number_sale,
                  c_sale,
                  c_at_sale
           FROM   tbs_d_gds t(NOLOCK)
           WHERE  t.c_dt >= @StartDate_Old
                  AND t.c_dt < @EndDate_Old
                  AND EXISTS (
                          SELECT 1
                          FROM   dbo.uf_split_string(@分类, ',') a
                          WHERE  t.c_ccode LIKE a.c_str + '%'
                      )
                  AND EXISTS (
                          SELECT 1
                          FROM   dbo.uf_split_string(@机构代码, ',') a
                          WHERE  c_store_id LIKE a.c_str + '%'
                      )
                  AND (
                          ISNULL(@商品编码, '') = ''
                          OR EXISTS (
                                 SELECT 1
                                 FROM   dbo.uf_split_string(@商品编码, ',') a
                                 WHERE  t.c_gcode LIKE a.c_str + '%'
                             )
                      )
       )t
       LEFT JOIN tb_store s
            ON  t.c_store_id = s.c_id
GROUP BY
       c_store_id,
       c_adno,
       c_ccode,
       c_gcode,
       t.c_name,
       s.c_sname
帮你格式化一下
一片冰心在玉壶 2014-04-28
  • 打赏
  • 举报
 回复
like 直接要命了。。。
發糞塗牆 2014-04-28
  • 打赏
  • 举报
 回复
把你的函数发给我。。。
發糞塗牆 2014-04-28
  • 打赏
  • 举报
 回复
看到邮箱地址没？
xdashewan 2014-04-28
  • 打赏
  • 举报
 回复
他的开销全集中在那两句like联表上
發糞塗牆 2014-04-28
  • 打赏
  • 举报
 回复
加载更多回复（15）
Mysql 安装实战精讲（大神必经之路）
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：1.数据以表格的形式出现2.每行为各种记录名称3.每列为记录名称所对应的数据域4.许多的行和列组成一张表单5.若干的表单组成database
MySQL千万级数据分区存储及查询优化
转载大神经验 原文： https://www.cnblogs.com/javaIOException/p/7524945.html 最近网校加入了题库，2000万条记录，在做模糊查询以及多条件查询时，特别是取随机值时，突破100秒运行时间，修改了Myisam搜索引擎之后，效果显著。 MySQL千万级数据分区存储及查询优化 作为传统的关系型数据库，MySQL因其体积小、速度快、总体拥有成本低受到中小...
【思考】--慢sql治理/千万级数据量优化
目录一、前言二、sql语句优化三、索引优化3.1、建立并利用合理索引3.1.1、前缀索引3.1.2、全文索引3.2、避免索引失效而全表扫描【导致索引失效】四、表优化4.1、分区表----(事先预估就分区场景)4.2、分区表----(数据量暴涨后再考虑分区场景)4.3、同库分表----(事先预告就分表场景)4.4、同库分表----(数据量暴涨后再考虑分表场景)五、大数据平台方案六、具体实例梳理6.1、类似“用户userA点赞内容contentB的记录表”的场景6.2、类似“内容基本信息表”场景6.3、类似“订
100000行级别数据的 Excel 导入优化之路
大家好，我是不才陈某~今天分享一篇大数据量Excel导入如何优化的文章，非常不错。需求说明项目中有一个 Excel 导入的需求：缴费记录导入由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板，应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。在接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求。但是到了 4.0 版本，预估导入时Excel ...
JAVA代码优化，接口优化，SQL优化 （小技巧）
不知道你有没有拼接过字符串，特别是那种有多个参数，字符串比较长的情况。比如现在有个需求：要用get请求调用第三方接口，url后需要拼接多个参数。以前我们的请求地址是这样拼接的： 字符串使用号拼接，非常容易出错。后面优化了一下，改为使用拼接字符串： 代码优化之后，稍微直观点。但还是看起来比较别扭。这时可以使用方法优化： 代码的可读性，一下子提升了很多。我们平常可以使用方法拼接url请求参数，日志打印等字符串。想必大家都使用得比较多，我们经常需要把数据某个文件，或者从某个文件中数据到中，甚至还有可能把文件a，从
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章