关于多表查询的效率问题

[jingxijun]

代码如下：
select a.orgid,a.secuid,a.stkcode,a.bsflag,cast(a.clearamt as int) mmje,cast(b.fundavl as int) kyye,cast(a.clearamt/5 as int) taxi,a.custid,a.custname,a.matchqty
from dlcg.run.dbo.orderrec a,dlcg.run.dbo.fundasset b
where a.clearamt<>0 and a.bsflag='0S' and a.secuid+a.stkcode in
(select fwgddm+fwzqdm collate Chinese_PRC_BIN from v_xsgdk) and a.custid=b.custid

其中 dlcg.run.dbo.orderrec 和 dlcg.run.dbo.fundasset 这两个表都是百万条记录左右，v_xsgdk很小的
感觉这个方法的查询效率还是不高，有何优化方法？

[jingxijun]

而且刚才测了，即使把视图转换成1个表，直接链接表并加索引的话，效率也没有提供

[v1ctory1216]

除了把in语句用exists还掉外，其余的好像没什么可优化的了

[王向飞]

JOIN v_xsgdk AS C ON a.secuid+a.stkcode = C.fwgddm+C.fwzqdm

[水族杰纶]

--try
select a.orgid,
       a.secuid, 
       a.stkcode,
       a.bsflag,
       cast(a.clearamt as int) mmje,
       cast(b.fundavl as int) kyye,
       cast(a.clearamt/5 as int) taxi,
       a.custid,
       a.custname,
       a.matchqty
 from dlcg.run.dbo.orderrec a,dlcg.run.dbo.fundasset b,v_xsgdk c
 where a.clearamt<>0 and a.bsflag='0S' and a.custid=b.custid
 and (a.secuid+a.stkcode=c.fwgddm+c.fwzqdm collate Chinese_PRC_BIN)

[jingxijun]

to 水族杰纶：
刚才测了下，和原来的基本一样，你的事24秒，原来的是23秒

[水族杰纶]

[Quote=引用 4 楼 jingxijun 的回复:]
to 水族杰纶：
刚才测了下，和原来的基本一样，你的事24秒，原来的是23秒
[/Quote]
索引了没？

[永生天地]

是链接服务器查询慢的问题吧

[ChinaJiaBing]

建好相关索引.........

[jingxijun]

orderrec 和 secuid 这两表是有custid这个索引的
v_xsgdk 是个视图

[jingxijun]

再，数据库是SQL2000,视图好像没法建索引，再，这个视图也就几百条记录

[水族杰纶]

[Quote=引用 9 楼 jingxijun 的回复:]
再，数据库是SQL2000,视图好像没法建索引，再，这个视图也就几百条记录
[/Quote]
能否换个思路
不通过视图呢
直接连接视图中包含的表呢
在基表上建立索引

[jingxijun]

哎，这个视图也是挺复杂的
CREATE view v_xsgdk
as
select orgid,fwgddm,fwzqdm,cast(fwmrje as bigint) fwmrje,fwzysm,convert(char(8),fwfsrq,112) fwfsrq from SJSFW
left join secuid c on fwgddm=secuid
where fwsjlb in ('20','30') and fwfsrq = (select max(fwfsrq) from SJSFW)
union all
select orgid,left(ZH1,10) fwgddm,left(ZQDM,6) FWZQDM,cast(JE1 as float) fwmrje,BZ fwzysm,TZRQ fwfsrq from tzxx
left join secuid c on ZH1=secuid
where TZRQ=(select max(TZRQ) from tzxx) and tzlb='021'

[feilniu]

a表的(clearamt,bsflag,secuid,stkcode)建一个组合索引。

[一朝春尽红颜老]

把条件转移到ON上面

select a.orgid,a.secuid,a.stkcode,a.bsflag,cast(a.clearamt as int) mmje,cast(b.fundavl as int) kyye,cast(a.clearamt/5 as int) taxi,a.custid,a.custname,a.matchqty
 from dlcg.run.dbo.orderrec a join dlcg.run.dbo.fundasset b on a.custid=b.custid 
where a.clearamt<>0 and a.bsflag='0S' and a.secuid+a.stkcode in
 (select fwgddm+fwzqdm collate Chinese_PRC_BIN from v_xsgdk)

--
a.secuid+a.stkcode in
(select fwgddm+fwzqdm collate Chinese_PRC_BIN from v_xsgdk)
--这段会影响效率,尽量不要用in,即使secuid有index也不会起作用.
试着把a.secuid+a.stkcode 拆开,不要用连接做条件.

--please have a try
select a.orgid,a.secuid,a.stkcode,a.bsflag,cast(a.clearamt as int) mmje,cast(b.fundavl as int) kyye,cast(a.clearamt/5 as int) taxi,a.custid,a.custname,a.matchqty
 from dlcg.run.dbo.orderrec a join dlcg.run.dbo.fundasset b on a.custid=b.custid
join v_xsgdk v on a.secuid collate Chinese_PRC_BIN=v.fwgddm and a.stkcode collate Chinese_PRC_BIN=v.fwzqdm 
where a.clearamt<>0 and a.bsflag='0S' 

[jingxijun]

TO 楼上，你的代码要50秒，原来代码的42秒

[dmg7205]

好象也只能用增加索引

[jingxijun]

貌似不是很难啊