RE: Stored Procedure performance
Thus, the date is not fixed? It is a value of the same record that you
select. The stored procedure would still be executed for every row that
you select.
David
-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of Colin Dawson
Sent: Friday, October 07, 2005 11:00 AM
To: informix-list@iiug.org
Subject: RE: Stored Procedure performance
The purpose of running the SP within a query is to return the lowest and
highest ID (which is indexed) instead of having a separate index for the
date because an index on the date affects other queries i.e. wrong
choice of
index by the optimiser - see a previous posting on the subject
Regards
Colin
There are 10 types of people in the world, those that understand binary
and
those that don't
>From: "David Reed" <david.reed@tollink.co.za>
>To: "Colin Dawson" <cjd_1955@hotmail.com>,<informix-list@iiug.org>
>Subject: RE: Stored Procedure performance
>Date: Fri, 7 Oct 2005 10:36:38 +0200
>
>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
sending to informix-list