sq语句高手帮忙

[kingtingshuo]

我现在正在做一个程序,三层结构,后台数据库是informix
因为涉及到10几个表,并且使用的频率很大,我需要一下子查出
一些记录,但各个表之间不关联,也就是可用or连接.我试着在
delphi中用多个query查询,但速度特慢.而改用联合之后还是速度慢
请问怎办才能加快速度!

[BoningSword]

你的数据量有多大?
关键字段是否都建了索引?
还有,where后面的条件语句怎么排列也很有讲究,不过没用过informix,
oracle倒是很熟.这样吧,你把你的sql列出来看看怎么样.

[PDK]

一点愚见：
1，尽量只查你要的数据
2，减少网络roundtrip
3,优化你的数据结构

[kingtingshuo]

帮帮忙吧

[liushiboy]

用到比较的字段要建立索引

[kingtingshuo]

语句为:
select his_ware.incode[1,1] as incode,his_ware.qty as qty from his_ware
where his_ware.chdate='12022003' and his_ware.incode='32080313'
union select his_gath.incode[1,2],sum(his_gath.sqty) as qty1 from his_gath
where incode='32080313' and dodate<='12022003'
group by incode,1 union select fjbh.gather_no[1,1],sum(fjbh.pickqty) as qty2 from fjbh
where fjbh.bh_date<='12022003' and fjbh.incode='32080313' and fjbh.workstat='1'
group by incode,1 union select uw_waretotray.goodsno[1,3],sum(uw_waretotray.sqty) as qty3 from uw_waretotray
where uw_waretotray.goodsno='32080313' and uw_waretotray.edodate <='12022003'
and uw_waretotray.stat='0'
group by uw_waretotray.goodsno,1 union select pick_full.incode[1,4],sum(pick_full.pickqty) as qty4 from pick_full
where pick_full.incode='32080313' and pick_full.pickdate<='12022003' and pick_full.workstat='4'
group by pick_full.incode,1
union select pick_refer.incode[1,5],sum(pick_refer.lastqty) as qty5 from pick_refer
where pick_refer.incode='32080313' and pick_refer.gather_no[1,8]<='20031202'
and pick_refer.workstat='5'
group by pick_refer.incode,1 union select picksh.printflag,sum(picksh.spickqty) as qty6 from picksh
where picksh.incode='32080313' and picksh.dodate<='12022003'
and picksh.pickflag='3' group by picksh.incode,1
union select othcase.gather_no[1,2],sum(othcase.pickqty) as qty7 from othcase
where othcase.incode='32080313' and othcase.pickdate<='12022003'
and othcase.workstat='5'
group by othcase.incode,1
union select order.merchan_no,sum(order.pickqty) as qty8 from order
where order.merchan_no[6,13]='32080313' and order.delivery_date<='20031202'
and pick_fg='Y'
group by order.merchan_no[6,13],1
union select flat.incode[1,6],sum(flat.pickqty) as qty9 from flat
where flat.pickdate<='12022003' and flat.incode='32080313'
and flat.workstat='5'
group by flat.incode,1
union select incode[1,7],sum(issu_qty) as qty10 from putorder
where gatherdate<='12022003' and incode='32080313' and hzstat='Y'
group by incode,1
union select incode[1,8],sum(spickqty) from movestock where incode='32080313'
and movedate<='12022003' and stat='1' group by 1,incode

看看各位高手帮帮忙

[zhengyi1978]

哥哥你用存储过程会快很多的.再有尽量把你的数据库设计的满足三范式啊

[playboy1012]

对，你该用存储过程，这样确实会快很多，我实践过！

[kingtingshuo]

各位大哥,谢谢帮忙.但后台不是我设计的.我也没办法.
存储过程没用过,有没有那位,给个例子参照参照.谢谢

[cjmo]

用系统自带的帮助文件，自已学一下储存过程什么过。

[haipin]

对啊，用存储过程啊。

[djkhym]

我认为影响性能的主要是在union语句，它要对结果集进行并运算，剃除重复部分逐一比较肯定会消耗大量时间。

[kingtingshuo]

各位大虾,帮帮忙

[kingtingshuo]

各位大哥,能不能写个存储过程给小弟看看.写一部分就可以,我参照一下

[djkhym]

create proc test
as
/* insert into tmptable incode,qty */
select his_ware.incode[1,1] as incode,his_ware.qty as qty from his_ware
where his_ware.chdate='12022003' and his_ware.incode='32080313'
go
/*这里是注释，我觉得可以将上述union中的结果集插入到一个临时的表中，然后再从表中选择数据时使用distinct 选项*/