Subject Re: [ib-support] PLAN and stored procedure
Author Svein Erling Tysvær
Hi Nils,

>Select Distinct
> Verzeichnislinks.AID
> >From Verzeichnislinks
> (Verzeichnislinks.VID in (Select VID From CreateVidListe(12002)))
>(Stored procedure CreateVidListe(12002))

I think your subselect is executed once for every row in Verzeichnislinks.
hence no index can be useful. Try rewriting to something like

SELECT DISTINCT Verzeichnislinks.AID
FROM Verzeichnislinks

or (possibly better)

SELECT DISTINCT Verzeichnislinks.AID
FROM Verzeichnislinks
JOIN CreateVidListe(12002) CVL ON CVL.VID = Verzeichnislinks.VID

Note that I don't use stored procedures very much and that there may be
some mistakes in my code.

>* Have IB problems with Stored Procedures used in a select statement and
>the query optimizer?

No, the problem is with the subselect in your IN clause.

>* What must I do if I want that Query using a stored produre to fill the
>IN statemend also use a index for the Verzeichnislinks table?

I don't think this is possible - in theory it is possible for the subselect
to depend upon a value in your table (e.g. select Distinct
Verzeichnislinks.AID From Verzeichnislinks Where (Verzeichnislinks.VID in
(Select VID From CreateVidListe(Verzeichnislinks.AID)))), so it is a bit
more dynamic than what you need in your case. I think it would be desirable
if IB/FB was able to detect whether the result of a subselect was depending
upon the table being selected from, but currently I simply don't think it
can do that (it is not enough just to examine the subselect, the subselect
may have further subselects etc. so it may be more difficult to implement
than those of us not knowing the code believes).