Re: Stored Procedure performance

Colin Dawson said: > > 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 So how does the SP work without accessing an index? What happens when you code the query without the SP? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche "You are an index and a prologue to the history of lust and foul thoughts." - William Shakespeare sending to informix-list