Informix 性能调优问题
表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
{ 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
9同行回答
create index giftcertsy1 on giftcert(gc_type, gc_date, gc_id);
create index sap_capture_logsy1 on sap_capture_log (type, key2,key3);收起
create index sap_capture_logsy1 on sap_capture_log (type, key2,key3);收起
浏览1573
存在如下问题：
1、索引创建有问题。可以尝试创建如下索引
create index i1giftcert1 on giftcert (gc_date,gc_type);
create index i1giftcert2 on giftcert (gc_date,gc_id);
create index sap_cap2 on sap_capture_log (key3,key2);
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 not exists(
select 1 from sap_capture_log
where
sap_capture_log.type="G"
sap_capture_log.key2=giftcert.gc_id
and sap_capture_log.key3=giftcert.gc_date
)收起
1、索引创建有问题。可以尝试创建如下索引
create index i1giftcert1 on giftcert (gc_date,gc_type);
create index i1giftcert2 on giftcert (gc_date,gc_id);
create index sap_cap2 on sap_capture_log (key3,key2);
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 not exists(
select 1 from sap_capture_log
where
sap_capture_log.type="G"
sap_capture_log.key2=giftcert.gc_id
and sap_capture_log.key3=giftcert.gc_date
)收起
浏览1517
建议还是先看一下sqexplain的输出，最好是在测试环境中实际跑一段时间，以记录一定的time statistics凸显问题核心收起
浏览1544
试试以下方法，采用临时表的方法，而不是使用相关子查询。
select type, key2, key3, count(*) count_log from sap_capture_log group by type,key2,key3 into temp temp_count;
create unique index idx_tmp_count on temp_count(type,key2,key3);
select gc_id,gc_orig_store,gc_amount,gc_type,gc_date,gc_invoice,gc_customer,'N'
from giftcert, temp_count t
where gc_date between today-7 and today-1 and gc_type="S"
and t.type="G" and gc_id=t.key2 and gc_date=t.key3 and t.count_log=0;收起
select type, key2, key3, count(*) count_log from sap_capture_log group by type,key2,key3 into temp temp_count;
create unique index idx_tmp_count on temp_count(type,key2,key3);
select gc_id,gc_orig_store,gc_amount,gc_type,gc_date,gc_invoice,gc_customer,'N'
from giftcert, temp_count t
where gc_date between today-7 and today-1 and gc_type="S"
and t.type="G" and gc_id=t.key2 and gc_date=t.key3 and t.count_log=0;收起
浏览1540