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

Hi!

> > > > VIEW_GRAAFIKU_VAHETUS TAB_VAHETUS
> > > > INDEX (RDB$PRIMARY4,FK_VLIIK,FK_VLIIK,FK_VLIIK))
> >
> > 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
>
> 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

Doesn't make any difference...

> > > > (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')?

Actually, now when I look at it more closely, it shouldn't be too bad

CREATE TABLE TAB_Tyypgraafik (
UID DOM_PK PRIMARY KEY,
...);

CREATE TABLE TAB_TyypGrPaev (
UID DOM_PK PRIMARY KEY,
Graafik DOM_FK CONSTRAINT FK_TyypGr REFERENCES
TAB_Tyypgraafik(UID) ON UPDATE CASCADE ON DELETE CASCADE,
...);

and there is usually 1..7 records in TAB_TyypGrPaev per one in
TAB_Tyypgraafik.

Anyway, I think that this is not case of bad index / plan, it looks
like FB will cache some stuff before returning it to the client... this

> 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

looked like good candidate but I replaced that "inner SP" with select
from a table and it didn't make any difference :(


ain