sqlserver sql优化案例及思路

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