Hi

We are running an embedded Firebird on several hundred old PCs and have just run into a strange slowdown that probably has very simple explanation.

In a stored procedure I have this line:
SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE
(CC_HASH = :CC_HASH and :CC_HASH is not null and :CC_HASH != 0) OR
(CC_HASH_SHA256 = :CC_HASH_SHA256 and :CC_HASH_SHA256 is not null and not
:CC_HASH_SHA256 = '')
into :CUSTOMER_ID_OUT;


For the purpose of performance analysis it can be shortened to:
SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE
(CC_HASH = :CC_HASH OR CC_HASH_SHA256 = :CC_HASH_SHA256)
into :CUSTOMER_ID_OUT;

Before the change it didn't have the "OR" so it looked like this:

SELECT FIRST 1 CUSTOMER_ID FROM T_CUSTOMERS WHERE
(CC_HASH = :CC_HASH) into :CUSTOMER_ID_OUT;

This version is super fast but with the additional OR in the where it is about 50 times slower.


T_CUSTOMERS contains 700000 rows
Both CC_HASH and CC_HASH_SHA256 have separate indexes.

Currently CC_HASH_SHA256 contains almost 100% nulls and only 50 or so actual values. With the index I assumed there would add almost no performance penalty (especially now when we have no data in that extra column)

CC_HASH_SHA256 is a char(44) column and CC_HASH is an integer column so it could be a touch slower because of that but not on this level.

The index looks like this
CREATE INDEX T_CUSTOMERS_IDX2
ON T_CUSTOMERS (CC_HASH_SHA256)

It did not help to recomputed selectivity.

I am running an embedded Firebird 1.5, any pointers appreciated.

Regards

Tim