RE: Stored Procedure performance
- From: "Colin Dawson" <cjd_1955@xxxxxxxxxxx>
- Date: Fri, 07 Oct 2005 09:00:10 +0000
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@xxxxxxxxxxxxx>
>To: "Colin Dawson" <cjd_1955@xxxxxxxxxxx>,<informix-list@xxxxxxxx>
>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@xxxxxxxx [mailto:owner-informix-list@xxxxxxxx]
>On Behalf Of Colin Dawson
>Sent: Thursday, October 06, 2005 16:47 PM
>To: informix-list@xxxxxxxx
>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
.
- Prev by Date: Re: sqlplus for informix
- Next by Date: RE: Stored Procedure performance
- Previous by thread: RE: Stored Procedure performance
- Next by thread: RE: Stored Procedure performance
- Index(es):
Relevant Pages
|
