此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
代码需要注释的,直接问就可以了