Conditional where clause results in terrible performance Firebird

Question

Firebird does not know how to execute a conditional where. Or that is what I think.

The First query returns values after 15ms.

 SELECT DISTINCT
            A.MANID,
            A.DISNO,
            A.DISID
          FROM
            TABLEB B
            INNER JOIN TABLEA A ON (A.ITEM_ID = B.ITEM_ID)
          WHERE
             (
                (POSITION('%' IN :ISEARCH) = 0 AND B.CATID = :ISEARCH)
             ) 

This second query takes more than 40 seconds and all is about the OR condition.

 SELECT DISTINCT
            A.MANID,
            A.DISNO,
            A.DISID
          FROM
            TABLEB B
            INNER JOIN TABLEA A ON (A.ITEM_ID = B.ITEM_ID)
          WHERE
             (
                (POSITION('%' IN :ISEARCH) = 0 AND B.CATID = :ISEARCH) OR
                POSITION('%' IN :ISEARCH) <> 0
             )

How can I tell firebird to behave in this type of situations?

Answer 1

A bit far-fetched and I'm not familiar with Firebird but for this particular case I'd suggest to try

(
    (POSITION('%' IN :ISEARCH) = 0 AND B.CATID = :ISEARCH) OR
    POSITION('%' IN :ISEARCH) <> 0
)

be written like

(
    (POSITION('%' IN :ISEARCH) <> 0 OR B.CATID = :ISEARCH) 
)

which might make more sense to the query optimizer? It's still an OR and a lot of RDBMSs don't like ORs but it's worth a try...

Worst case you could try to split the statement into 2 separate queries that you UNION ALL together again where one handles POSITION('%' IN :ISEARCH) <> 0 and the other B.CATID = :ISEARCH. The trouble with that approach might be doubled entries which will require filtering out again. (aka: a new can of worms...)