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