--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:

>
> --- In firebird-support@yahoogroups.com, "bjorgeitteno"

<bjorge@i...>

> wrote:
> >
> > It was silly of me to leave this in. I have tried removing the
> first
> > call,
> > and it doesn't change anything noticeably.
>
> Good, getting somewhere. I conclude that this is not the cause of
> your slowness (although with appropriate indices you may be able to
> speed it up), so lets focus on the real problem.
>
> 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
>
> This query is running slow. You can substitute hard coded dates for
> the moment to do some experimenting. If you dont need a left join,
> then just use a join, because this opens up other possibilities for
> the optimiser.

I will have that in mind ...

> Firebird 1.5 sometimes performs better if the
> conditions in the where clause are migrated to the join condition,
> so you could try that.

I coducted some tests, and it seems that the time consumption was
proportional to (STOCK_VALUE_P.Count X
INVOICE_12BOX_PRODUCT.Count)...:

# Records # Records
STOCK_VALUE_P INVOICE_12BOX.. Time (S)
228 120 0,1
2640 1834 1
4740 2991 3
6552 4174 5
11640 7262 14
15312 9156 23
30504 15900 88
92000 42000 701 (est.)

My collegue, mr. S.E.Tysvaer, provided me with a piece of advice:
create an index on STOCK_VALUE_P.PRODUCT - and this did the
trick...only not enough. That is, I created a similar index for
INVOICE_12BOX_PRODUCT.PRODUCT as well - and both of them were used in
the PLAN.
This maneuver got the time down from 12 min. to 40 sec.
It seems I need to rework the table structure, to reduce the number
of records to be summed. Maybe do a "CROSS-TABLE TRICK" and let what
is now 1 record per period be 1 record with 1 field per period - and
do a (P_200407 + P_200408 [...] + P_200509) STOCK_VALUE when
selecting from the procedure. It requires some rewriting of the
application, though. Than I would reduce the number of visited
records by a large amount.

> Possibly your product index is not very selective in one or both of
> the tables, you could force it to ignore the index.

The index is farily selective, I believe - max. 12 records per
PRODUCT value in both tables (in this test-database).

> You are also
> sorting by an aggregate field, so you have no index for the sort.
> (ie, you will have to wait for all the records to be read before

you

> get the first record returned).

I know that, and there's no way around it AFAIK. The worst thing is
that when I select from this procedure, I do another grouping /
summing - now ending up in 0 - 12 rows. These few records are
retrieved and displayed by the client app.

> Others will be able to give you a more informed suggestion over

where

> this problem will be caused from.

Your hints are really valuable to me;
Thank you very much, Adam !

Regards,
Bjoerge Saether