sqlserver sql优化案例及思路
2024-04-03 02:26:28
始sql：
SELECT TOP 100 PERCENT ZZ.CREW_NAME AS 机组, ZZ.CREW_ID, AA.年度时间,
CC.当月时间, DD.连续七天时间 AS 最近七天
FROM (SELECT *
FROM CABIN_CREW_INFO
WHERE QUIT_DATE > CONVERT(VARCHAR, YEAR(43381)) + '-01-01')
ZZ LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 年度时间
FROM (SELECT A.FLTID AS FLTID, dbo.TOTTime2(C.OFFBLK, A.ETD, A.ATD, A.ATA, A.ETA,
C.ONBLK, A.STATUS,D.QAR_OUT,D.QAR_OFF,D.QAR_ON,D.QAR_IN) AS FLIGHT_TIME, B.*
FROM dbo.FLY A LEFT OUTER JOIN
(SELECT DISTINCT TASK_ID, CREW_ID, ALLPOS
FROM dbo.CABIN_CREW_TASK_COPOS) B ON
A.CABIN_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN
dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT OUTER JOIN
dbo.FLIGHTS_QAR D ON A.FLTID = D.FLTID
WHERE (A.STC <> 'K') AND (A.STATUS <> 'CNL') AND
(A.CABIN_CREW_GROUP > 0) AND (A.ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(43381)) + '-1-1') - 3.0 / 24, 120) AND CONVERT(VARCHAR,
CONVERT(DATETIME, (FLOOR(CONVERT(FLOAT, 43381)) + 1)
- 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120)) AND
((SELECT COUNT(*)
FROM CABIN_SPECIAL_FLIGHTS
WHERE (CREW_ID = B.CREW_ID) AND (FLTID = A.FLTID)) = 0))
DEL
GROUP BY CREW_ID) AA ON ZZ.CREW_ID = AA.CREW_ID LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 当月时间
FROM (SELECT A.FLTID AS FLTID, dbo.TOTTime2(C.OFFBLK, A.ETD, A.ATD, A.ATA, A.ETA,
C.ONBLK, A.STATUS,D.QAR_OUT,D.QAR_OFF,D.QAR_ON,D.QAR_IN) AS FLIGHT_TIME, B.*
FROM dbo.FLIGHTS A LEFT OUTER JOIN
(SELECT DISTINCT TASK_ID, CREW_ID, ALLPOS
FROM dbo.CABIN_CREW_TASK_COPOS) B ON
A.CABIN_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN
dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT OUTER JOIN
dbo.FLIGHTS_QAR D ON A.FLTID = D.FLTID
WHERE (A.STC <> 'K') AND (A.STATUS <> 'CNL') AND
(A.CABIN_CREW_GROUP > 0) AND (A.ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(43381)) + '-' + CONVERT(VARCHAR, MONTH(43381)) + '-1')
- 3.0 / 24, 120) AND CONVERT(VARCHAR, CONVERT(DATETIME,
(FLOOR(CONVERT(FLOAT, 43381)) + 1)
- 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120)) AND
((SELECT COUNT(*)
FROM CABIN_SPECIAL_FLIGHTS
WHERE (CREW_ID = B.CREW_ID) AND (FLTID = A.FLTID)) = 0))
DEL
GROUP BY CREW_ID) CC ON ZZ.CREW_ID = CC.CREW_ID LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 连续七天时间
FROM (SELECT A.FLTID AS FLTID, dbo.TOTTime2(C.OFFBLK, A.ETD, A.ATD, A.ATA, A.ETA,
C.ONBLK, A.STATUS,D.QAR_OUT,D.QAR_OFF,D.QAR_ON,D.QAR_IN) AS FLIGHT_TIME, B.*
FROM dbo.FLIGHTS A LEFT OUTER JOIN
(SELECT DISTINCT TASK_ID, CREW_ID, ALLPOS
FROM dbo.CABIN_CREW_TASK_COPOS) B ON
A.CABIN_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN
dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT OUTER JOIN
dbo.FLIGHTS_QAR D ON A.FLTID = D.FLTID
WHERE (A.STC <> 'K') AND (A.STATUS <> 'CNL') AND
(A.CABIN_CREW_GROUP > 0) AND (A.ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(DATEADD(DD, - 6, 43381))) + '-' + CONVERT(VARCHAR,
MONTH(DATEADD(DD, - 6, 43381))) + '-' + CONVERT(VARCHAR,
DAY(DATEADD(DD, - 6, 43381)))) - 3.0 / 24, 120) AND
CONVERT(VARCHAR, CONVERT(DATETIME, (FLOOR(CONVERT(FLOAT,
43381)) + 1) - 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120)) AND
((SELECT COUNT(*)
FROM CABIN_SPECIAL_FLIGHTS
WHERE (CREW_ID = B.CREW_ID) AND (FLTID = A.FLTID)) = 0))
DEL
GROUP BY CREW_ID) DD ON ZZ.CREW_ID = DD.CREW_ID
ORDER BY DD.连续七天时间 DESC
优化思路
1：分析最慢点：
-----分解sql执行，问题出现在这个sql
SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 年度时间
FROM (SELECT A.FLTID AS FLTID, dbo.TOTTime2(C.OFFBLK, A.ETD, A.ATD, A.ATA, A.ETA,
C.ONBLK, A.STATUS,D.QAR_OUT,D.QAR_OFF,D.QAR_ON,D.QAR_IN) AS FLIGHT_TIME, B.*
FROM dbo.fly A LEFT OUTER JOIN
(SELECT DISTINCT TASK_ID, CREW_ID, ALLPOS
FROM dbo.CABIN_CREW_TASK_COPOS) B ON
A.CABIN_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN
dbo.fly_CREW_REPORT C ON A.FLTID = C.FLTID LEFT OUTER JOIN
dbo.fly_QAR D ON A.FLTID = D.FLTID
WHERE (A.STC <> 'K') AND (A.STATUS <> 'CNL') AND
(A.CABIN_CREW_GROUP > 0) AND (A.ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(43381)) + '-1-1') - 3.0 / 24, 120) AND CONVERT(VARCHAR,
CONVERT(DATETIME, (FLOOR(CONVERT(FLOAT, 43381)) + 1)
- 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120)) AND
((SELECT COUNT(*)
FROM CABIN_SPECIAL_fly
WHERE (CREW_ID = B.CREW_ID) AND (FLTID = A.FLTID)) = 0))
DEL
GROUP BY CREW_ID
2：根据sqlserver提示建立索引，未达到效果。
------tunning1
-USE [ISA]
GO
CREATE NONCLUSTERED INDEX [<Missing_INDEX_20181009]
ON [dbo].[fly] ([STATUS],[ATD],[STC],[CABIN_CREW_GROUP])
INCLUDE ([FLTID],[ETD],[ETA],[ATA])
GO
3：红色部分大部分重复，出去一个and 条件谓词都一样，这种sql难以迭代更新维护，考虑with as 语句改写
with SS as
(SELECT A.FLTID AS FLTID,A.ATD AS ATD ,dbo.TOTTime2(C.OFFBLK, A.ETD, A.ATD, A.ATA, A.ETA,
C.ONBLK, A.STATUS,D.QAR_OUT,D.QAR_OFF,D.QAR_ON,D.QAR_IN) AS FLIGHT_TIME, B.*
FROM dbo.FLIGHTS A LEFT OUTER JOIN
(SELECT DISTINCT TASK_ID, CREW_ID, ALLPOS FROM dbo.CABIN_CREW_TASK_COPOS) B ON
A.CABIN_CREW_GROUP = B.TASK_ID LEFT OUTER JOIN
dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID LEFT OUTER JOIN
dbo.FLIGHTS_QAR D ON A.FLTID = D.FLTID
WHERE (A.STC <> 'K') AND (A.STATUS <> 'CNL') AND (A.CABIN_CREW_GROUP > 0) AND ((SELECT COUNT(*)
FROM CABIN_SPECIAL_FLIGHTS WHERE (CREW_ID = B.CREW_ID) AND (FLTID = A.FLTID)) = 0))
SELECT TOP 100 PERCENT ZZ.CREW_NAME AS 机组, ZZ.CREW_ID, AA.年度时间,
CC.当月时间, DD.连续七天时间 AS 最近七天
FROM (SELECT *
FROM CABIN_CREW_INFO
WHERE QUIT_DATE > CONVERT(VARCHAR, YEAR(43381)) + '-01-01')
ZZ LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 年度时间
FROM ( SELECT * FROM SS WHERE (ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(43381)) + '-1-1') - 3.0 / 24, 120) AND CONVERT(VARCHAR,
CONVERT(DATETIME, (FLOOR(CONVERT(FLOAT, 43381)) + 1)
- 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120))
)
DEL
GROUP BY CREW_ID) AA ON ZZ.CREW_ID = AA.CREW_ID LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 当月时间
FROM (SELECT * FROM SS WHERE (ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(43381)) + '-' + CONVERT(VARCHAR, MONTH(43381)) + '-1')
- 3.0 / 24, 120) AND CONVERT(VARCHAR, CONVERT(DATETIME,
(FLOOR(CONVERT(FLOAT, 43381)) + 1)
- 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120))
)
DEL
GROUP BY CREW_ID) CC ON ZZ.CREW_ID = CC.CREW_ID LEFT OUTER JOIN
(SELECT CREW_ID, DBO.HOURFORMAT(SUM(FLIGHT_TIME) / 60.0, 4)
AS 连续七天时间
FROM (SELECT * FROM SS WHERE
(ATD BETWEEN
CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR,
YEAR(DATEADD(DD, - 6, 43381))) + '-' + CONVERT(VARCHAR,
MONTH(DATEADD(DD, - 6, 43381))) + '-' + CONVERT(VARCHAR,
DAY(DATEADD(DD, - 6, 43381)))) - 3.0 / 24, 120) AND
CONVERT(VARCHAR, CONVERT(DATETIME, (FLOOR(CONVERT(FLOAT,
43381)) + 1) - 3.0 / 24 - 1.0 / (24 * 60 * 60)), 120))
)
DEL
GROUP BY CREW_ID) DD ON ZZ.CREW_ID = DD.CREW_ID
ORDER BY DD.连续七天时间 DESC
改写之后，sql临时表逻辑清晰，易于维护和性能优化，临时表SS,只是条件不同。在oracle这样能大幅度减少临时表扫描次数，可惜在sqlserver 效果不明显。
4：终极杀手锏，分析执行计划
查看上述sql红框sql进行了全表扫描及排序，可以考虑合适的索引替代：
create index CABIN_CREW_TASK_COPOS_20181009 on CABIN_CREW_TASK_COPOS(TASK_ID) INCLUDE(CREW_ID,ALLPOS)
立即可以看到优化效果
SQL Server 执行时间:
CPU 时间 = 25859 毫秒，占用时间 = 29679 毫秒。
总结：拿到sql，首先看整体框架，然后进行慢sql 分解，然后进行分布优化。通常情况下都是索引缺缺失，在OLTP环境中添加索引dba 需要权衡。
最新文章
- MySQL索引的设计和使用
- asp.net 有关时间各种（输出）处理
- 移动端设备UA检测
- 《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式
- Could not create the view: An unexpected exception was thrown. 电脑突然断电，myeclipse非正常关闭，出现错误
- 【转】使用Python的IDE：Eclipse+PyDev
- JavaScript基本概念(一) v0.5
- 正在执行的SQL和之前执行的SQL
- windows 下安装Python
- log4j.rootLogger详细说明
- C#读取XML文件的五个步骤
- z-index在IE中的坑
- 使用Aspose将DataTable转Excel
- oldboy s21day12.设计商城系统，主要提供两个功能：商品管理、会员管理。
- 消息 xxx，级别 16，状态 x，过程 sp_executesql，第 x 行 过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'。
- JAVA:简单添加菜单界面(swing)第二版
- 【转】在Linux下安装python3
- linux存储管理之磁盘配额
- MySQL-join的实现原理、优化及NLJ算法
- spring batch中控制step的走向