>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
