A common method of developing applications today involves a lot of web developers and no data modelers. Even now when relational databases are a proven commodity, it’s hard to believe how messed up they get.
One of my clients has a vendor application that follows this pattern, essentially no database design for a high-volume application. They break all the rules and then some; no foreign key constraints, poor relations among tables, and they create tables on the fly, implementing their own strange partitioning scheme. As inevitably happens, it broke yesterday. Large amounts of locking and blocking ground the system to a halt.
I received a call, dug into it, and determined they were doing a full table scan on 46M rows more than one per second. An obvious index was missing. I built it, recompiled the stored procedure where the problem surfaced, and they were off and running. A familar problem for DBAs, but I have to say I’m surprised how often I still see this kind of thing happen, and it seems to be worse in the web-centric world of software development.
Dejected but unbowed, I enjoin the DBAs of the world who blog, speak, write and work in the trenches to continue educating, reviewing, and fixing this all too common error!