Performance Testing @ the Frontline

A hidden world where small things make a big difference

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

Posted by Kim on Tuesday, July 15, 2008

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 Responses to “Firebird v2.5 Alpha 1 UPPER() SQL statement resource hog”

  1. Adriano said

    Was your AUserName column indexed?
    A normal index can’t be used with functions… But you could create a expression index.

  2. Kim said

    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

 
%d bloggers like this: