RE: Stored Procedure performance
- From: "Colin Dawson" <cjd_1955@xxxxxxxxxxx>
- Date: Wed, 12 Oct 2005 12:48:24 +0000
For those that were interested in this,
The developer worked out that the SP was being executed for each row in the
table (all 200M of them!!) before I got back to them, it was changed to only
have one parameter of a datetime and would return the first id for that
datetime. It is called within a subquery in the where clause and not called
directly in the list of columns.
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 12:24:53 +0200
>
>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@xxxxxxxx [mailto:owner-informix-list@xxxxxxxx]
>On Behalf Of Colin Dawson
>Sent: Friday, October 07, 2005 11:00 AM
>To: informix-list@xxxxxxxx
>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@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
>sending to informix-list
sending to informix-list
.
- Prev by Date: Re: fragmentation factor
- Next by Date: Re: Heavily used tables
- Previous by thread: RE: Stored Procedure performance
- Next by thread: Can I use ESQL/C datetime type to define non-host variables / parameters
- Index(es):
Relevant Pages
|
