Subject Re: Speed problem with selectable Stored Proc
Author bjorgeitteno
--- In, "Adam" <s3057043@y...> wrote:
> Bjoerge,
> try to simplify your procedure down a bit. You have two queries in
> stored procedure. Which one is (or are both) slow? This is pretty
> 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
and it doesn't change anything noticeably.

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

The LEFT JOIN was my last attempt, actually. I have tried 2 other
solutions - seemingly giving similar problems on speed:
1. Instead of a LEFT JOIN doing a select into the STOCK_VALUE
within the FOR SELECT.. loop
2. leaving the subselect out of the procedure, doing a subselect while
selecting from the procedure.

I am now experimenting with much smaller datasets, starting with 100
records, then 200, 400, etc., to try getting a feel for the "nature"
of the


Bjoerge Saether