I've had more than just a taste of this myself recently. I'm no database expert but I know how to build a query, normalisation, relational integrity and all that. Enough to identify bad SQL in a CRUD application. And I'll take the time to learn more about it and how to better construct a schema and query (and whether or not an RDBMS is even appropriate for the application).
With this in mind my boss mentioned one of our sites was "dying on its arse." It was hammering the slow query log, pages could take 10, maybe 20 seconds to load.
Turned out there were NO indexes at all. Maybe the odd primary key from the default CMS install (which was by no means database efficient). So we sorted that and that sorted out the immediate speed issues.
Of course, it went without saying that a poorly constructed schema must be related to a poorly constructed query or two. We were actually wrong. It was more like a query or twenty.
The submission of one form was done field by field. There was an UPDATE query constructed for each field and processed there and then, on a form with at least 12 fields. There was a comment above, "We have to do it this way. Trust me." As far as I could tell they didn't know how to dynamically build a query.
Further along, different update queries were performed by first deleting the record, and then re-inserting it with one of the fields updated.
I never quite got as far as that. There was no comment anywhere about the reasoning for such a strange approach.
I think some developers don't bother figuring it out because they think there are better things to do than optimise your code.
Hi there! I made mostly random changes until it worked, so I have no idea why this contorted approach fixes the earlier failures. But I spend two weeks on this section, so don't touch it!
[this comment removed and replaced with the "Trust me" line, because maybe multi-line comments are breaking things today...]
We have a legacy database we support that creates a new dynamic stored procedure (with the same body) every time it performs a particular operation. How you can have enough knowledge to do this, but not to use a single procedure is beyond me.
With this in mind my boss mentioned one of our sites was "dying on its arse." It was hammering the slow query log, pages could take 10, maybe 20 seconds to load.
Turned out there were NO indexes at all. Maybe the odd primary key from the default CMS install (which was by no means database efficient). So we sorted that and that sorted out the immediate speed issues.
Of course, it went without saying that a poorly constructed schema must be related to a poorly constructed query or two. We were actually wrong. It was more like a query or twenty.
The submission of one form was done field by field. There was an UPDATE query constructed for each field and processed there and then, on a form with at least 12 fields. There was a comment above, "We have to do it this way. Trust me." As far as I could tell they didn't know how to dynamically build a query.
Further along, different update queries were performed by first deleting the record, and then re-inserting it with one of the fields updated.
I never quite got as far as that. There was no comment anywhere about the reasoning for such a strange approach.
I think some developers don't bother figuring it out because they think there are better things to do than optimise your code.