At 07:27 PM 17/04/2007, you wrote:

>Now our question:
>Could you provide us with any tips / help / links to solve the problem?

>For information, the Stored Procedure:

If you don't have *both* ascending and descending indexes on
T303_EPOCHE.C303_EPC_ID then add the missing one. If C303_EPC_ID has
low selectivity (a few possible values, or a serious prevalence of
one or two values) then create the two indexes as compounds, with the
primary key column in the rightmost slot[s].

You seem to be doing some unnecessarily costly things here to get
your variables. You don't need SELECT FIRST or ORDER BY. Also,
because you are fetching only one record, you should not use a FOR SELECT loop.
Try this:

BEGIN
/* First Epoch in interval [VI_VON, VI_BIS]. */
SELECT min (C303_EPC_ID)
FROM
T303_EPOCHE
WHERE
C303_BEGINN BETWEEN :VI_VON AND :VI_BIS
INTO
:V_ERSTE_EPC;

/* Last Epoch in interval [VI_VON, VI_BIS]. */
SELECT max (C303_EPC_ID)
FROM
T303_EPOCHE
WHERE
C303_BEGINN BETWEEN :VI_VON AND :VI_BIS
INTO
:V_LETZTE_EPC

OK, next, you do not alias output fields (real or derived) in PSQL
select statements. So, in the latter block, remove all the "AS xxx" clauses.

FOR
SELECT
T503_DIAGRAMM_MSS_TEMP.C503_MSS_ID,
T201_MESSSTELLE.C201_MSS_NAME,
T503_DIAGRAMM_MSS_TEMP.C503_VKRT_ID,
DIV(:V_LETZTE_EPC - T303_EPOCHE.C303_EPC_ID,
:VI_ANZ_EPC_IN_ZS) /* AS ZS_ID */ ,
Min(T303_EPOCHE.C303_BEGINN) ,
COUNT(DISTINCT T303_EPOCHE.C303_EPC_ID),
Avg(T301_VEKTOR.C301_K1),
Avg(T301_VEKTOR.C301_K2),
Avg(T301_VEKTOR.C301_K3)

FROM
T503_DIAGRAMM_MSS_TEMP
INNER JOIN T302_ERGEBNIS
ON (T503_DIAGRAMM_MSS_TEMP.C503_MSS_ID =
T302_ERGEBNIS.C302_MSS_ID)
INNER JOIN T405_ERGEBNISVEKTOR
ON (T302_ERGEBNIS.C302_ERG_ID =
T405_ERGEBNISVEKTOR.C405_ERG_ID)
AND (T503_DIAGRAMM_MSS_TEMP.C503_VKRT_ID =
T405_ERGEBNISVEKTOR.C405_VKRT_ID)
INNER JOIN T301_VEKTOR
ON (T405_ERGEBNISVEKTOR.C405_VKR_ID = T301_VEKTOR.C301_VKR_ID)
INNER JOIN T303_EPOCHE
ON (T302_ERGEBNIS.C302_EPC_ID = T303_EPOCHE.C303_EPC_ID)
INNER JOIN T201_MESSSTELLE
ON (T503_DIAGRAMM_MSS_TEMP.C503_MSS_ID =
T201_MESSSTELLE.C201_MSS_ID)
WHERE
(T503_DIAGRAMM_MSS_TEMP.C503_DMT_ANFRAGE_ID = :VI_DMT_ANFRAGE_ID)
AND

(T303_EPOCHE.C303_EPC_ID BETWEEN :V_ERSTE_EPC AND :V_LETZTE_EPC)
AND (MOD(DIV(:V_LETZTE_EPC - T303_EPOCHE.C303_EPC_ID,
:VI_ANZ_EPC_IN_ZS), :VI_JEDE_NTE_ZS) = 0)
GROUP BY
T503_DIAGRAMM_MSS_TEMP.C503_MSS_ID,
T201_MESSSTELLE.C201_MSS_NAME,
T503_DIAGRAMM_MSS_TEMP.C503_VKRT_ID,
DIV(:V_LETZTE_EPC - T303_EPOCHE.C303_EPC_ID, :VI_ANZ_EPC_IN_ZS)

PLAN JOIN (
T503_DIAGRAMM_MSS_TEMP INDEX (PK_T503_DIAGRAMM_MSS_TEMP),
T303_EPOCHE INDEX (PK_T303_EPOCHE),
T302_ERGEBNIS INDEX (IDX_T302_ERG_EPC_MSS),
T405_ERGEBNISVEKTOR INDEX (PK_T405_ERGEBNISVEKTOR),
T301_VEKTOR INDEX (IDX_T301_VKR_VKR),
T201_MESSSTELLE INDEX (PK_T201_MESSSTELLE)
)
INTO
:VO_MSS_ID ,
:VO_MSS_NAME ,
:VO_VKRT_ID ,
:VO_ZS_ID ,
:VO_ZS_BEGINN ,
:VO_ZS_ANZ_EPC ,
:VO_AVG_K1 ,
:VO_AVG_K2 ,
:VO_AVG_K3
DO
BEGIN
SUSPEND;
END
END

This probably isn't going to be a subsecond execution but it should
not be taking *minutes* to return the row from the first SUSPEND.

I would suggest removing (commenting out) your custom plan. If you
have good indexes then the optimizer's choices will be the best for
the actual data conditions. And take note of Svein's alert about
garbage if there is likelihood of a lot of previous deleting in that
***TMP*** table. Test your plan against the optimizer's plan when
these tables are in similarly dirty (or clean) states.

Also, because you have a very complex grouping here, you probably
need to look at tuning up the available memory for sorts - see the
parameters SortMemBlockSize and SortMemUpperLimit in
firebird.conf. As 1 Gb of RAM is fairly ordinary, don't sacrifice
too much RAM in making your page buffers too big and thus forcing a
lot of this memory-intensive sorting stuff out to disk I/O.

In testing various configurations, don't overlook the need to stop
and restart the server to bring a change into effect. And I hope you
don't have any ".gdb" extensions on database files....

./heLen