Firebird query taking too long when using IN clause with Stored Procedure

For a report I had to write a recursive Stored Procedure GET_RECIPE_STEPS_ID(recipe_id). It returns ids of steps which are of type recipe. I.E.

SELECT GET_RECIPE_STEPS_ID.ID FROM GET_RECIPE_STEPS_ID(3189)

It Returns
3189
3190
3191
3192

When I run it on it’s own It’s quick (like 0.031sec execution time). But if it is to be used with IN clause in a query it takes ages. Like the following query took almost 12 minutes.

SELECT rs.RECIPEID
FROM RECIPESTEPS rs
WHERE rs.RECIPEID IN (select GET_RECIPE_STEPS_ID.ID from GET_RECIPE_STEPS_ID(3189))

Which is equivalent to the following query, and almost as quick as the stored procedure itself (0.038sec)

Select rs.RECIPEID
FROM RECIPESTEPS rs
WHERE rs.RECIPEID IN (3189, 3190, 3191, 3192)

Stored Procedure

CREATE OR ALTER PROCEDURE GET_RECIPE_STEPS_ID 
 (recipe_id integer) 
RETURNS 
 (id integer)
AS 
declare variable coType integer;
BEGIN
  /* Recursive Procedure 
   * For Passed Recipe 'Recipe_id', it Returns the step's which are of type Recipe again.
   * 
   * If any step is of type Recipe(i.e COTYPE = 1)
   * Then it calls itself again for that step(Recipe) 
   */
    id =: recipe_id;
    SUSPEND;

    FOR SELECT rs.COMMODITYID, c.COTYPE
        FROM RECIPESTEPS rs 
        LEFT JOIN COMMODITIES c ON c.COMMODITYID = rs.COMMODITYID
        WHERE rs.RECIPEID  =: recipe_id INTO :id, :coType
    Do
    BEGIN
        IF(coType = 1)
        THEN 
            FOR SELECT r.RECIPEID FROM RECIPES r WHERE r.LATEST = 1 AND r.COMMODITYID =:id into :id
            DO
            BEGIN
                FOR SELECT GET_RECIPE_STEPS_ID.ID
                    FROM GET_RECIPE_STEPS_ID(:id) INTO :id
                DO
                BEGIN
                    SUSPEND;
                END
            END     
    END 
END^

Answer

The problem is two-fold:

1. IN does not have very good performance to begin with and
2. In this situation the stored procedure gets executed for each row and not once like you would expect; I guess the Firebird optimizer doesn’t infer that this stored procedure invocation is not correlated with the query.

It will probably perform better if you transform your query to use an INNER JOIN instead of IN:

select rs.RECIPEID
from GET_RECIPE_STEPS_ID(3189) grs
inner join RECIPESTEPS rs
  on rs.RECIPEID = grs.ID

I assume that your real query might be more complex, because otherwise just select ID from GET_RECIPE_STEPS_ID(3189) would suffice.

The above query will behave slightly different than IN, for example if an ID occurs multiple times in the stored procedure output, it will also produce multiple rows now. You may need to adjust accordingly.