Multi-record returns with Stored Prodedures

I've created a stored prodedure on an Informix 7.1 DB. The procedure returns multiple records. Problem is the time it takes to execute is unacceptable. When I query directly to the DB(through Isql) the records are returned in less than a second, however the SP takes almost 2 minutes. I have a hunch it has something to do with the FOREACH syntax you must use to have an Informix SP return multiple records. Has anybody experienced similar problems? OR can anyone tell me how to better optimize this SP. CREATE PROCEDURE "dbaccess".sp_StxnType (strParent CHAR(8), datBegDate DATE, datEndDate DATE) RETURNING INTEGER; DEFINE p_stxn_code INTEGER; FOREACH SELECT DISTINCT stxn_code INTO p_stxn_code FROM stxns WHERE cl_id = strParent AND txn_code = 'MF' AND txn_type <> 'N' AND txn_date >= datBegDate AND txn_date <= datEndDate RETURN p_stxn_code WITH RESUME; END FOREACH; END PROCEDURE **Note** If I leave out the FOREACH or WITH RESUME statements the procedure only returns the first record. Oh, and the table stxns I'm querying only has about 600,000 records and is indexed by cl_id. Please help! Thanks, cg. Sent via Deja.com http://www.deja.com/ Before you buy.

2 values in the where clause are variable, which are not available at optimisation time (when the procedure is saved). This is not the case when executing through ISQL. Because of that, Informix will optimise differently. Set Explain in the procedure and in ISQL to see the difference in theoptimisation. I am not sure how to force the 7.1 optimiser down a particular path. I guess you can try creating indexes. Alternativelly, 7.3 allows optimiser directives. -- Bashar Chalabi CTL, London