> Any comments will be of great help, thanks.
>
>
> CREATE PROCEDURE SP_LINKS_SEL_NOTVISITED (
> entity_id_in integer,
> link_v_id_in integer,
> rows_qty integer)
> returns (
> entity_id integer,
> entity_name varchar(128),
> entity_datein timestamp,
> link_id integer,
> link_link varchar(2048))
> as
> BEGIN
> FOR
> select entity_id, entity_name, entity_datein, link_id, link_link
> from entities
> inner join links on (entities.entity_id =

links.link_fk_entity_id)

> where
> (entities.entity_id = :entity_id_in)
> and
> (links.link_id not in (
> select l_v_fk_link_id from links_visited where
> l_v_fk_link_visits_id = :link_v_id_in
> )
> )

> rows :rows_qty

First, this part of the query looks completely out of place...

Try replacing the SELECT with:

select
entity_id, entity_name, entity_datein, link_id, link_link
from entities
join links on (entities.entity_id = links.link_fk_entity_id)
where
(entities.entity_id = :entity_id_in)
and
not exists (
select 1 from links_visited where l_v_fk_link_visits_id =
:link_v_id_in and l_v_fk_link_id = links.link_id
)

A compound index on l_v_fk_link_visits_id and l_v_fk_link_id would be a
great help for performance.


Sean