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 表)来优化,效果非常好。

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值