IDS Forum

RE: IDS 12.1- Query cost High

Posted By: Everett Mills
Date: Thursday, 7 April 2016, at 11:19 a.m.

In Response To: Re: IDS 12.1- Query cost High (MOHD FADZIL JUSOH)

When you did the first update statistics, was it with the drop distributions option? I'm pretty sure that you need to do that after an in place upgrade.

--EEM

> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> MOHD FADZIL JUSOH
> Sent: Thursday, April 07, 2016 10:08 AM
> To: ids@iiug.org
> Subject: Re: IDS 12.1- Query cost High [36923]
>
> Hi,
>
> That indicates that the data distributions in the v12.10 are way off,
> What do you means "way off"? need to change configuration to on this?
>
> Questions:
>
> 1.Was this an in-place upgrade or a new instance? I suspect a new
>
> instance. If so then how did you load the data?
>
> - I do in-place migration. At new server, i install ids version 11.10
> first and than restore data using ontape command. After complete
> restored, i install ids 12.10 and bring up the instance using ids
> 12.10. After that before test application, we run updates statistics
> low, medium & high for all table.
>
> Any step i miss during migration?
>
> 2.. Is there a difference in the partitioning of the table in the two
>
> instances? - all table at same instance
>
> 3. Is the storage used by both instances comparable (ie both the same
>
> RAID level, same number of spindles or types of drives, same stripe
> block
>
> size, etc.)?
>
> A. ids 12.10 using better disk/storage compare with ids 11.10
>
> 4. If the upgrade was in-place did you drop all data distributions then
>
> recreate them from scratch after the upgrade?
> - Yes, we do in-place upgrade, how to drop all data distributions and
> recreate again? We need to do this?
>
> I try to point back my problem, same query return higher query cost on
> ids 12.10, if u see my first posting, on ids 11.10 this query
> successfully running using index patch, but on ids 12.10 it running
> using sequence scan, why it return different result, any wrong with my
> exiting index? Any parameter config on ids 12.10 need to enable to
> allow syntax using index.
>
> Here i share my table structure :
>
> $ dbschema -db life -t acc_pay
>
> 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
> );
>
> 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 ;
> create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd)
>
> using btree ;
> create index "informix".i2_acc_pay on "informix".acc_pay (surr_key)
>
> using btree ;
> create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ,
>
> bas_doc_no,pay_typ,pay_dt) using btree ; create index "stjf".i4_acc_pay
> on "informix".acc_pay (trn_cd,doc_series,
>
> yymm,doc_no) using btree ;
> create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ,
>
> pay_typ,pay_chg_typ,bas_doc_no) using btree ; 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 ; create index
> "stjf".tmp_acc on "informix".acc_pay (bas_doc_no)
>
> using btree ;
>
> And my syntax as below:
>
> select ref_no, pay_dt, surr_key, trn_cd, doc_series, yymm, doc_no,
> bas_doc_no, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid 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
>
> On ids 11.10, look i success run using index path as below:
>
> 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 != ' ' )
>
> But when we run it at ids 12.10 after i drop & recreate again index, it
> give us that syntax not running using index path , but running using
> sequence scan as below:
>
> 1) informix.acc_pay: SEQUENTIAL SCAN
>
> Filters: (((((informix.acc_pay.ref_no = 'T00001' AND
> 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.ref_typ = 'AGT' ) AND
> informix.acc_pay.trn_cd != ' ' ) AND informix.acc_pay.trn_cd IS NOT
> NULL ) AND informix.acc_pay.pay_dt <= 31/03/
> 2016 )
>
> What happen to current index? Based on my syntax, can u advice the
> right index we should created for this table?
>
> Thank You
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• IDS 12.1- Query cost High
  MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 4:52 a.m.
  • Re: IDS 12.1- Query cost High
    Art Kagel -- Thursday, 7 April 2016, at 6:40 a.m.
    • Re: IDS 12.1- Query cost High
      MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 7:04 a.m.
      • Re: IDS 12.1- Query cost High
        Fernando Nunes -- Thursday, 7 April 2016, at 7:32 a.m.
      • Re: IDS 12.1- Query cost High
        Art Kagel -- Thursday, 7 April 2016, at 7:58 a.m.
        • Re: IDS 12.1- Query cost High
          MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 11:07 a.m.
          • RE: IDS 12.1- Query cost High
            Everett Mills -- Thursday, 7 April 2016, at 11:19 a.m.
          • Re: IDS 12.1- Query cost High
            Art Kagel -- Thursday, 7 April 2016, at 11:42 a.m.
            • Re: IDS 12.1- Query cost High
              MOHD FADZIL JUSOH -- Friday, 8 April 2016, at 11:19 a.m.
              • Re: IDS 12.1- Query cost High
                Fernando Nunes -- Friday, 8 April 2016, at 11:30 a.m.
                • RE: IDS 12.1- Query cost High
                  Everett Mills -- Friday, 8 April 2016, at 1:32 p.m.
          • Re: IDS 12.1- Query cost High
            Madison Pruet -- Thursday, 7 April 2016, at 8:10 p.m.
      • Re: IDS 12.1- Query cost High
        Spokey Wheeler -- Thursday, 7 April 2016, at 8:04 a.m.
        • Re: IDS 12.1- Query cost High
          Fernando Nunes -- Thursday, 7 April 2016, at 10:12 a.m.
      • RE: IDS 12.1- Query cost High
        Mike Walker -- Thursday, 7 April 2016, at 8:58 a.m.
        • Re: RE: IDS 12.1- Query cost High
          MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 10:46 a.m.
          • Re: IDS 12.1- Query cost High
            Spokey Wheeler -- Thursday, 7 April 2016, at 10:54 a.m.
          • Re: RE: IDS 12.1- Query cost High
            Fernando Nunes -- Thursday, 7 April 2016, at 11:13 a.m.
          • RE: RE: IDS 12.1- Query cost High
            Mike Walker -- Thursday, 7 April 2016, at 11:18 a.m.
  • Re: IDS 12.1- Query cost High
    MARK JALKIEWICZ -- Friday, 8 April 2016, at 10:19 a.m.
    • Re: IDS 12.1- Query cost High
      Fernando Nunes -- Friday, 8 April 2016, at 10:54 a.m.
      • Re: IDS 12.1- Query cost High
        MOHD FADZIL JUSOH -- Friday, 8 April 2016, at 11:12 a.m.
        • Re: IDS 12.1- Query cost High
          MARK JALKIEWICZ -- Friday, 8 April 2016, at 11:55 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.