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@...]
> 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/
>
>
>
>