select cc_crk_gsid,cc_wlbm_cpbh,cc_wlbm_pch,cc_wlbm_pm,cc_wlbm_ggxh,cc_wlbm_dj,cc_wlbm_pp,cc_wlbm_kz,isnull((select cast(sum(cc_jtmx_sjsl) as varchar)+'$'+cast(sum(cc_jtmx_sjsl*cc_wlbm_jz) as varchar) from v_cc_tj_khhw tj_qcrk where tj_qcrk.cc_crk_gsid=tj.cc_crk_gsid and tj_qcrk.cc_wlbm_cpbh=tj.cc_wlbm_cpbh and tj_qcrk.cc_wlbm_pch=tj.cc_wlbm_pch and tj_qcrk.cc_wlbm_pm=tj.cc_wlbm_pm and tj_qcrk.cc_wlbm_ggxh=tj.cc_wlbm_ggxh and tj_qcrk.cc_wlbm_dj=tj.cc_wlbm_dj and tj_qcrk.cc_wlbm_pp=tj.cc_wlbm_pp and tj_qcrk.cc_wlbm_kz=tj.cc_wlbm_kz and tj_qcrk.cc_crk_gsid=18 and datediff(d,'2010-11-01',tj_qcrk.rksj)<0),'0$0') as qcrks from v_cc_tj_khhw tj where cc_crk_gsid=18 and 1=1 group by cc_crk_gsid,cc_wlbm_cpbh,cc_wlbm_pch,cc_wlbm_pm,cc_wlbm_ggxh,cc_wlbm_dj,cc_wlbm_pp,cc_wlbm_kz
这条SQL语句,本身没有什么问题,但是执行速度很慢,里面就一个子查询,只是说,我把子查询条件tj_qcrk.cc_wlbm_cpbh=tj.cc_wlbm_cpbh and tj_qcrk.cc_wlbm_pch=tj.cc_wlbm_pch and tj_qcrk.cc_wlbm_pm=tj.cc_wlbm_pm and tj_qcrk.cc_wlbm_ggxh=tj.cc_wlbm_ggxh and tj_qcrk.cc_wlbm_dj=tj.cc_wlbm_dj and tj_qcrk.cc_wlbm_pp=tj.cc_wlbm_pp and tj_qcrk.cc_wlbm_kz=tj.cc_wlbm_kz,去掉,速度就很快,只是这些条件又不能取消,要不然数据出来的不准,我在这些字段中,都加了索引,可是速度还是慢,请达人指点
这条SQL语句,本身没有什么问题,但是执行速度很慢,里面就一个子查询,只是说,我把子查询条件tj_qcrk.cc_wlbm_cpbh=tj.cc_wlbm_cpbh and tj_qcrk.cc_wlbm_pch=tj.cc_wlbm_pch and tj_qcrk.cc_wlbm_pm=tj.cc_wlbm_pm and tj_qcrk.cc_wlbm_ggxh=tj.cc_wlbm_ggxh and tj_qcrk.cc_wlbm_dj=tj.cc_wlbm_dj and tj_qcrk.cc_wlbm_pp=tj.cc_wlbm_pp and tj_qcrk.cc_wlbm_kz=tj.cc_wlbm_kz,去掉,速度就很快,只是这些条件又不能取消,要不然数据出来的不准,我在这些字段中,都加了索引,可是速度还是慢,请达人指点
红色的条件取消,速度就很快
需要加上index的地方请加上,排除table scan部分。SELECT CC_CRK_GSID,CC_WLBM_CPBH,CC_WLBM_PCH,CC_WLBM_PM,CC_WLBM_GGXH
,CC_WLBM_DJ,CC_WLBM_PP,CC_WLBM_KZ
,ISNULL(CAST(SUM(TJ_QCRK.CC_JTMX_SJSL) AS VARCHAR)+'$'
+CAST(SUM(TJ_QCRK.CC_JTMX_SJSL*CC_WLBM_JZ) AS VARCHAR),'0$0') AS QCRKS
FROM V_CC_TJ_KHHW TJ
LEFT JOIN
V_CC_TJ_KHHW TJ_QCRK
ON (TJ_QCRK.CC_CRK_GSID=TJ.CC_CRK_GSID
AND TJ_QCRK.CC_WLBM_CPBH=TJ.CC_WLBM_CPBH
AND TJ_QCRK.CC_WLBM_PCH=TJ.CC_WLBM_PCH
AND TJ_QCRK.CC_WLBM_PM=TJ.CC_WLBM_PM
AND TJ_QCRK.CC_WLBM_GGXH=TJ.CC_WLBM_GGXH
AND TJ_QCRK.CC_WLBM_DJ=TJ.CC_WLBM_DJ
AND TJ_QCRK.CC_WLBM_PP=TJ.CC_WLBM_PP
AND TJ_QCRK.CC_WLBM_KZ=TJ.CC_WLBM_KZ
AND TJ_QCRK.CC_CRK_GSID=18
AND DATEDIFF(D,'2010-11-01',TJ_QCRK.RKSJ)<0
)
WHERE CC_CRK_GSID=18 AND 1=1
GROUP BY CC_CRK_GSID,CC_WLBM_CPBH,CC_WLBM_PCH,CC_WLBM_PM,
CC_WLBM_GGXH,CC_WLBM_DJ,CC_WLBM_PP,CC_WLBM_KZ
可以把执行计划截个图发上来看看。