Subject Re: [firebird-support] Re: Does SP use indexes?
Author chand0s
I've done some tests and understood that views use indexes, but views
haven't a functionality like SP. =((

tnx to All...



2006/7/30, Adam <s3057043@...>:
>
> --- In firebird-support@yahoogroups.com, chand0s
> <chand0s.lazy.inbox@...> wrote:
> >
> > I have a test table like this -
> > create table test_table(name varchar(32), id integer);
> > where id field is primary key
> >
> > and I have a simple SP -
> > create procedure test_sp()
> > returns(name varchar(32), id integer)
> > as
> > begin
> > for select name, id from test_table into :name, :id
> > do suspend;
> > end
> >
> > Question -
> > if I will execute my SP with query
> > select * from test_sp() order by id
> >
> > Will SP use indexes?
>
> No, see Dimitry's post for details.
>
> If you want to use an index on ID, you can do it from within the
> stored procedure.
>
> eg:
>
> create procedure test_sp()
> returns(name varchar(32), id integer)
> as
> begin
> for select name, id from test_table order by ID into :name, :id
> do suspend;
> end
>
> combined with:
>
> select * from test_sp()
>
> Will return the records ordered by ID possibly using the index on ID
> if the optimiser thinks that is beneficial.
>
> Just a note about sorting on indexed fields. It is often slower to
> use an index to sort a recordset than to read the records in the
> order they occur on disk and sort in memory. Sorting using an index
> is extremely beneficial when you are selecting the first n.
>
> Adam
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>
>


--
respect by chand0s...


[Non-text portions of this message have been removed]