巧用top percent优化top 1

废话不多说，直接上sql

SELECT  top 1  B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc

执行计划如下：

 

执行时间： 16秒、

 

分析：

嵌套循环之前，进行了排序操作，驱动表CREW_TASK 单表数据量300232。去重排序之后29w+。FLIGHTS 表单表44w+，这里不走hash 的原因是因为top1导致。故去掉 TOP 1 限制语句

 

SELECT   B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc

执行计划：

 

执行时间：

分析：很明显hash走了hash 匹配，可见在双大表情况下，hash 匹配返回5000多行比嵌套循环返回一条要快得多。

方向明确，那么怎么取上述第一条数据呢？这个时候用到 TOP percent，也就是返回结果集的一个比例值，而不是 top 1 第一条数据：

sql如下：

SELECT  top 100 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc

执行时间 ，返回100%的结果集

 

怎么返回第一条？很简单，我返回1%的结果集如下：

 

 

SELECT  top 1 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc

执行时间：，很明显返回的是1%的结果集

返回0.01的结果集：

SELECT  top 0.01 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc

执行时间：   已经达到要求。

 

注意：这里返回的是结果集的 百分比，虽然现在满足取第一条的要求，但是是随数据量增大，可能会多条， 故，外面包一层，取TOP 1 这样能保证只取一条。

select top 1 AA.* from (
SELECT  top 1 percent B.CREW_ID, E.CREW_NAME,C.OFFBLK,C.ONBLK,dbo.PEK_OPS_Date(A.STD) as STD
          FROM dbo.FLIGHTS A 
        LEFT OUTER JOIN
          (SELECT DISTINCT TASK_ID, CREW_ID
           FROM dbo.CREW_TASK
          ) B ON A.COCKPIT_CREW_GROUP = B.TASK_ID 
        LEFT OUTER JOIN dbo.FLIGHTS_CREW_REPORT C ON A.FLTID = C.FLTID 
        LEFT JOIN CREW_INFO E ON B.CREW_ID = E.CREW_ID
        LEFT JOIN FLIGHTS_STC F ON A.STC = F.IATA_CODE
      WHERE (A.COCKPIT_CREW_GROUP > 0) AND (A.STATUS='ATA')
        AND F.RATIO = 1 AND NOT (A.AC LIKE '%--%')
        AND B.CREW_ID = ISNULL(60, B.CREW_ID) AND C.OFFBLK is not null AND C.ONBLK is not null
        AND  C.CREW_LEFT is not null AND C.CREW_RIGHT is not null 
     ORDER BY A.STD desc )AA

执行时间：

 

 

 

收工！