Hi.
We are developing a Geo Monitoring System. We use Firebird (Version 1.5.3)
as database.
The System is already productive and has a db-size of approx. 5 Gb, most of
it in one table containing the measurement data.
So far so good.

New evaluations for visualization of the data are under development. We use
the freely available C#-API.
We respect typical performance issues like:
* all tables with primary key
* all elaborative SELECTs with 100% indexed access
* specifying PLANs where the optimizer fails
* intensive use of STORED PROCEDURES in order to minimize Network traffic
Generally we are happy with the performance. But we have one SELECT -
embedded in a STORED PROCEDURE - that is very slow: It has to return -
depending on the parameters - say 60 rows but to read approx. 100.000
datarecords (joined from various tables and grouped into the 60 rows). It
takes 2 to 5 Minutes to return. We think it should be much faster, taking
the Hardware of the development System into account (1 GB RAM, 300 GB HD
SATA, etc.).

We have already tried to optimize the firebird server by
* increasing the page size from 4K to 8K
* trying different values for buffers: from 10000 to 100000 (using gfix as
well as firebird.conf)
* reindexing by backup and restore
* deactivating Hyper-Threading in the BIOS
* spezifying the processormask of the server in order to use only one
processor.

Now our question:
Could you provide us with any tips / help / links to solve the problem?
We did not really feel a difference by using different buffer values. How
can we check if we did it correctly and how much cache is used by the
firebird server.
The productive system runs a super-server on a Windows 2003 Server box.

We would be happy to hearing from you.
Roman


For information, the Stored Procedure:

BEGIN
/* First Epoch in interval [VI_VON, VI_BIS]. */
FOR
SELECT FIRST 1
T303_EPOCHE.C303_EPC_ID
FROM
T303_EPOCHE
WHERE
T303_EPOCHE.C303_BEGINN BETWEEN :VI_VON AND :VI_BIS
ORDER BY
T303_EPOCHE.C303_BEGINN
INTO
:V_ERSTE_EPC
DO
BEGIN
/* SUSPEND;*/
END

/* Last Epoch in interval [VI_VON, VI_BIS]. */
FOR
SELECT FIRST 1
T303_EPOCHE.C303_EPC_ID
FROM
T303_EPOCHE
WHERE
T303_EPOCHE.C303_BEGINN BETWEEN :VI_VON AND :VI_BIS
ORDER BY
T303_EPOCHE.C303_BEGINN DESC
INTO
:V_LETZTE_EPC
DO
BEGIN
/* SUSPEND;*/
END

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) AS
ZS_BEGINN ,
COUNT(DISTINCT T303_EPOCHE.C303_EPC_ID) AS
ZS_ANZ_EPC,
Avg(T301_VEKTOR.C301_K1) AS
AVG_K1 ,
Avg(T301_VEKTOR.C301_K2) AS
AVG_K2 ,
Avg(T301_VEKTOR.C301_K3) AS
AVG_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



intermetric GmbH
Roman Pils
Industriestr. 24
D-70565 Stuttgart

Tel: (07 11) 78 00 39 - 519
Fax: (07 11) 78 00 39 - 7
http://www.intermetric.de

intermetric Gesellschaft für Ingenieurmessung und raumbezogene
Informationssysteme mbH
Sitz: Industriestrasse 24, 70565 Stuttgart - Amtsgericht Stuttgart HRB
3500,
Umsatzsteuer-Ident-Nummer: DE 147 802 365
Geschäftsführer: Dipl.-Math. Ulrich Völter jun., Prof. Dr.-Ing. Ulrich
Völter sen.