RE: Stored Procedure performance

I would say that your Query is executing the Stored Procedure for every record in the count. I would rather have a select before this one that you can use to load the value into variables. Something like Select pGetIdFromDate('2005-09-01 00:00:00', 'gte'), pGetIdFromDate('2005-09-30 00:00:00', 'le') into l_start_seq, l_end_seq from systables where tabid = 1 In this way the Stored Procedure would only execute once and not on all the records. David -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Colin Dawson Sent: Thursday, October 06, 2005 16:47 PM To: informix-list@iiug.org Subject: Stored Procedure performance IDS 7.31.FD7 Solaris 9 I've got a stored procedure that takes 2 input parameters, a datetime and a relational operator (ge,lte,gte). On it's own it works well - EXECUTE PROCEDURE pGetIdFromDate('2005-10-05 00:00:00', 'gte')returns all id's greater than or equal to the specified date. If I execute the SP as part of a query SELECT COUNT(*) from tableA WHERE rec_id BETWEEN pGetIdFromDate('2005-09-01 00:00:00', 'gte') AND pGetIdFromDate('2005-09-30 00:00:00', 'le')it takes a very long time, the SP uses birnary division to find the start and end ID's for the requested date. Is there a big performance hit when using a stored provedure in this manner? BTW the SP is to avoid adding an index to the table, the table had 200M rows Regards Colin There are 10 types of people in the world, those that understand binary and those that don't sending to informix-list sending to informix-list