数据大概在10W行左右，运行速度很慢，高手帮忙优化下
[code=SQL][/code]
select distinct '1' as HJ, '1' as PX, KCSW_YWRQ, YFD_SHDID as CGSHMX_SHDID,
YFD_GYSID as CGSH_GYSID, YFD_CGYID as CGSH_CGYID, YFD_CKID as CGSH_CKID,
YFD_BMID as CGSH_BMID, YFD_LYLX as CGSH_SWLXID, YFDMX_WLID as CGSHMX_WLID,
WLXX_MC, WLXX_GG, WLXX_JLDW, WLXX_LBID, YFDMX_CGSL as CGSHMX_SSSL,
YFDMX_HSJG as CGSHMX_HSJG, YFDMX_HSJE as CGSHMX_HSJE, YFDMX_WSJG as CGSHMX_WSJG, YFDMX_WSJE as CGSHMX_WSJE,
nvl(FPSL,0) as YFDMX_FPSL, nvl(FPJE,0) as YFDMX_FPJE,
(nvl(FPSL,0) - nvl(JSSL,0)) as TZSL, (nvl(FPJE,0) - nvl(JSJE,0) ) as TZJE,
(YFDMX_CGSL - nvl(FPSL,0)) as WDPSL, (YFDMX_WSJE - nvl(JSJE,0) ) as WDPJE,
CGSHMX_POID, KCSW_DJBH, GHFP_KPRQ, FP.CGJSMX_FPID as GHFP_FPID, YFD_YWRQ,
YFDMX_YFDID, YFDMX_YFDXH, nvl(JSSL,0) as YFDMX_JSSL, nvl(JSJE,0) as YFDMX_JSJE
from YFDMX
left join YFD F on F.YFD_YFDID = YFDMX_YFDID
left join SWLX on SWLX_SWLXID = YFD_LYLX
left join KCSW on KCSW_DJBH = YFDMX_KCBH and KCSW_DJXH = YFDMX_KCXH
left join CGSHMX on CGSHMX_SHDID = KCSW_TZDBH and CGSHMX_XH = KCSW_TZDXH
left join CGSH on CGSH_SHDID = CGSHMX_SHDID
left join WLXX on WLXX_WLID = YFDMX_WLID
left join ( select max(CGJSMX_FPID) as CGJSMX_FPID, max(GHFP_KPRQ) as GHFP_KPRQ, CGJSMX_YFDID, CGJSMX_YFDXH,
sum(nvl(CGJSMX_FPSL,0)) as FPSL, sum(nvl(CGJSMX_WSJE,0)) as FPJE,
sum(nvl(CGJSMX_JSSL,0)) as JSSL, sum(nvl(CGJSMX_WSJSJE,0)) as JSJE
from CGJSMX
left join YFDMX on YFDMX_YFDID = CGJSMX_YFDID and YFDMX_YFDXH = CGJSMX_YFDXH
left join WLXX on WLXX_WLID = YFDMX_WLID
left join YFD G on YFD_YFDID = YFDMX_YFDID
left join GHFP on GHFP_FLID = CGJSMX_FLID and GHFP_FPID = CGJSMX_FPID
where nvl(CGJSMX_YFDID,' ')<>' ' and GHFP_ZT = 'F'
group by CGJSMX_YFDID, CGJSMX_YFDXH
) FP on FP.CGJSMX_YFDID = YFDMX_YFDID and FP.CGJSMX_YFDXH = YFDMX_YFDXH
where 1=1 and (SWLX_XTSWLXID = 'WGRK' or SWLX_XTSWLXID = 'WXRK')
[解决办法]
这么多的join！
第一优化数据库
第二，根据业务需求，对过程优化
找DBA帮你分析吧
[解决办法]
听高手说的，left join 先查数据少的，关联多的也能提高不少速度。
还有索引
我就知道这么多了
[解决办法]
不知道是啥数据库，不同数据库的优化处理不一样，但通用的做法是
1.将条件语句包含的字段做索引
如:
from YFDMX
left join YFD F on F.YFD_YFDID = YFDMX_YFDID
中 YFD.YFD_YFDID 和 YFDMX.YFDMX_YFDID 这2个字段都需要建立单独索引
2.join的时候，数据量少的表或查询放在前面
3.尽量避免使用 distinct ，like等，因为很少会利用到索引
[解决办法]
MSSQL好像有个性能分析器，看看哪个连接耗时间
[解决办法]
数据多了，这是很正常的事了
[解决办法]
有点像oracle，用plsql分析一下，可以看到占用时间最多的部分，进行有针对性的优化
当然，必要的索引啥的肯定是必不可少的
[解决办法]
语句写得有问题吧
[解决办法]
这么多的Left Join 不是SQL写得有问题就是数据库设计有问题！
[解决办法]
分页就行
[解决办法]
视图不行吗？
[解决办法]
你还是找个专业DBA帮你优化下数据库吧。。。
