--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

Sorry about not responding, I was away few days...

> Hmm, limited information, but I'll tear your plan apart and comment
> within:

> > VIEW_GRAAFIKU_VAHETUS TAB_GRAAFIK INDEX
> > (FK_TLEPING_G,CU_GRAAFIK_KUUPLEPVAH),
>
> Hmm, this could be OK, depending on the selectivity of the two indexes.

CU_GRAAFIK_KUUPLEPVAH is unique but I quess that FK_TLEPING_G isn't
that good - it is FK constraint and there is lot of dublicates in
TAB_GRAAFIK. And it will get worse over time as new recors will be
inserted...

> > VIEW_GRAAFIKU_VAHETUS TAB_VAHETUS
> > INDEX (RDB$PRIMARY4,FK_VLIIK,FK_VLIIK,FK_VLIIK))
>
> Hmm, this seems bad. Why use FK_VLIIK thrice when you already are
> using hte primary key? Add some +0 or ||'' to the fields to get rid of
> these foreign keys.

I guess this part of the plan is caused by following statement in the SP:

IF(EXISTS(SELECT 1 FROM VIEW_Graafiku_Vahetus WHERE(Tooleping =
:Tooleping)AND(Kuupaev >= :AlgD)AND(Kuupaev <= :LopD)AND(Liik
IN(1,7,8))))THEN

Field Liik is FK so there is index for it. But I'm afraid that this
index is pretty bad selectivity wise...

> > (TAB_TYYPGRPAEV INDEX (FK_TYYPGR))
>
> Maybe OK, depending on selectivity.

Not very good I'm afraid - it's index to enforce FK constraint...

> That's about as much as I can help you given the information you
> provided. I don't think it is slow prepare that is the cause of your
> problem (I've hardly heard about that being a problem with Firebird
> before - except for a few persons looping through a set repreparing
> for every execution), just that it takes a long time for Firebird to
> find the first record (are you by any chance using ORDER BY?).

No ORDER BY.

Actually, the SP does some pretty complex stuff and I expect it to be
slow, but the delay should be between each record, not in the
beginning. The main structure of the SP is

CREATE PROCEDURE JatkaTyypgraafikuid(aReziim INTEGER, aYksus INTEGER,
aAmet INTEGER, aTeam INTEGER, aProjekt INTEGER,
AlgD DATE, LopD DATE, ExGrAct INTEGER)
RETURNS(Tooleping INTEGER, TehtiGr INTEGER)
AS
BEGIN
FOR SELECT ToolepID, Alates, Kuni FROM ListToolepingud(:AlgD, :LopD,
:aYksus, :aAmet, :aTeam, :aProjekt)
INTO :Tooleping, :LepAlg, :LepLop DO BEGIN
/* do some stuff here */
SUSPEND;
END
END^

So when I execute query

SELECT Tooleping, TehtiGr FROM
JatkaTyypgraafikuid(NULL,NULL,NULL,NULL,NULL,'2005-11-01',
'2005-11-30', 3);

I expect that records will appear one by one with some delay between
them, but instead I get huge delay in the beginning and then all the
records appear instantly.


ain