>I have stored procedure developed under FB 2.1 and it's very, very

slow (more than 10 minutes) under FB 3 because it has NOT IN condition
in WHERE clause:

>not SHIPPING.ORDERNO in (select orderno from invoices)
>I wrote equivalent NOT EXIST statement with the same result:
>NOT EXISTS(SELECT 1 FROM INVOICES where INVOICES.ORDERNO =

SHIPPING.ORDERNO)

>but it's runs for 6min 45sec to get result!

It was wise of you to change to NOT EXISTS, when we switched to Firebird
0.9.4 many years ago, IN <subselect> was one of the first things I
learnt never to use.

>I tried join tables - it runs for couple seconds
>NOT EXISTS(SELECT 1 FROM INVOICES INNER JOIN SHIPPING ON

INVOICES.ORDERNO = SHIPPING.ORDERNO)

>but result is wrong

Of course this gets the wrong result, you've turned it into a static
query with no longer any link to your main query! Hence, the result set
will be empty (well, unless no INVOICES record match any record in
SHIPPING, but that is highly unlikely).

>What can I do? When FB 3 is going to reach the same performance as FB

2.1 with NOT EXIST clause?

Start by showing the table definitions, the complete query with its plan
and some info about the indexes involved. The most obvious explanation
would be if you lack an active index for INVOICES.ORDERNO that is fairly
selective. I'm still on Fb 2.5, but I seriously doubt NOT EXISTS to be a
general problem with Firebird 3.0 (then many more would have complained
at this list).

An alternative way of writing NOT EXISTS, is to write

FROM SHIPPING
LEFT JOIN INVOICES ON SHIPPING.ORDERNO = INVOICES.ORDERNO
WHERE INVOICES.ORDERNO IS NULL

but that shouldn't be any quicker than your NOT EXISTS.

HTH,
Set