IDS Forum

Re: SQL return high query cost on ids 12.10

Posted By: Spokey Wheeler
Date: Monday, 16 May 2016, at 4:03 a.m.

In Response To: SQL return high query cost on ids 12.10 (MOHD FADZIL JUSOH)

UPDATE STATISTICS?

Also, does it actually run any slower?

> On 16 May 2016, at 08:59, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote:
>
> 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
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

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.