Firebird v2.5 Alpha 1 UPPER() SQL statement resource hog

During my testing of the Firebird Performance I encountered a strange issue with the UPPER() command in SQL statements. Using the UPPER() command my SQL queries suddenly consumed 100% CPU from the server. All transaction times spike from 1-2 seconds to over 10 for the login operation, and the CPU on the DB machine goes to 100% flat instead of jumping between 30% and 90%…

My login check SQL query with the UPPER() command is as follows:
SELECT ID, APASSWORD FROM Accounts
WHERE UPPER(AUsername)=? AND Banned=0 AND Status=0

It seems the UPPER() statement is a real resource hog. This could be the way it’s supposed to work, but I have to change my login statement to not include the UPPER() part and instead save usernames as uppercased in the database.

Advertisements

2 thoughts on “Firebird v2.5 Alpha 1 UPPER() SQL statement resource hog

  1. The column was indexed yes, and I had not taught of creating an expression index.
    I looked at a few examples and came up with this (Tested with FB v2.5 Alpha 1):

    /* Create the index */
    CREATE INDEX I_ACCOUNTS_AUSERNAME ON ACCOUNTS COMPUTED BY (UPPER(AUsername))

    If a SELECT statememnt does not contain UPPER() for the indexed field it seems the query plan also omits the index. If the UPPER() is there the plan includes the index and is much faster!

    Also worth mentioning is a bug in FB 2.0.1 regarding expression indexes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s