> I am still fighting with getting the name index into the plan.
>
> If I do:
> select name from subregion_l where name starting with 'Mur'
>
> it uses the following plan with sub second response:
> PLAN (SUBREGION_L INDEX (SUBREGION_L_IDX1))
>
> Table def is:
> CREATE TABLE SUBREGION_L (
> NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
> etc etc
>
> Now I do the same with a very simplified version of my original stored
> procedure which brought all this up.
>
> select name from subregion_lptest where name starting with 'Mur'
>
> But it uses the following plan and response takes more then a second on
> this small table.
> PLAN (SUBREGION_L NATURAL)
>
> The proc is:
> CREATE OR ALTER PROCEDURE SUBREGION_LPTEST
> returns (
> id keys,
> name varchar(70) collate unicode_ci_ai,
> searchname varchar(30))
> as
> begin
> for select id, name, searchname from subregion_l
> into :id, :name, :searchname
> do
> begin
> suspend;
> end
> end
>
> What am I doing/specifying incorrectly that the name index is not used
> by the stored procedure?
>
> Would very much appreciate any tips on how to get this work.

That's a problem with SPs in general. Using a WHERE clause when calling
a SP won't be able to use an index. You have to put that *into* the SP
whenever possible.

So, if you don't need a (complex) logic for returning a result set,
you'd better use a view, because a view can use an index for a provided
WHERE clause.

> Like to take this opportunity and wish everyone involved with the FB
> project a great holiday season and all the best for the New Year.

To you as well. ;-)



--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/

> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>