sql优化实战：从40分钟到10秒（更新统计信息）

sql代码如下（这个代码只是所有代码的一部分）：

     SELECT    inout.biz_date ,
                    inout.EMP_ID ,
                    inout.duty_id ,
                    inout.store_id ,
                    org_id ,
                    'use_time' AS flag ,
                    inout.OUTTIME ,
                    inout.intime ,
                    MIN(inout.inTIME) OVER ( PARTITION BY org_id,
                                             inout.BIZ_DATE ) starttime ,
                    MAX(inout.outtime) OVER ( PARTITION BY org_id,
                                              inout.BIZ_DATE ) endtime ,
                    ROW_NUMBER() OVER ( PARTITION BY org_id, inout.BIZ_DATE ORDER BY intime DESC ) rn ,  -- review by glh
                    LV_NAME ,
                    TP_NAME ,
                    store_code ,
                    is_planed ,
                    inout.holiday ,
                    ISNULL(mc.calendar_days, 0) - ISNULL(ra.attendance_days, 0) AS morning_afternoon ,
                    type_id ,
                    NULL AS plan_store_count ,
                    NULL AS visit_store_count
          FROM      ( SELECT    intime ,
                                OUTTIME ,
                                CASE WHEN CONVERT(VARCHAR(2), INTIME, 114) < '12'
                                     THEN 1
                                     ELSE 2
                                END AS morning_afternoon ,
                                v.biz_date ,
                                V.STORE_ID ,
                                ms.type_id ,
                                v.EMP_ID ,
                                emp.duty_id ,
                                LV.ITEM_NAME LV_NAME ,
                                TP.ITEM_NAME TP_NAME ,
                                org.org_id ,
                                v.is_planed ,
                                ISNULL(ms.memo10, ms.store_code) AS store_code ,
                                mc.holiday
                      FROM      tb_ss v 
                                INNER JOIN tb_sof msf ON msf.store_id = v.store_id
                                                              AND LEFT(v.biz_date,
                                                              7) = msf.biz_date
                                INNER JOIN tb_of org ON msf.org_id = org.org_id
                                                              AND org.state = '1'
                                                              AND org.level_code IN (
                                                              3, 4 )
                                                              AND v.emp_id = org.emp_id
                                                              AND msf.biz_date = org.biz_date
                                                              AND org.biz_date IN (
                                                              '2016-10' )
                                INNER JOIN tb_ef emp ON emp.emp_id = org.emp_id
                                                              AND emp.biz_date IN (
                                                              '2016-10' )
                                                              AND org.biz_date = emp.biz_date
                                INNER JOIN tb_sf ms ON ms.store_id = v.store_id
                                                              AND ms.type_id <> 112119
                                                              AND ms.biz_date IN (
                                                              '2016-10' )
                                                              AND org.biz_date = ms.biz_date
                                                              AND ms.state = '1'
                                INNER JOIN tb_cal mc ON mc.cdate = v.biz_date 
                                                              AND mc.cdate BETWEEN ISNULL(emp.EMPLOYMENT,
                                                              '2001-01-01')
                                                              AND
                                                              ISNULL(emp.dimission,
                                                              '2999-01-01')
                                LEFT JOIN TB_DICT_ITEM LV ON LV.DICT_ITEM_ID = MS.LEVEL_ID
                                LEFT JOIN TB_DICT_ITEM TP ON TP.DICT_ITEM_ID = MS.TYPE_ID
                      WHERE     intime IS NOT NULL
                                AND outtime IS NOT NULL
                                AND func_code = 'SB123'

                                AND LEFT(v.biz_date, 7) IN ( '2016-10' )
                    ) inout 
                    LEFT JOIN ( SELECT  ra.emp_id ,
                                        COUNT(DISTINCT CAST(ra.biz_date AS VARCHAR)
                                              + LTRIM(ra.morning_afternoon))
                                        * 1.0 / 2 AS attendance_days
                                FROM    TB_ATT ra
                                        INNER JOIN tb_calendar mc ON mc.cal_date = ra.biz_date
                                                              AND mc.holiday = 0
                                WHERE   ra.half_date <> 'CCZT' 
                                        AND LEFT(ra.biz_date, 7) IN (
                                        '2016-10' )
                                GROUP BY ra.emp_id
                              ) ra ON ra.emp_id = inout.emp_id
                    LEFT JOIN ( SELECT  COUNT(*) AS calendar_days
                                FROM    tb_calendar mc
                                WHERE   1 = 1
                                        AND CONVERT(VARCHAR(7), mc.cdate, 120) IN (
                                        '2016-10' )
                                        AND mc.holiday = 0
                              ) mc ON 1 = 1

运行时间： 40分钟没有出结果。

表信息：tb_SS的数据量在100w以内，其他的表都是在几千到上万条。所以，原始的数据量并不大。

结果集：最后返回的结果集在100条左右。

优化方法：通过更新统计信息，再次运行代码10秒就返回了结果。

总结：当语句比较长或复制时，而又没办法通过修改语句来简化sql，同时表中数据不是很多，少的几千行，多的就100w行时，返回结果集也不多，可以试试更新统计信息（update statistics 表）来优化，效果非常好。

但如果数据量很大，而又需要返回大量的数据，那么更新统计信息的作用就不太明显，有时候反而会更慢。