Stored Procedure performance

Posted in 2005

Colin Dawson — Thu, 06 Oct 2005 14:47:17 +0000 — source: Usenet: comp.databases.informix

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