Re: Stored Procedure performance

Posted in 2005

Jose Luis Matute — Fri, 07 Oct 2005 00:11:31 +0200 — source: Usenet: comp.databases.informix

> 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 understand it right: of course! It doesn't access any row... > > 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? > If I understand it right: of course! It accesess 200M rows... > BTW the SP is to avoid adding an index to the table, the table had 200M > rows > Nice try!... but this is not the way. The index will be the fastest way. -- Josi Luis Matute Martmnez Responsable dividisn Sistemas D&D Grupo Dydes Polig. Europolis Edif. Sevilla, Calle T, n: 1, 28230 Las Rozas (Madrid) Tf: +34 91 6407080 Fax: +34 91 6373280 sending to informix-list