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 表)来优化,效果非常好。
但如果数据量很大,而又需要返回大量的数据,那么更新统计信息的作用就不太明显,有时候反而会更慢。