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