- 论坛徽章:
- 0
|
表1描述:giftcert(156581 rows)
{ TABLE "informix".giftcert row size = 50 number of columns = 11 index size = 36}
create table "informix".giftcert
(
gc_id char( not null,
gc_orig_store char(3),
gc_amount decimal(10,2),
gc_type char(1),
gc_date date,
gc_store char(3),
gc_invoice integer,
gc_customer char(10),
gc_slsman char(6),
gc_trans integer,
gc_extra char(1)
) extent size 16 next size 16 lock mode row;
revoke all on "informix".giftcert from "public";
create unique index "informix".i1giftcert on "informix".giftcert
(gc_date,gc_orig_store,gc_id,gc_invoice,gc_type);
表2描述:sap_capture_log(2085997 rows)
{ TABLE sap_capture_log row size = 33 number of columns = 5 index size = 49 }
create table sap_capture_log
(
type char(1),
cap_date date,
key1 char(20),
key2 integer,
key3 date
) extent size 16 next size 16 lock mode page;
revoke all on sap_capture_log from "public";
create index sap_cap1 on sap_capture_log (type,
key1,key2,key3);
问一下大虾有没有办法提高一下以下SQL的性能或者指出以下SQL的问题在哪里? 谢谢。
select gc_id,gc_orig_store,gc_amount,gc_type,gc_date,gc_invoice,gc_customer,'N'
from giftcert
where gc_date between today-7 and today-1
and gc_type="S" and
(select count(*) from sap_capture_log
where type="G" and key2=gc_id and key3=gc_date)=0
在一般情况下,需要3个小时才能把结果算出来,是不是太慢了,就这么个小SQL。本人对调优不是很在行,用过重建索引,重新load表,update statatistics,均不见效。
软件/硬件平台:
Compaq Proliant Server 7100/1024MB/Ultra 2 SCSI/
SCO OpenServer 5.0.4
root> memsize
1073213440
Informix Dynamic Server Version 7.31.UC2 -- On-Line -- Up 5 days 21:58:54 -- 3
68640 Kbytes
祝新年快乐! |
|