始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 需要权衡。