Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Hyderabad, India
    Posts
    7

    Question Unanswered: Informix Performance

    I have some problem with Informix Server performance.The Explain result of my Procedure is given below and its total estimated cost is 17.
    but to execute this procdure it takes around 2 min. one of my table involved in query is having 130,000 records and the remaining two are very small copared to the other one.(around 200 records and 5 records)

    please suggest me any tip to decrease the response time and improve the performance of my query.

    Procedure: informix.commaseparatedvalues
    insert into "informix".commatotable (institutionids) values (? )
    QUERY:
    ------
    Estimated Cost: 1
    Estimated # of Rows Returned: 1
    ----------
    Procedure: informix.onusnotonuspercentage
    select count(*) from "swx".log_record x0 ,"swx".device x1 ,"informix".commatotable x2 where (((((x0.chp_inst
    _nbr > 0. ) AND (x0.type_code > 10. ) ) AND (x1.institution = x2.institutionids ) ) AND (x1.device_id = x0.d
    evice_id ) ) AND ((x0.tran_datetime >= TO_DATE (? ,'%Y-%m-%d %H:%M:%S' )) AND (x0.tran_datetime <= TO_DATE (
    ? ,'%Y-%m-%d %H:%M:%S' )) ) )
    QUERY:
    ------
    Estimated Cost: 7
    Estimated # of Rows Returned: 1
    1) informix.l: INDEX PATH
    Filters: (informix.l.chp_inst_nbr > 0 AND informix.l.type_code > 10 )
    (1) Index Keys: tran_datetime (desc) (Serial, fragments: ALL)
    Lower Index Filter: informix.l.tran_datetime <= TO_DATE ('2002-12-31 23:59:59' , '%Y-%m-%d %H:%M:%S'
    )
    Upper Index Filter: informix.l.tran_datetime >= TO_DATE ('2002-01-01 00:00:00' , '%Y-%m-%d %H:%M:%S'
    )
    2) informix.d: INDEX PATH
    (1) Index Keys: device_id (Serial, fragments: ALL)
    Lower Index Filter: informix.d.device_id = informix.l.device_id
    NESTED LOOP JOIN
    3) informix.c: SEQUENTIAL SCAN
    DYNAMIC HASH JOIN
    Dynamic Hash Filters: informix.d.institution = informix.c.institutionids
    ----------
    Procedure: informix.onusnotonuspercentage
    select count(*) from "swx".log_record x0 ,"swx".device x1 ,"informix".commatotable x2 where ((((((x0.chp_ins
    t_nbr = 0. ) AND (x0.type_code > 10. ) ) AND (x1.institution = x2.institutionids ) ) AND (x1.device_type = 1
    . ) ) AND (x1.device_id = x0.device_id ) ) AND ((x0.tran_datetime >= TO_DATE (? ,'%Y-%m-%d %H:%M:%S' )) AND
    (x0.tran_datetime <= TO_DATE (? ,'%Y-%m-%d %H:%M:%S' )) ) )
    QUERY:
    ------
    Estimated Cost: 7
    Estimated # of Rows Returned: 1
    1) informix.l: INDEX PATH
    Filters: (informix.l.chp_inst_nbr = 0 AND informix.l.type_code > 10 )
    (1) Index Keys: tran_datetime (desc) (Serial, fragments: ALL)
    Lower Index Filter: informix.l.tran_datetime <= TO_DATE ('2002-12-31 23:59:59' , '%Y-%m-%d %H:%M:%S'
    )
    Upper Index Filter: informix.l.tran_datetime >= TO_DATE ('2002-01-01 00:00:00' , '%Y-%m-%d %H:%M:%S'
    )
    2) informix.d: INDEX PATH
    (1) Index Keys: device_type device_id (Serial, fragments: ALL)
    Lower Index Filter: (informix.d.device_id = informix.l.device_id AND informix.d.device_type = 1 )
    NESTED LOOP JOIN
    3) informix.c: SEQUENTIAL SCAN
    DYNAMIC HASH JOIN
    Dynamic Hash Filters: informix.d.institution = informix.c.institutionids
    ----------
    Procedure: informix.onusnotonuspercentage
    select round(((x0.count1 / (x0.count1 + x1.count2 ) ) * 100.0000000000000000 ) , 2 ) ,round(((x1.count2 / (x
    0.count1 + x1.count2 ) ) * 100.0000000000000000 ) , 2 ) from "informix".a x0 ,"informix".b x1
    QUERY:
    ------
    Estimated Cost: 2
    Estimated # of Rows Returned: 1
    1) informix.b: SEQUENTIAL SCAN
    2) informix.a: SEQUENTIAL SCAN
    NESTED LOOP JOIN

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Do you have an index on the columns institution and institutionids ?

  3. #3
    Join Date
    Nov 2002
    Location
    Hyderabad, India
    Posts
    7
    Originally posted by rnealejr
    Do you have an index on the columns institution and institutionids ?
    thanks for your reply
    I have index on institution but institutionids is comming from a temporary table and it will have only 5 to 10 records. and its cost is very less( around 2) will this be optimum or will we get any more performance.

  4. #4
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    So what is your big table here?
    Did you update statistics correctly?
    What the setting of your
    OPTCOMPIND and OPT_GOAL in your $ONCONFIG?
    rws

  5. #5
    Join Date
    Nov 2002
    Location
    Hyderabad, India
    Posts
    7
    Originally posted by Roelwe
    So what is your big table here?
    Did you update statistics correctly?
    What the setting of your
    OPTCOMPIND and OPT_GOAL in your $ONCONFIG?
    the large table there is Log_record

    optcompind is 2
    opt_goal is -1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •