SQL中，添加一个条件后，执行效率瞬间下降，怎么优化

Question

此SQL语句为查询数据库中，当前月，前一月，前两月数据，但是where中加了一个条件后，查找时间从2秒延长至50秒左右，请问造成这种状况的原因是什么？以及有什么优化方法？
SELECT
TR.RC_NO,
TR.FEE_TYPE,
TR.FISCAL_PERIOD,--月份
MIN (DATE_START) CUR_DATE_START,
MAX (DATE_END) CUR_DATE_END,
CONVERT(VARCHAR(30),CAST(SUM(ISNULL(FEE_ACTUAL,0))/ 10000 AS decimal(18,2)),1) AS CUR_FEE_ACTUAL,

replace(SUBSTRING ( CAST (DATEADD(m ,- 1,   CAST (TR.FISCAL_PERIOD + '01' AS DATE)) AS VARCHAR),1,7),'-','') AS PRE_FISCAL_PERIOD,
PRE.PRE_DATE_START,
PRE.PRE_DATE_END,
PRE.PRE_FEE_ACTUAL,

replace(SUBSTRING ( CAST (dateadd(m ,- 2,   CAST (TR.FISCAL_PERIOD + '01' AS DATE)) AS VARCHAR),1,7),'-','') AS BEF_FISCAL_PERIOD,
BEF.BEF_DATE_START,
BEF.BEF_DATE_END,
BEF.BEF_FEE_ACTUAL

FROM
T_AP_PAYMENT_REQUEST TR

LEFT JOIN
(
SELECT
TP.RC_NO,TP.FEE_TYPE,
TP.FISCAL_PERIOD,
MIN (DATE_START) PRE_DATE_START,
MAX (DATE_END) PRE_DATE_END,
CONVERT(VARCHAR(30),CAST(SUM(ISNULL(FEE_ACTUAL,0))/ 10000 AS decimal(18,2)),1) AS PRE_FEE_ACTUAL
FROM
T_AP_PAYMENT_REQUEST TP
WHERE

IS_DELETE = '0'
AND IS_AUTO_PAY = 'D00002'
AND ISNULL(IS_FIRST_PAY, '') != 'D00002'
AND PR_STATUS != 'L04301'
GROUP BY
TP.RC_NO,
TP.FEE_TYPE,
TP.FISCAL_PERIOD
) PRE
ON PRE.RC_NO = TR.RC_NO AND PRE.FEE_TYPE=TR.FEE_TYPE

LEFT JOIN
(
SELECT
TA.RC_NO,TA.FEE_TYPE,
TA.FISCAL_PERIOD,
MIN (DATE_START) BEF_DATE_START,
MAX (DATE_END) BEF_DATE_END,
CONVERT(VARCHAR(30),CAST(SUM(ISNULL(FEE_ACTUAL,0))/ 10000 AS decimal(18,2)),1) AS BEF_FEE_ACTUAL
FROM
T_AP_PAYMENT_REQUEST TA
WHERE

IS_DELETE = '0'
AND IS_AUTO_PAY = 'D00002'
AND ISNULL(IS_FIRST_PAY, '') != 'D00002'
AND PR_STATUS != 'L04301'
GROUP BY
TA.RC_NO,
TA.FEE_TYPE,
TA.FISCAL_PERIOD
) BEF
ON BEF.RC_NO = TR.RC_NO AND BEF.FEE_TYPE=TR.FEE_TYPE

WHERE
TR.FISCAL_PERIOD='201701' --此语句为添加的条件
AND
PRE.FISCAL_PERIOD = replace(SUBSTRING (CAST (dateadd(m ,- 1,CAST (TR.FISCAL_PERIOD + '01' AS DATE)) AS VARCHAR),1,7),'-','')
AND BEF.FISCAL_PERIOD = replace(SUBSTRING (CAST (dateadd(m ,- 2,CAST (TR.FISCAL_PERIOD + '01' AS DATE)) AS VARCHAR),1,7),'-','')
AND TR.IS_DELETE = '0'
AND TR.IS_AUTO_PAY = 'D00002'
AND ISNULL(TR.IS_FIRST_PAY, '') != 'D00002'
AND TR.PR_STATUS != 'L04301'
GROUP BY
TR.RC_NO,
TR.FEE_TYPE,
TR.FISCAL_PERIOD,
PRE.PRE_DATE_START,
PRE.PRE_DATE_END,
PRE.PRE_FEE_ACTUAL,
BEF.BEF_DATE_START,
BEF.BEF_DATE_END,
BEF.BEF_FEE_ACTUAL
ORDER BY
TR.RC_NO,
TR.FEE_TYPE,
TR.FISCAL_PERIOD

代码需要注释的，直接问就可以了

Answer 1

在where中把过滤条件多的放在最前面，把过滤条件少的放后面，可以试试
参考：http://www.cnblogs.com/daxiongblog/p/4350583.html