关于多表查询的效率问题

Question

代码如下：
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很小的
感觉这个方法的查询效率还是不高，有何优化方法？ 

Answer 1

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

Answer 2

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

Answer 3

--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)

Answer 4

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

Answer 5

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

Answer 6

哎，这个视图也是挺复杂的
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'

Answer 7

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

Answer 8

把条件转移到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'