At 07:08 PM 23/04/2008, you wrote:

>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.

That is no surprise. But it is "the additional OR" that does the damage, it is all that NOT logic in the WHERE clause.

In any case, this SELECT FIRST logic is far too expensive if you are doing it inside a stored procedure.

Do it like this:

create procedure.... (CC_HASH whatever, CC_HASH_SHA256 whatever)
returns ( CUSTOMER_ID_OUT integer)
as
declare rowcounter smallint = 0;
begin
...
CUSTOMER_ID_OUT = -9999; /* or whatever you want */
if (CC_HASH is not null and CC_HASH <> 0
and CC_HASH_SHA256 is not null and CC_HASH_SHA256 <>'')
THEN
FOR
SELECT CUSTOMER_ID FROM T_CUSTOMERS
WHERE CC_HASH = :CC_HASH
or CC_HASH_SHA256 = :CC_HASH_SHA256
INTO :CUSTOMER_ID_OUT
DO
begin
if (rowcounter > 0) then exit;
else
SUSPEND; /* or whatever you wanted to do with the result */
rowcounter = rowcounter + 1;
end
end

./heLen