Greetings All,

In the Firebird 2 Migration & Installation guide
http://www.firebirdsql.org/file/documentation/release_notes/Firebird-2_1_6-Installation.pdf
on page 8 under the performance section it states the following:

The following changes should be noted as possible sources of performance loss:

Existence Predicates NOT IN and ALL May Be Slow

Firebird and, before that, InterBase, have produced incorrect results for the logical existence predicates ALL and NOT IN for many years. That problem has been corrected in Firebird 2.0, but the change means that indexes on the inner tables cannot be used and performance may be slow compared to the same query's performance in V.1.5. “Inner tables” are the tables used in the subquery argument inside an ALL or NOT IN expression.

So my questions:

This only applies when there is a JOIN in the SQL statement correct?

In other words it does not apply to a stored procedure like the following does it?

SET TERM  ^^ ;

CREATE PROCEDURE SPS_ATTORNEY_STATE (

  V_ATTORNEY_ID Integer)

returns (

  R_ASSOCIATED SmallInt,

  R_ATTORNEY_ID Integer,

  R_STATE_CODE Char(2),

  R_STATE_NAME VarChar(35),

  R_CREATE_DATE TimeStamp,

  R_CREATE_USER SmallInt,

  R_MODIFY_DATE TimeStamp,

  R_MODIFY_USER SmallInt,

  R_COURT_NO VarChar(10))

AS

/*

  Author   : Michael Tuttle

  Date     : 10/02/2006

  Purpose  :

*/

begin

  FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,

             A.ATTORNEY_ID,

             A.STATE_CODE,

             S.NAME AS STATE_NAME,

             A.CREATE_DATE,

             A.CREATE_USER,

             A.MODIFY_DATE,

             A.MODIFY_USER,

             A.COURT_NO

        FROM ATTORNEY_STATE A

        JOIN STATE S ON S.STATE_CODE = A.STATE_CODE

       WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID

    UNION

      SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,

             CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,

             S.STATE_CODE,

             S.NAME AS STATE_NAME,

             CAST(NULL AS TIMESTAMP) AS CREATE_DATE,

             CAST(NULL AS SMALLINT) AS CREATE_USER,

             CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,

             CAST(NULL AS SMALLINT) AS MODIFY_USER,

             CAST(NULL AS VARCHAR(10)) AS COURT_NO

        FROM STATE S

       WHERE S.STATE_CODE NOT IN (SELECT A2.STATE_CODE

                                    FROM ATTORNEY_STATE A2

                                   WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID)

      INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME, :R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER, :R_COURT_NO DO

    BEGIN

      SUSPEND;

    END

end

^^

SET TERM ;  ^^

Thanks,

Mike