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