Hi Tanz,


Tanz Anthrox schrieb:

> Hi!,
>
> I have a SP that executes a different SP so many times. Performance is not good enough.
> And, I wish to learn what are the possible (best) practises about this?
>
> I added an example.Let me first explain
> when I run SP_RUNTHIS procedure it calls SP_RUNMANYTIMES(:begindate,:enddate) procedure so many times.
> when I check the SP_RUNTHIS it reads (50K records) but SP_RUNMANYTIMES reads ove (2.3 million records). Note row count in tables are not more that 50K.
>
> I do not want to create a temporary table and store SP_RUNMANYTIMES result in it?
> I think I need to solve this by using PLAN.
>
> So, If you were me, what would you do?

Your problem can't be solved by using a certain plan since you have two
StoredProcs (and there's no plan between procedures).

The first one (SP_RUNTHIS) reads up to 50k records (as you said) with a
further SELECT IN clause.

Then for every returned records it calls the second procedure which has
got to read 2.3 million records, grouping them and returning a result
set (completely!) which afterwards is filtered with the WHERE clause
within the first proc.

This is of course one of the major speed problems.

If you can move your where clause (WHERE SP.MUH_HESAPAD = :HAREKETKODU)
from SP_RUNTHIS directly into SP_RUNMAYNTIMES I bet you'll get quite
some more performance (since the second proc has got to do some
calculation on the "fitting" records and not on all 2.3 millions).

Luc.