Hi Ain!

> > > 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...
>

Well, you could try eliminating using this index in much the same way
as I suggest below.

> > > 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...

Try changing to

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

if Kuupaev or Tooleping is your primary key then it may have a very
positiv effect on your performance.

> > > (TAB_TYYPGRPAEV INDEX (FK_TYYPGR))
> >
> > Maybe OK, depending on selectivity.
>
> Not very good I'm afraid - it's index to enforce FK constraint...

Is it so bad that a NATURAL would be better (i.e. are there only very
few possible values or does most records contain the same value - like
'DELIVERY OK')?

> 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.

Hmm, selecting from a stored procedure within a stored procedure,
well, that ought to be OK, but it is something that I've never done,
so I'll leave answering this bit to the real experts (my 'native
language' is limited to writing reasonably simple SQL and reading
plans, stored procedures are more like Russian - which I only
understand partially).

Set