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

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.

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