I tried it back and forth several times. Didn't make any difference

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
wrote:

> did you then run it by running the SP then the subselect query? i.e.

change

> the order - did it make a difference?
> Interesting
> Alan
>
> > -----Original Message-----
> > From: Tom Frey [mailto:tom@g...]
> > Sent: Tuesday, 11 November 2003 6:18 PM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: SubSelect Question
> >
> >
> > Your wish shall be fulfilled:
> >
> > I ran this Query:
> >
> > SELECT (SELECT avg(volume) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT
> > avg(price) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT avg(volume)
> > from ABNTRADEDATA WHERE VOLUME <0) FROM RDB$DATABASE;
> >
> > versus this stored procedure:
> >
> > CREATE PROCEDURE GET_STATS
> > RETURNS (
> > AVG_VOLUMEPOS INTEGER,
> > AVG_VOLUMENEG INTEGER,
> > AVG_PRICE DECIMAL (10, 3))
> > AS
> > BEGIN
> > SELECT AVG(VOLUME), AVG(PRICE) FROM ABNTRADEDATA WHERE VOLUME > 0 INTO
> > :AVG_VOLUMEPOS, :AVG_PRICE;
> > SELECT AVG(VOLUME) FROM ABNTRADEDATA WHERE VOLUME < 0 INTO

:AVG_VOLUMENEG;

> > SUSPEND;
> >
> > END
> >
> > Result is that the storec procedure is almost twice as fast.
> >
> > First Log is from the Query, 2nd one from the stored procedure
> >
> > Test was run on Windows 2003, P4 2.8GHz, FirebirdSql 1.5RC7
> > Database contains 20,283 rows
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 172
> > Avg fetch time: 172.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,099,248
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 7,313
> > Writes : 4
> > Fetches: 136,397
> >
> > Plan:
> > ------------------------------------------------
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (ABNTRADEDATA NATURAL)
> > PLAN (RDB$DATABASE NATURAL)
> >
> > Enchanced Info:
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | Table Name | Index | Non-Index | Updated | Deleted |
> > Inserted |
> > | | reads | reads | | |
> > |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | RDB$FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_FIELDS| 13 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATIONS| 2 | 0 | 0 | 0 |
> > 0 |
> > | RDB$RELATION_CONSTRAINTS| 1 | 0 | 0 | 0 |
> > 0 |
> > | RDB$DATABASE| 0 | 1 | 0 | 0 |
> > 0 |
> > | ABNTRADEDATA| 0 | 60,849 | 0 | 0 |
> > 0 |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> >
> >
> >
> >
> >
> > Query Time
> > ------------------------------------------------
> > Prepare : 15
> > Execute : 94
> > Avg fetch time: 94.00 ms
> >
> > Memory
> > ------------------------------------------------
> > Current: 1,065,624
> > Max : 1,196,224
> > Buffers: 2,048
> >
> > Operations
> > ------------------------------------------------
> > Read : 4,865
> > Writes : 4
> > Fetches: 90,887
> >
> > Plan:
> > ------------------------------------------------
> >
> > Enchanced Info:
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | Table Name | Index | Non-Index | Updated | Deleted |
> > Inserted |
> > | | reads | reads | | |
> > |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> > | RDB$PROCEDURE_PARAMETERS| 3 | 0 | 0 | 0 |
> > 0 |
> > | ABNTRADEDATA| 0 | 40,566 | 0 | 0 |
> > 0 |
> > +--------------------------+-------+-----------+---------+--------
> > -+----------+
> >
> > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> > wrote:
> > > > A stored procedure.
> > > >
> > > > create procedure GetAverages
> > > > returns (
> > > > AvPriceForPos numeric(18,2),
> > > > AvVolPos numeric (18,2),
> > > > AvVolNeg numeric (18,2)
> > > > )
> > > > as
> > > > begin
> > > > select
> > > > avg(volume), avg(price) from abntradedata
> > > > where volume > 0
> > > > into :AvVolPos, :AvPriceForPos;
> > > > select avg(volume) from abntradedata
> > > > where volume < 0
> > > > into :AvVolNeg
> > > > end
> > > >
> > > > That's two queries but no subqueries to slow you down.
> > > >
> > >
> > > I wouldn't mind seeing a test result of this - I doubt that a
> > subquery would
> > > be any slower at all over this SP method
> > >
> > > Alan
> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
> >
> >
> > Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/

> >
> >
> >
> >