Folks who first learned to type on typewriters tend to prefer putting two spaces after a period.  Most of the rest of us prefer just one. And this may have caused a performance problem.

Rob's application had a quick search feature to track down customer claims. One day, the quick search was running quickly and efficiently. A user could type in a claim number, hit enter, and a moment later their screen would show the claim. Suddenly, it slowed down. It wasn't just the gradual decline of growing data or stale statistics or bad indexes. It was a code change, and it didn't take long to find the problem:

select claimid, masterclaimid, claimdata, claimtype from claim where upper(REGEXP_REPLACE(claimnum,'( ){2,}',' ')) = upper(:1 ) and claimtype in (0,1,2)

Apparently, someone was typing two (or more) spaces someplace in the stored claim number. Rob has no information about why that was happening, and it's unlikely that some old-school typist was forcing extra spaces. I prefer to think that somebody in management had spilled a cocktail on their keyboard and now the spacebar was sticky, and spammed five or six spaces every time you pressed it once. Instead of getting their keyboard fixed, or the data in the database corrected, they had the code "fixed". Or maybe somebody just felt like their claim numbers should be whitespace insensitive, like HTML sort of is.

Without knowing why, we can still understand the bad idea. The REGEXP_REPLACE searches for ( ){2,}: a group containing a single space, repeated two or more times. Regexes are expensive at the best of times, regexes in the where clause of a query are going to make it much more expensive.

This change didn't go through any formal deployment process- someone with database credentials logged in, made the change, and didn't tell anyone. We don't know who, we don't know why, but what we do know is that Rob reverted it back to the version of the code in source control. No one complained.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!