3 ms·
SQL Databases are quite a lot like front-wheel drive cars on icy roads, you don't get a lot of warning before everything goes bad. The biggest problem is a que
by protomyth 15y ago
SQL Databases are quite a lot like front-wheel drive cars on icy roads, you don't get a lot of warning before everything goes bad. The biggest problem is a query that finally exceeds what can be cached. You can go from less than a second to multiple minutes per query. It is much like the problem of doing high performance calculations where you want to use the CPU cache very efficiently and not have a cache miss.
Worse is when some optimizers suddenly switch plans to something that is inefficient. Sybase had a really bad habit of this with big datasets.
Sadly, a lot of the problem comes down to schema design. A lot of designs are just fine retrieving one item and its attributes from the database, but get into trouble when you try to retrieve a group of items with the same attribute from a database. Status reports (e.g. how many unpaid invoices do we have?) tend to sniff these problems out. I generally follow the rule "if a single attribute on an item is important to know, then knowing all of the items with that attribute is going to come up and should be designed for".