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

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

wrote:

> > 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.
>
> 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. Firebird 1.5 sometimes performs better if the
conditions in the where clause are migrated to the join condition, so
you could try that.

Possibly your product index is not very selective in one or both of
the tables, you could force it to ignore the index. 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).

Others will be able to give you a more informed suggestion over where
this problem will be caused from.

Adam