开发札记sql提速

事件：提速从24秒提速到1秒内，关键是这个sql语句的奇怪现象让我感觉很不可思议，粘出来代码。大家分享下！

先前语句sql，

WITH TEMP AS
(
SELECT  D.FzherenItcode,P.objid release
FROM MB_RecruitmentDoc D
INNER JOIN MB_RecruitmentDoc_Antifilled AN ON AN.DemandID=D.DemandID AND D.ThisFlowEndDate>'2010-4-1 0:00:00' AND D.ThisFlowEndDate<='2011-3-31 0:00:00'
INNER JOIN position P ON P.objid=AN.StVacancyPostNum
),
 OnBoard  AS
(
SELECT  a.RecommendITcode ItCode, c.ID onNum  FROM MB_Resume_Recommend a
INNER JOIN MB_Recommend_Resume b ON a.RecommendID=b.RecommendID AND a.RecommendTime>'2010-4-1 0:00:00' and a.RecommendTime<='2011-3-31 0:00:00'                    
INNER JOIN MB_Resume_Dc_UserInfo c ON c.ResumeID=b.ResumeID 
)

SELECT A.ItCode,
ISNULL(COUNT(T.release),0) release,
ISNULL(SUM(M.ResumeID),0) input,
ISNULL(SUM(r1.RecommendType),0) receive,
ISNULL(SUM(r3.Recommendcount),0) business,
ISNULL(SUM(r2.Recommendcount),0) send,
ISNULL(COUNT(1),0) inSum

FROM  Accounts_Users A
LEFT JOIN TEMP T ON T.FzherenItcode=A.ItCode
LEFT JOIN MB_Posinal M ON M.SourceItCode=A.ItCode AND M.SourceID=2015 AND M.LastModifyTime>'2010-4-1 0:00:00' AND M.LastModifyTime<='2011-3-31 0:00:00'  
LEFT JOIN MB_Resume_Recommend r1 ON r1.RecommendedITcode=A.ItCode AND r1.RecommendType!=3 AND r1.RecommendTime>'2010-4-1 0:00:00' AND r1.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r2 ON r2.RecommendITcode=A.ItCode AND r2.RecommendType!=3 AND r2.RecommendTime>'2010-4-1 0:00:00' AND r2.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r3 ON r3.RecommendITcode=A.ItCode AND r3.RecommendType=3 AND r3.RecommendTime>'2010-4-1 0:00:00' AND r3.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN OnBoard B ON B.ItCode=A.ItCode
GROUP BY A.ItCode

---

优化后的

WITH TEMP AS
(
SELECT  D.FzherenItcode,P.objid release
FROM MB_RecruitmentDoc D
INNER JOIN MB_RecruitmentDoc_Antifilled AN ON AN.DemandID=D.DemandID AND D.ThisFlowEndDate>'2010-4-1 0:00:00' AND D.ThisFlowEndDate<='2011-3-31 0:00:00'
INNER JOIN position P ON P.objid=AN.StVacancyPostNum
),
 OnBoard  AS
(
SELECT  a.RecommendITcode ItCode, c.ID onNum  FROM MB_Resume_Recommend a
INNER JOIN MB_Recommend_Resume b ON a.RecommendID=b.RecommendID AND a.RecommendTime>'2010-4-1 0:00:00' and a.RecommendTime<='2011-3-31 0:00:00'                    
INNER JOIN MB_Resume_Dc_UserInfo c ON c.ResumeID=b.ResumeID 
)

SELECT A.ItCode,
ISNULL(COUNT(T.release),0) release,
ISNULL(SUM(M.ResumeID),0) input,
ISNULL(SUM(CASE WHEN r1.RecommendType!=3 THEN  ISNULL(r1.Recommendcount,0) ELSE 0 END),0) receive,
ISNULL(SUM(CASE WHEN r1.RecommendType=3 THEN  ISNULL(r1.Recommendcount,0) ELSE 0 END),0) business,
ISNULL(SUM(r2.Recommendcount),0) send,
ISNULL(COUNT(1),0) inSum

FROM  Accounts_Users A
LEFT JOIN TEMP T ON T.FzherenItcode=A.ItCode
LEFT JOIN MB_Posinal M ON M.SourceItCode=A.ItCode AND M.SourceID=2015 AND M.LastModifyTime>'2010-4-1 0:00:00' AND M.LastModifyTime<='2011-3-31 0:00:00'  
LEFT JOIN MB_Resume_Recommend r1 ON r1.RecommendedITcode=A.ItCode AND r1.RecommendTime>'2010-4-1 0:00:00' AND r1.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r2 ON r2.RecommendITcode=A.ItCode AND r2.RecommendType!=3 AND r2.RecommendTime>'2010-4-1 0:00:00' AND r2.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN OnBoard B ON B.ItCode=A.ItCode
GROUP BY A.ItCode

——————————————————————————————————————————————————————————————

初看差不多，明显的第2个case when 语句，

LEFT JOIN MB_Resume_Recommend r1 ON r1.RecommendedITcode=A.ItCode AND r1.RecommendType!=3 AND r1.RecommendTime>'2010-4-1 0:00:00' AND r1.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r3 ON r3.RecommendITcode=A.ItCode AND r3.RecommendType=3 AND r3.RecommendTime>'2010-4-1 0:00:00' AND r3.RecommendTime<='2011-3-31 0:00:00'

这2个逻辑相反，不用再次关联表就可以实现了。直接用case when 语句，由于思维定势，上去就关联表。从最早的那个sql语句可以看出

LEFT JOIN MB_Resume_Recommend r1 ON r1.RecommendedITcode=A.ItCode AND r1.RecommendType!=3 AND r1.RecommendTime>'2010-4-1 0:00:00' AND r1.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r2 ON r2.RecommendITcode=A.ItCode AND r2.RecommendType!=3 AND r2.RecommendTime>'2010-4-1 0:00:00' AND r2.RecommendTime<='2011-3-31 0:00:00'
LEFT JOIN MB_Resume_Recommend r3 ON r3.RecommendITcode=A.ItCode AND r3.RecommendType=3 AND r3.RecommendTime>'2010-4-1 0:00:00' AND r3.RecommendTime<='2011-3-31 0:00:00'

关联前2个都没问题，一旦关联上第3个连接语句，速度就超慢，仔细查看发现。前2个语句都是Type！=3，如果第3个语句Type=3。那么的话，还得去重新扫描这个表。应为前两个表已经筛选过了，再重新筛选。速度自然自然很慢。

其实速度提升的办法很简单，case when 也很容易。但是由于思维定势，没想到用case when 去解决，还是去反复关联。

经过改造，完全可以去掉第3个关联。