视图执行速度慢的问题!

[swxandwf]

SELECT DISTINCT
dbo.wlss.wlss_id, dbo.wlss.wlss_ms, dbo.wlss.wlss_fy, dbo.wlss.wlss_shzt, dbo.wlss.wlss_ntime, dbo.wlss.wlss_bz, dbo.wlss.wlss_csbz,
dbo.wlss.wlss_zsbz, dbo.ddxx.d_zrbm, dbo.wlss.wlss_sjss, dbo.wlss.wlss_hdh, dbo.wlss.wlss_sfbc, dbo.wlss.wlss_bc_csbz,
dbo.wlss.wlss_bc_zsbz, dbo.wlss.wlss_bc_fy, dbo.wlss.wlss_bc_bz, dbo.gsxx.g_gsmc, dbo.pcxx.pc_xyh, wlssbmxx.bmbh, wlssbmxx.bmmc,
ISNULL(dbo.wlss.wlss_fy, 0) + ISNULL(dbo.wlss.wlss_bc_fy, 0) AS ygpk, ISNULL(zwlss.zssje, 0) AS wlssje, ISNULL(zwlss.zcsje, 0) AS wlsscsje,
ISNULL(dbo.wlss.wlss_sjss, 0) - ISNULL(zwlss.zssje, 0) AS sjss, wlssbmxx.bmmc AS wlssbmmc, dbo.pcxx.pc_id, dbo.plandd.p_hdbh, dbo.gsxx.g_id,
dbo.ddxx.d_bdrq, ISNULL(wlsr.wlsrje, 0) AS wlsrje, ISNULL(zwlss.zssje, 0) - ISNULL(wlsr.wlsrje, 0) AS wclje
FROM dbo.wlss INNER JOIN
dbo.zcxx ON dbo.wlss.wlss_pcid = dbo.zcxx.zc_pcid INNER JOIN
dbo.pcxx ON dbo.pcxx.pc_id = dbo.zcxx.zc_pcid INNER JOIN
dbo.plandd ON dbo.plandd.pl_id = dbo.zcxx.zc_plid AND dbo.plandd.p_hdbh = dbo.wlss.wlss_hdh INNER JOIN
dbo.ddxx ON dbo.ddxx.d_id = dbo.plandd.p_ddid INNER JOIN
dbo.gsxx ON dbo.gsxx.g_id = dbo.ddxx.d_gsid INNER JOIN
dbo.bmxx AS wlssbmxx ON dbo.ddxx.d_zrbm = wlssbmxx.bmbh LEFT OUTER JOIN
(SELECT dbo.clss.clss_ljwlssid, SUM(ISNULL(dbo.clss.clss_zsje, 0)) AS zssje, SUM(ISNULL(dbo.clss.clss_csje, 0)) AS zcsje
FROM dbo.clss INNER JOIN
dbo.wlss AS wlss_2 ON dbo.clss.clss_ljwlssid = wlss_2.wlss_id
WHERE (wlss_2.wlss_shzt = '2')
GROUP BY dbo.clss.clss_ljwlssid) AS zwlss ON zwlss.clss_ljwlssid = dbo.wlss.wlss_id LEFT OUTER JOIN
(SELECT clss_1.clss_ljwlssid, ISNULL(SUM(dbo.szxx.sz_srje), 0) AS wlsrje
FROM dbo.szxx INNER JOIN
dbo.clss AS clss_1 ON dbo.szxx.sz_spid = clss_1.clss_id INNER JOIN
dbo.wlss AS wlss_1 ON wlss_1.wlss_id = clss_1.clss_ljwlssid
WHERE (wlss_1.wlss_shzt = '2') AND (dbo.szxx.sz_srtype = 5)
GROUP BY clss_1.clss_ljwlssid) AS wlsr ON dbo.wlss.wlss_id = wlsr.clss_ljwlssid


如果去掉黑色的死,速度快一倍,真是百思不得其解

[fcuandy]

把distinct去掉，

然后比较去黑色和不去黑色效率差异大不。

如果去掉distinct 后， 有没有黑色部分效率差异不大，就是实际数据引起的。

[swxandwf]

为什么去掉颜色加深部份,速度就变得很快,但是同一张视图,在其他的库里,执行速度就很快.真是怪事,有人说,是索引坏了,但我跟本没有加索引,其实我知道问题出在最后一个left中,但光执行left后面的这个,速度也很快的,真是奇了个怪了,各位朋友有没有碰到这样的问题呀?

[swxandwf]

去掉之后,速度确实要快,但这样的话,数据就变多了很多,肯定不行的,这里就是很奇怪,我两个一模一样的库,一个快,一个慢,数据等都是一样的,最后没有办法,我在慢的那个库上做了测试,我将显示的最后两列去掉,速度就快了一倍

[fcuandy]

我不是让你去。而是让你去掉distinct然后比较 有黑字和无黑字两种情况下效率的差别。

如果这时，二者区别不大，就可以确定是数据的问题。

[Garnett_KG]

分别在两个库中拿执行计划比较就知道了。

[ilovewalk]

试试Database Engine Tuning Advisor(DTA),看有没有推荐的优化方法.

[swxandwf]

[Quote=引用 5 楼 Garnett_KG 的回复:]

分别在两个库中拿执行计划比较就知道了。
[/Quote]


兄弟呀,可不可以讲得具体点,并不是很清楚你的想法?你不是说,进行sql跟踪?

[Garnett_KG]

--先执行
SET SHOWPLAN_ALL ON

--然后再执行你的语句

[swxandwf]

出来的东东看不懂,你想要知道什么?我告诉你,可以吗?

[Garnett_KG]

分别再两个库中都执行一次，然后再把执行计划都帖上来，注明分别属於哪个库的。

[swxandwf]

select *from v_zcdd_kh where 1=1 and (pc_ssbm='1002') and gzs=0 and d_bdrq>='2008-5-1' and d_id in (select d_id from v_zcxx where 1=1 and (pc_ssbm='1002') and gzs=0 and d_bdrq>='2008-5-1')

select *from v_zcdd_kh where 1=1 and (pc_ssbm='1002') and gzs=0 and d_bdrq>='2008-5-1' and exists (select 1 from v_zcxx where v_zcxx.d_id=v_zcdd_kh.d_id and v_zcxx.pc_id=v_zcdd_kh.pc_id and (pc_ssbm='1002') and gzs=0 and d_bdrq>='2008-5-1')

想问一下,这两句的执行速度如何,我以前是用上面这个的,但想试一下exists,不知道这样写对不对?