Hello,

I'm having problems with the simple "select" stored procedure. If no rows
are returned, the execution of this SP takes more than 30secs, and CPU
performance reaches almost 100%. If any row is returned, the response time
is almost inmediate. All tables participating in this query have respective
indices over primary and foreign keys. Executing the same statement as a
simple query shows the same bad performance.

I'm using Firebird 2, page size 16384.

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
INTO :ENTITY_ID, :ENTITY_NAME, :ENTITY_DATEIN, :LINK_ID, :LINK_LINK
DO SUSPEND;
END