Monday, July 27 2009

“Show me on this doll where the bad SQL touched you”

I don’t want a database guru. I want a database ogre, who lives in a dank, dark cave lined with the bones of developers who think they can write their own queries and release them to Production.

During my latest round of load-testing, I discovered that one particular client-driven query degrades rather seriously under load. As in, fifteen minutes to use a unique device ID to look up the matching unique customer ID and a single string related to RMA status. Part of the problem was that the dev was looking in the wrong place, but the main problem was that he didn’t understand the data, so the query was written in a way guaranteed to maximize search time. (rant about poor schema design saved for another day…)

I am a SQL caveman. My formal training in database technology began and ended with a single COBOL class in the mid-Eighties. I rewrote the query and dropped the time to 0.062 seconds under the same heavy load.

Four orders of magnitude? Time to feed another dev to the ogre!

(and, yes, the checkin comment attached to this query begins “optimized the sql query for …”. The sad thing is that, relatively speaking, this is a true statement; his previous code was worse)

My new Monday t-shirt