Hi, all !

I'm struggeling with a severe speed problem with a selectable stored
proc. It has been tested with other computers as well, my server is a
standard 2GHz/256MB/Dual IDE disks running FB 1.0.3 or 1.5 (tested
both)

The two tables involved are declared as follows:

CREATE TABLE INVOICE_12BOX_PRODUCT (
CONTRIB FLOAT NOT NULL
, DB FLOAT NOT NULL
, PERIOD INTEGER NOT NULL
, QUANTUM FLOAT NOT NULL
, LINES FLOAT NOT NULL
, PRICE_DIFF FLOAT NOT NULL
, COST_TRANSP FLOAT NOT NULL
, RESULT FLOAT NOT NULL
, SALES FLOAT NOT NULL
, PRODUCT INTEGER NOT NULL
, QVANTUM_BUDGET FLOAT NOT NULL
, CONSTRAINT INVOICE_12BOX_PRODUCT_PX
PRIMARY KEY (PERIOD, PRODUCT )
)
...containing 42183 records

and:

CREATE TABLE STOCK_VALUE_SELECT_P (
PERIOD INTEGER NOT NULL
, PRODUCT INTEGER NOT NULL
, STOCK_VALUE FLOAT
, QUANTUM FLOAT
, CONSTRAINT STOCK_VALUE_SELECT_P_PK
PRIMARY KEY ( PERIOD,PRODUCT )
)

...containing 92856 records.

Now, the troublesome procedure:

CREATE PROCEDURE L1_PRODUCT_SA(PERIOD_FROM INTEGER
, PERIOD_TO INTEGER )
RETURNS ( BOX_NO INTEGER
, SALES FLOAT
, PRODUCT INTEGER
, STOCK_VALUE FLOAT )
AS

DECLARE VARIABLE pTotSales Float;

BEGIN

SELECT
SUM(SALES)
FROM
INVOICE_12BOX_PRODUCT
WHERE
PERIOD BETWEEN :PERIOD_FROM
AND :PERIOD_TO
into
pTotSales;

FOR SELECT
I.PRODUCT,
SUM(I.SALES),
SUM(S.STOCK_VALUE)
FROM
INVOICE_12BOX_PRODUCT I
LEFT JOIN
STOCK_VALUE_SELECT_P S ON (S.PERIOD BETWEEN :PERIOD_FROM
AND :PERIOD_TO
AND S.PRODUCT = I.PRODUCT)
WHERE
I.PERIOD BETWEEN :PERIOD_FROM AND :PERIOD_TO
GROUP BY
I.PRODUCT
ORDER BY
2 DESC
into :PRODUCT,
:SALES,
:STOCK_VALUE
do
BEGIN
BOX_NO=1;
suspend;
END
END

And, to test the procedure:

select
BOX_NO,
SUM(SALES) SALES,
SUM(STOCK_VALUE) STOCK_VALUE,
COUNT(*) NUM_REC
from
L1_PRODUCT_SA(200407, 200509) f
group by
BOX_NO

(the procedure has been reduced beyond recognition, hence some
mysterious
fields lik BOX_NO. They obviously don't make much difference, as I
experienced exactly the same problems when the procedure
was "complete".

I restarted the service after 13 minutes og fbserver running at 99%.
Two things I noticed:
1) The server quickly spent 25MB, produced some 11,000 page faults -
and remained at those figures. Hardly any page faults at all after a
few seconds. This indicates it's all processed in "real" RAM, doesn't
it ?
2) Most of the time the preparation alone would take 15 seconds. Why ?

My question is: Why does this procedure run so very, very slow ? Here
is the plan presented by IB_SQL:
PLAN SORT ((INVOICE_12BOX_PRODUCT INDEX (INVOICE_12BOX_PRODUCT_PX))
SORT (SORT (JOIN (I INDEX (INVOICE_12BOX_PRODUCT_PX),S INDEX
(STOCK_VALUE_SELECT_P_PK)))))

Any input is welcome !
--
Regards,
Bjoerge Saether