sql查询很慢

[神谕风行]

我创建了一个视图如下，该视图查询速度特别慢，几百条数据要查30秒左右
create view [dbo].[view_T_NB_FZ_BH_XYRYXX] as( SELECT [T_NB_FZ_BH_XYRYXX].[id]
,[T_NB_FZ_BH_XYRYXX].[isdel]
,[T_NB_FZ_BH_XYRYXX].[dataversion]
,[T_NB_FZ_BH_XYRYXX].[lrr_sfzh]
,[T_NB_FZ_BH_XYRYXX].[lrsj]
,[T_NB_FZ_BH_XYRYXX].[xgr_sfzh]
,[T_NB_FZ_BH_XYRYXX].[xgsj]
,[T_NB_FZ_BH_XYRYXX].[rybh]
,[T_NB_FZ_BH_XYRYXX].[xm]
,[T_NB_FZ_BH_XYRYXX].[cym]
,[T_NB_FZ_BH_XYRYXX].[bh]
,[T_NB_FZ_BH_XYRYXX].[sfzh]
,[zd_ry_zjlx].[mc]
,[T_NB_FZ_BH_XYRYXX].[qtzjhm1]
,[T_NB_FZ_BH_XYRYXX].[qtzjlx2]
,[T_NB_FZ_BH_XYRYXX].[qtzjhm2]
,[T_NB_FZ_BH_XYRYXX].[qtzjlx3]
,[T_NB_FZ_BH_XYRYXX].[qtzjhm3]
,ZD_RY_XB.mc as xb
,ZD_RY_MZ.mc as mz
,[T_NB_FZ_BH_XYRYXX].[csrq]
,[T_NB_FZ_BH_XYRYXX].[nl]
,[T_NB_FZ_BH_XYRYXX].[lxfs]
,[T_NB_FZ_BH_XYRYXX].[qtlxfs]
,ZD_RY_HYZT.mc as [hyzk]
,ZD_RY_ZZMM.mc as [zzmm]
,ZD_RY_WHCD.mc as [whcd]
,[T_NB_FZ_BH_XYRYXX].[sg]
,ZD_RY_TX.[mc] as tx
,ZD_RY_ZY.[mc] as zy
,[T_NB_FZ_BH_XYRYXX].[zylb]
,[T_NB_FZ_BH_XYRYXX].[hjd]
,[T_NB_FZ_BH_XYRYXX].[hjdxz]
,[T_NB_FZ_BH_XYRYXX].[xzd]
,[T_NB_FZ_BH_XYRYXX].[xzdxz]
,[T_NB_FZ_BH_XYRYXX].[xzdxz_x]
,[T_NB_FZ_BH_XYRYXX].[xzdxz_y]
,[T_NB_FZ_BH_XYRYXX].[grxg]
,[T_NB_FZ_BH_XYRYXX].[sftsqt]
,ZD_RY_TSQT.[mc] as tsqt
,[T_NB_FZ_BH_XYRYXX].[zw]
,[T_NB_FZ_BH_XYRYXX].[dna]
,[T_NB_FZ_BH_XYRYXX].[sflar]
,[T_NB_FZ_BH_XYRYXX].[larsfsfmq]
,ZD_RY_XX.[bh] as xx
,ZD_RY_ZAGJ.[mc] as [zagj]
,ZD_RY_ZATD.mc as [zatd]
,[T_NB_FZ_BH_XYRYXX].[jtzk]
,[T_NB_FZ_BH_XYRYXX].[shgx]
,[T_NB_FZ_BH_XYRYXX].[wfss]
,ZD_RY_GJ.mc as [gj]
,[T_NB_FZ_BH_XYRYXX].[gzdw]
,[T_NB_FZ_BH_XYRYXX].[bz]
,[T_NB_FZ_BH_XYRYXX].[sfzbm]
,[T_NB_FZ_BH_XYRYXX].[dlr]
,[T_NB_FZ_BH_XYRYXX].[dlrdh]
,[T_NB_FZ_BH_XYRYXX].[sf]
,[T_NB_FZ_BH_XYRYXX].[sfythcj]
,[T_NB_FZ_BH_XYRYXX].[ay_bh]
,[T_NB_FZ_BH_XYRYXX].[ay_mc]
,[T_NB_FZ_BH_XYRYXX].[tbsj]
,[T_NB_FZ_BH_XYRYXX].[sfga]
,[T_NB_FZ_BH_XYRYXX].[gasj]
,[T_NB_FZ_BH_XYRYXX].[sfbmsf]
,[T_NB_FZ_BH_XYRYXX].[nlsx]
,[T_NB_FZ_BH_XYRYXX].[zpid]
,[T_NB_FZ_BH_XYRYXX].[crj_zjlx]
,[T_NB_FZ_BH_XYRYXX].[crj_zjhm]
,[T_NB_FZ_BH_XYRYXX].[ch]
,[T_NB_FZ_BH_XYRYXX].[qkqk]
,[T_NB_FZ_BH_XYRYXX].[sftb]
,[T_NB_FZ_BH_XYRYXX].[scspzt]
,[T_NB_FZ_BH_XYRYXX].[sfdy]
,[T_NB_FZ_BH_XYRYXX].[zwxx]
,[T_NB_FZ_BH_XYRYXX].[rddb]
,[T_NB_FZ_BH_XYRYXX].[zxwy]
,[T_NB_FZ_BH_XYRYXX].[cbdw_bh]
,[T_NB_FZ_BH_XYRYXX].[cbqy_bh]
,[T_NB_FZ_BH_XYRYXX].[ywjsxx]
,[T_NB_FZ_BH_XYRYXX].[jzdxzqh]
,[T_NB_FZ_BH_XYRYXX].[jzdz]
,[T_NB_FZ_BH_XYRYXX].[hjdxzqh]
,[T_NB_FZ_BH_XYRYXX].[ky]
,[T_NB_FZ_BH_XYRYXX].[tmtz]
,[T_NB_FZ_BH_XYRYXX].[tbbj]
,[T_NB_FZ_BH_XYRYXX].[zc]
,[T_NB_FZ_BH_XYRYXX].[fzjl]
,[T_NB_FZ_BH_XYRYXX].[qzcs]
,[T_NB_FZ_BH_XYRYXX].[cskssj]
,[T_NB_FZ_BH_XYRYXX].[zszt]
,[T_NB_FZ_BH_XYRYXX].[r_rssj]
,[T_NB_FZ_BH_XYRYXX].[c_cssj]
,[T_NB_FZ_BH_XYRYXX].[sfda]
,[T_NB_FZ_BH_XYRYXX].[rksj]
FROM [T_NB_FZ_BH_XYRYXX] left join zd_ry_zjlx on [T_NB_FZ_BH_XYRYXX].qtzjlx1 =zd_ry_zjlx.bh left join ZD_RY_XB on [T_NB_FZ_BH_XYRYXX].xb=ZD_RY_XB.bh left join ZD_RY_MZ on [T_NB_FZ_BH_XYRYXX].mz=ZD_RY_MZ.bh
left join ZD_RY_HYZT on [T_NB_FZ_BH_XYRYXX].hyzk= ZD_RY_HYZT.bh left join ZD_RY_ZZMM on [T_NB_FZ_BH_XYRYXX].zzmm= ZD_RY_ZZMM.bh left join ZD_RY_WHCD on [T_NB_FZ_BH_XYRYXX].whcd=ZD_RY_WHCD.bh
left join ZD_RY_TX on [T_NB_FZ_BH_XYRYXX].tx= ZD_RY_TX.bh left join ZD_RY_ZY on [T_NB_FZ_BH_XYRYXX].zy= ZD_RY_ZY.bh left join ZD_RY_TSQT on [T_NB_FZ_BH_XYRYXX].tsqt= ZD_RY_TSQT.bh left join ZD_RY_XX on [T_NB_FZ_BH_XYRYXX].xx= ZD_RY_XX.bh
left join ZD_RY_ZAGJ on [T_NB_FZ_BH_XYRYXX].zagj= ZD_RY_ZAGJ.bh left join ZD_RY_ZATD on [T_NB_FZ_BH_XYRYXX].zatd= ZD_RY_ZATD.bh left join ZD_RY_GJ on [T_NB_FZ_BH_XYRYXX].gj= ZD_RY_GJ.bh)

执行计划如下，不知道如何解决

[二月十六]

关联字段都加上索引试试；另外关联这么多表啊

[神谕风行]

关联的表都是词典表，每个词典表上都建了聚集索引，你的意思是我还要在主表的每一个关联字段上建立非聚集索引吗？

[OwenZeng_DBA]

你这个视图没有筛选条件，又join了很多表，速度慢很正常。你对视图的查询应该是有筛选条件的，你找到这个语句，看看这个语句慢不慢

[神谕风行]

事实上我建立了三个视图，每个视图都关联了很多表，最后我想要这三张视图再联合查询

[吉普赛的歌]

你这样没有任何意义， 直接贴出最后联合查询的SQL 及对应的执行计划

[OwenZeng_DBA]

引用 4 楼 qq_35796004 的回复:

事实上我建立了三个视图，每个视图都关联了很多表，最后我想要这三张视图再联合查询

那联合查询的SQL语句呢，发一下，执行时间是多久。

[神谕风行]

上面的这个sql语句执行时间在两分钟以上，我想要的数据是三张视图的全部数据，但是关联查询出来之后我发现，里面的内容并不全

[神谕风行]

select * from T_NB_FZ_BH_RAGL right join view_T_NB_FZ_BH_AJXX on T_NB_FZ_BH_RAGL.ajbh=view_T_NB_FZ_BH_AJXX.ajbh right join view_T_NB_FZ_BH_SARYXX on T_NB_FZ_BH_RAGL.rybh= view_T_NB_FZ_BH_SARYXX.rybh right join view_T_NB_FZ_BH_XYRYXX on T_NB_FZ_BH_RAGL.rybh= view_T_NB_FZ_BH_XYRYXX.rybh 这是联合查询的sql语句，不过我总觉得这个语句有问题

[吉普赛的歌]

不全的问题你自己理吧， 毕竟你才了解业务。 慢是因为没有做任何筛选， 有多少就得出多少。 这个查出来的总数据量是多少条记录？

[OwenZeng_DBA]

引用 8 楼 qq_35796004 的回复:

上面的这个sql语句执行时间在两分钟以上，我想要的数据是三张视图的全部数据，但是关联查询出来之后我发现，里面的内容并不全

如上面所说，你不要单独看这个视图，你把查询视图的语句执行看看，多少时间

[a2048]

建议借用优化顾问试试。