SQL 优化问题，请教各位坛友，谢谢！

wuxi883 2018-10-30 10:57:56
SQL语句如下：查询时间 20秒左右。
WITH
A203 AS (select sum(a)*0.09385 a,sum(b)*0.09385 b,sum(c)*0.09385 c,sum(d)*0.09385 d,sum(e)*0.09385 e,sum(f)*0.09385 f,sum(g)*0.09385 g from JY203_deadcycletime where datediff(DD,dateadd(hour,-8,time),dateadd(hour,-8,getdate()))=0 and h=0 group by NUM),
A206 AS (select sum(a)*0.09385 a,sum(b)*0.09385 b,sum(c)*0.09385 c,sum(d)*0.09385 d,sum(e)*0.09385 e,sum(f)*0.09385 f,sum(g)*0.09385 g from JY206_deadcycletime where datediff(DD,dateadd(hour,-8,time),dateadd(hour,-8,getdate()))=0 and h=0 group by NUM),
A208 AS (select sum(a)*0.09385 a,sum(b)*0.09385 b,sum(c)*0.09385 c,sum(d)*0.09385 d,sum(e)*0.09385 e,sum(f)*0.09385 f,sum(g)*0.09385 g from JY208_deadcycletime where datediff(DD,dateadd(hour,-8,time),dateadd(hour,-8,getdate()))=0 and h=0 group by NUM)

SELECT 'JY203' AS PRESS,avg(a) a,avg(b) b,avg(c) c,avg(d) d,avg(e) e,avg(f) f,avg(g) g
from A203 where a!=0 and b!=0 and c!=0 and d!=0 and e!=0 and f!=0 and g!=0
union
SELECT 'JY206' AS PRESS,avg(a) a,avg(b) b,avg(c) c,avg(d) d,avg(e) e,avg(f) f,avg(g) g
from A206 where a!=0 and b!=0 and c!=0 and d!=0 and e!=0 and f!=0 and g!=0
union
SELECT 'JY208' AS PRESS,avg(a) a,avg(b) b,avg(c) c,avg(d) d,avg(e) e,avg(f) f,avg(g) g
from A208 where a!=0 and b!=0 and c!=0 and d!=0 and e!=0 and f!=0 and g!=0

语句是查询每个表（三个表结构一样）中 当天早8点至明天早8点（非自然天） a~g字段先按NUM分组求和 然后求当天平均值进行union。
每个表都是每100毫秒记录一条数据（早上8点开始没有数据，到第二天早上8点前数据量最大）

表结构 如下


查询计划如下



请问大家还有什么优化方法吗？数据量也不是很大，索引也建立了，20秒也太久了吧。
...全文
88 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-11-01
  • 打赏
  • 举报
 回复 
--创建一个专门的标量函数，用于获取条件时间
IF OBJECT_ID('dbo.Fun_GetWhereTime') IS NOT NULL 
	DROP FUNCTION dbo.Fun_GetWhereTime
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION dbo.Fun_GetWhereTime()
RETURNS DATETIME
AS
BEGIN
	DECLARE @d DATETIME
	SET @d=GETDATE()
	--每天的1~7点，我要查询昨天早8点到现在的数据
	IF DATEPART(hour,@d)<=7
	BEGIN
 		SET @d = CONVERT(CHAR(10),@d-1,120)+' 08:00:00' 
	END
	--每天的8~24点，我要查询今天早8点到现在的数据
	ELSE
	BEGIN
 		SET @d = CONVERT(CHAR(10),@d,120)+' 08:00:00' 
	END
	RETURN @d;
END
GO

--查询时直接取标量函数即可
SELECT * FROM [Time]>dbo.Fun_GetWhereTime()
这样代码就简洁多了
wuxi883 2018-11-01
  • 打赏
  • 举报
 回复
试了一下，确实datediff没用到索引查找，慢很多
之前版主写的这个条件 ，因为[Time]格式问题执行不了
WHERE [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
我改成下面那样，才没有效果：
WHERE convert(char(16),[Time],20)>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND convert(char(16),[Time],20)<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
我现在先用
where time between DATEADD(hour,-24,getdate()) and getdate() 筛选出近一天的数据
然后再用
datediff(DD,dateadd(hour,-8,time),dateadd(hour,-8,getdate()))=0 选取早8点开始到第二天8点的数据
效率确实快了有十倍之多，

业务逻辑问题，需要再研究一下，我现在先用 bwteen先筛选出一部分数据，然后在用datediff精确定位，不知道有没有更好的时间定为方法。
我想查询的时间范围：【非自然天早8点到第二天早8点】
这里指的是 ，每天的循环是早上8点，也可以描述为：
（每天的1~7点，我要查询昨天早8点到现在的数据）
（每天的8~24点，我要查询今天早8点到现在的数据）

总之先谢谢版主了，已经找到问题并且效率也提高很多了。谢谢！
吉普赛的歌 2018-10-30
  • 打赏
  • 举报
 回复 
--加索引
CREATE INDEX ix_JY203_deadcycletime_Time_h ON JY203_deadcycletime([Time],h)
CREATE INDEX ix_JY206_deadcycletime_Time_h ON JY206_deadcycletime([Time],h)
CREATE INDEX ix_JY208_deadcycletime_Time_h ON JY208_deadcycletime([Time],h)
GO

--不能用 DateDiff ， 这是大忌
WITH A203 AS (
         SELECT SUM(a) * 0.09385     a,
                SUM(b) * 0.09385     b,
                SUM(c) * 0.09385     c,
                SUM(d) * 0.09385     d,
                SUM(e) * 0.09385     e,
                SUM(f) * 0.09385     f,
                SUM(g) * 0.09385     g
         FROM   JY203_deadcycletime
         WHERE  [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
                AND h = 0
         GROUP BY
                NUM
     ),
     A206 AS (
         SELECT SUM(a) * 0.09385     a,
                SUM(b) * 0.09385     b,
                SUM(c) * 0.09385     c,
                SUM(d) * 0.09385     d,
                SUM(e) * 0.09385     e,
                SUM(f) * 0.09385     f,
                SUM(g) * 0.09385     g
         FROM   JY206_deadcycletime
         WHERE  [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
                AND h = 0
         GROUP BY
                NUM
     ),
     A208 AS (
         SELECT SUM(a) * 0.09385     a,
                SUM(b) * 0.09385     b,
                SUM(c) * 0.09385     c,
                SUM(d) * 0.09385     d,
                SUM(e) * 0.09385     e,
                SUM(f) * 0.09385     f,
                SUM(g) * 0.09385     g
         FROM   JY208_deadcycletime
         WHERE  [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
                AND h = 0
         GROUP BY
                NUM
     )

SELECT 'JY203'  AS PRESS,
       AVG(a)      a,
       AVG(b)      b,
       AVG(c)      c,
       AVG(d)      d,
       AVG(e)      e,
       AVG(f)      f,
       AVG(g)      g
FROM   A203
WHERE  a != 0
       AND b != 0
       AND c != 0
       AND d != 0
       AND e != 0
       AND f != 0
       AND g != 0

union
SELECT 'JY206'  AS PRESS,
       AVG(a)      a,
       AVG(b)      b,
       AVG(c)      c,
       AVG(d)      d,
       AVG(e)      e,
       AVG(f)      f,
       AVG(g)      g
FROM   A206
WHERE  a != 0
       AND b != 0
       AND c != 0
       AND d != 0
       AND e != 0
       AND f != 0
       AND g != 0

union
SELECT 'JY208'  AS PRESS,
       AVG(a)      a,
       AVG(b)      b,
       AVG(c)      c,
       AVG(d)      d,
       AVG(e)      e,
       AVG(f)      f,
       AVG(g)      g
FROM   A208
WHERE  a != 0
       AND b != 0
       AND c != 0
       AND d != 0
       AND e != 0
       AND f != 0
       AND g != 0
先试下效果
吉普赛的歌 2018-10-30
  • 打赏
  • 举报
 回复
语句是查询每个表（三个表结构一样）中 当天早8点至明天早8点（非自然天） a~g字段先按NUM分组求和 然后求当天平均值进行union。 我只是根据你红色字体部分来写的。 就算按你的， 两者同时减去 8 个小时， 那跟同一天有什么区别？ 业务逻辑的问题就不跟你纠缠了， 总之你应该改成类似我那样的写法。 datediff 根本用不到索引， 你看你的计划全是 聚集索引扫描。 如果 h 只有 0 或 1 ， 为什么不改为 bit 型？ 如果只用得到 h =0， 那就这样创建索引 ： 
CREATE INDEX ix_JY203_deadcycletime_Time_h ON JY203_deadcycletime([Time]) WHERE h=0
CREATE INDEX ix_JY206_deadcycletime_Time_h ON JY206_deadcycletime([Time]) WHERE h=0
CREATE INDEX ix_JY208_deadcycletime_Time_h ON JY208_deadcycletime([Time]) WHERE h=0
wuxi883 2018-10-30
  • 打赏
  • 举报
 回复
谢谢版主，试了，查询时间几乎没变化，time本身设为主键已经有索引了，
忘了说，a~h列都是0或1的值
h列加索引没有效果。

自己优化了一下
因为a~h都是0或1的值，之前用float格式储存，改为tinyint，查询缩短到2秒了

另外请问，WHERE后面用datediff函数会严重拖慢速度吗？
用
WHERE [Time]>=CONVERT(CHAR(10), GETDATE(), 120)+' 08:00' AND [Time]<CONVERT(CHAR(10), GETDATE()+1, 120)+'08:00'
替代
WHERE datediff(DD,dateadd(hour,-8,time),dateadd(hour,-8,getdate()))=0 and h=0
好像不能达到我想要的数据筛选要求，在晚上0点之后，会查询不到数据把？
MySQL版SQL优化
本课程通过Centos版的MySQL讲解了SQL优化的一些常见手段。 其中讲解了MySQL的分层、存储引擎等底层逻辑，并讲解了常见的索引优化手段。在讲解时，先通过理论对先关的优化知识进行了铺垫，然后使用实际的案例详细的...
sql优化的几种方式
一、为什么要对SQL进行优化 我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。 二、SQL优化的一些方法 1.对查询进行优化，应尽量避免全表扫描...
Sql优化总结！详细！（2021最新面试必问）
Sql优化基础Sql优化查询SQL尽量不要使用select *，而是具体字段避免在where子句中使用or来连接条件使用varchar代替char尽量使用数值替代字符串类型查询尽量避免返回大量数据使用explain分析你SQL执行计划是否使用了索引及其扫描类型创建name字段的索引优化like语句：字符串怪现象索引不宜太多，一般5个以内索引不适合建在有大量重复数据的字段上where限定查询的数据避免在索引列上使用内置函数避免在where中对字段进行表达式操作避免在where子句中使用!=或<>操
sql优化的几种方法
在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t wh
sql优化的N种方法_持续更新
当你访问网站的时候,有的时候会慢的想让你砸电脑,这个时候服务器要背锅了吗? 不,要背锅的不仅仅是服务器,数据库也有很大责任,不负责任的sql开发者更会让你崩溃的.为了提高sql响应速度,还是好好了解下sql的优化吧 sql优化的方式 1: explain关键字 :使用explain关键字来查看当前sql语句的执行情况,来对症下药.因为内容较多,放在最后进行讲解 2: 正确的建立索引:索引的建立对sql的影响是非常大的,如果对索引不太理解的可以看我另一篇文章: ............
疑难问题

22,209

社区成员

121,730

社区内容

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

加载中

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

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

+ 用AI写文章