Bjoerge,

try to simplify your procedure down a bit. You have two queries in the
stored procedure. Which one is (or are both) slow? This is pretty easy
to test,

replace the first query with something like

ptotsales = 100;

If your procedure is still slow, then you need to review the second
query. If it is all of a sudden fast, then your first query needs
attention.

Are you unnecessarily tying the hands of the optimiser with the left
join and group by or order by routine, or can you think of a better
plan that should have been used?

Adam



--- In firebird-support@yahoogroups.com, "bjorgeitteno" <bjorge@i...>
wrote:

>
> 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
>