Hi Roman!

I take it that it is the last query that runs slow?

Excepting that I don't know the DIV function, that functions like count
and avg could take some time (though not five minutes to count 100000
rows) and that I have no clue about the selectivity of the indexes that
are not primary keys (if not selective, these would be the culprit), I
cannot see why this should be slow. To me, the plan seems good.

Maybe the table name T503_DIAGRAMM_MSS_TEMP could give us some clue?
Does the _TEMP mean that it is a temporary table? Have you deleted lots
of records from it prior to running this query? If so, it could be
garbage collection hitting you. Try to run the same query using isql,
IB_SQL, DB Workbench, IB Expert or whatever and see if it is equally
slow as when run from your program (without deleting lots of records
before running this query, of course).

Also, if this isn't the cause, please tell us the plan suggested by
Firebird if you do not specify any plan.

Sorry for not being of any more help, I have no clue why such a
seemingly good query runs slow.

HTH,
Set

roman.pils@... wrote:

> 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 =3D
> T302_ERGEBNIS.C302_MSS_ID)
> INNER JOIN T405_ERGEBNISVEKTOR
> ON (T302_ERGEBNIS.C302_ERG_ID =3D
> T405_ERGEBNISVEKTOR.C405_ERG_ID)
> AND (T503_DIAGRAMM_MSS_TEMP.C503_VKRT_ID =3D
> T405_ERGEBNISVEKTOR.C405_VKRT_ID)
> INNER JOIN T301_VEKTOR
> ON (T405_ERGEBNISVEKTOR.C405_VKR_ID =3D T301_VEKTOR.C301_VKR_I=
> D)
> INNER JOIN T303_EPOCHE
> ON (T302_ERGEBNIS.C302_EPC_ID =3D T303_EPOCHE.C303_EPC_ID)
> INNER JOIN T201_MESSSTELLE
> ON (T503_DIAGRAMM_MSS_TEMP.C503_MSS_ID =3D
> T201_MESSSTELLE.C201_MSS_ID)
> WHERE
> (T503_DIAGRAMM_MSS_TEMP.C503_DMT_ANFRAGE_ID =3D :VI_DMT_ANFRAGE_I=
> D)
> 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) =3D 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