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.