IDS Forum

SQL return high query cost on ids 12.10

Posted By: MOHD FADZIL JUSOH
Date: Monday, 16 May 2016, at 3:59 a.m.

Hi,

I do some testing & comparison between ids 12.10 with IDS 11.10 (OS for both version same) . I try to run SQL sytax for at both version,estimated cost return to very high on ids 12.10.

Result as below:

1. result run on IDS 11.10

/home/stjf $ vi acc_pay.txt
"acc_pay.txt" 38 lines, 1464 characters

QUERY: (OPTIMIZATION TIMESTAMP: 04-07-2016 14:03:46)
------
select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt),
rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no
from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0
and ref_no ="T00001"
and ref_typ = "AGT"
and ( trn_cd is not null and trn_cd != " ")
and pay_dt <="31/03/2016"
order by pay_dt

Estimated Cost: 1344
Estimated # of Rows Returned: 903

1) informix.acc_pay: INDEX PATH

Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N
OT NULL )

(1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL)

Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' )

Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016

Index Key Filters: (informix.acc_pay.trn_cd != ' ' )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 acc_pay

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 903 829946 00:00:39 1345

2. Result run on IDS 12.10

/home/informix/task/april $ more acc_pay4.txt

QUERY: (OPTIMIZATION TIMESTAMP: 04-29-2016 13:57:00)
------
select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt),
rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no
from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0
and ref_no ="T00001"
and ref_typ = "AGT"
and ( trn_cd is not null and trn_cd != " ")
and pay_dt <="31/03/2016"
order by pay_dt

Estimated Cost: 2188546
Estimated # of Rows Returned: 250424

1) informix.acc_pay: INDEX PATH

Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info
rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn
_cd IS NOT NULL )

(1) Index Name: informix.i_acc_pay

Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_do
c_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL
)

Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc
_pay.ref_typ = 'AGT' )

Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016

Index Key Filters: (informix.acc_pay.trn_cd != ' ' )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 acc_pay

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 250424 829946 00:08.01 2188547

3. Table schema with index.

/home/informix/task/april $ dbschema -db life -t acc_pay -ss

DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC6AEE

{ TABLE "informix".acc_pay row size = 118 number of columns = 22 index size = 201 }

create table "informix".acc_pay
(

ref_typ char(3) not null ,

ref_no char(13) not null ,

pay_dt date not null ,

pay_typ char(3) not null ,

pay_chg_typ char(3) not null ,

trn_cd char(2),

doc_series char(2),

yymm char(4),

doc_no integer,

prio_no smallint,

pay_amt decimal(12,2),

pay_int_amt decimal(12,2),

paid_amt decimal(12,2),

adj_amt decimal(12,2),

rel_amt decimal(12,2),

bnk_cd char(4),

bas_doc_typ char(3),

bas_doc_no char(14),

clt_cd char(8),

surr_key char(8),

lst_int_dt date,

versn smallint
) in datadb3 extent size 1769488 next size 221186 lock mode row;

revoke all on "informix".acc_pay from "public" as "informix";

create index "stjf".i05_acc_pay on "informix".acc_pay (ref_typ,

trn_cd,pay_dt) using btree in datadb3;
create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd)

using btree in idxdbs3;
create index "informix".i2_acc_pay on "informix".acc_pay (surr_key)

using btree in idxdbs3;
create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ,

bas_doc_no,pay_typ,pay_dt) using btree in datadb3;
create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series,

yymm,doc_no) using btree in datadb3;
create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ,

pay_typ,pay_chg_typ,bas_doc_no) using btree in datadb3;
create unique index "informix".i_acc_pay on "informix".acc_pay

(ref_typ,ref_no,pay_dt,pay_typ,pay_chg_typ,bas_doc_typ,bas_doc_no,

clt_cd,trn_cd,doc_series,yymm,doc_no) using btree in idxdbs3;

create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no)

using btree in datadb3;

Summary

#ids 11.10
Estimated Cost: 1344
Estimated # of Rows Returned: 903

#ids 12.10
Estimated Cost: 2188546
Estimated # of Rows Returned: 250424

Please advice, beside that, How to optimizer index for this table to get better performance. What are different between version ids 11.10 & IDS 12.10?

Thank You

Messages In This Thread

• SQL return high query cost on ids 12.10
  MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 3:59 a.m.
  • Re: SQL return high query cost on ids 12.10
    Spokey Wheeler -- Monday, 16 May 2016, at 4:03 a.m.
    • Re: SQL return high query cost on ids 12.10
      MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 4:21 a.m.
      • Re: SQL return high query cost on ids 12.10
        Art Kagel -- Monday, 16 May 2016, at 10:03 a.m.
        • Re: SQL return high query cost on ids 12.10
          MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 11:12 a.m.
          • Re: SQL return high query cost on ids 12.10
            Art Kagel -- Monday, 16 May 2016, at 11:39 a.m.
            • Re: SQL return high query cost on ids 12.10
              Fernando Nunes -- Monday, 16 May 2016, at 11:46 a.m.
              • Re: SQL return high query cost on ids 12.10
                Art Kagel -- Monday, 16 May 2016, at 12:37 p.m.
  • Re: SQL return high query cost on ids 12.10
    Fernando Nunes -- Monday, 16 May 2016, at 5:34 a.m.
    • Re: SQL return high query cost on ids 12.10
      MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 8:32 a.m.
      • Re: SQL return high query cost on ids 12.10
        Fernando Nunes -- Monday, 16 May 2016, at 9:53 a.m.
        • Re: SQL return high query cost on ids 12.10
          MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 11:24 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.