Leyne, Sean wrote:

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

There is a slight difference between these two queries, Sean, if
links.link_id is null, then NOT IN will be null whereas NOT EXISTS will
be true. Hence, if links.link_id can be null, you have to add

and links.link_id is not null

to your solution for these two statements to be identical.

Set