3 ms·

Me too. I've seen a few systems that replaced their simple request requiring 10,000 queries "optimized" by requiring 10,000 cache lookups when they should have

by flukus 6y ago

Me too. I've seen a few systems that replaced their simple request requiring 10,000 queries "optimized" by requiring 10,000 cache lookups when they should have just added some joins. The bottleneck is the network latency, not the database. The worst I've seen is an nHibernate cache stored in a session variable, half the database was being serialized/deserialized on every http request. Fortunately that was a small database. Even with in memory caches I've seen systems grind to a halt by death of a thousand cuts, dictionary based entity attribute systems where each attribute is looked up individually. There seems to be a mentality that constant lookup == free lookup and devs don't seem to realize that constant * $bignumber == $biggerNumber. Caching shouldn't be granular. Obligatory latency numbers every programmer should know: https://gist.github.com/jboner/2841832 https://gist.github.com/jboner/2841832

YarickR2 6y ago
It very much depends. I've seen too many cases when business logic was moved to the database layer, with absolutely disastrous effects. Scaling application backends doing map-reduce on a pre-filtered datasets is way easier than scaling database to handle five-way joins. Yet,I've seen opposing cases too, when rdbms was used as a huge key-value storage , and was pushing hundreds of thousands of rows to tens of backends, sucking it's bandwidth dry.
noisy_boy 6y ago
Having worked with Hibernate recently on a smallish database (that isn't expected to grow too big), my take away is to minimize the network trips. If you have to check something for a list/set of things and all of that can be done from, say, two not so big tables, fetch the superset of records via a join with as much criteria pushed to predicates as efficiently possible and handle rest of the logic in application code. It is so much more quicker. Of course, like most things in software, conditions apply like network latency/total no. of records being fetched by the query/potential for data to suddenly grow etc.
- bernawil 6y ago
  Not to cache simple trips to the database is pretty uncontroversial, the are more controversial cases elsewhere. Say you consume a Restful API to hidrate order data in another system. Do you fetch orders as: /orders?ids=id-1,id-2,id-3 or separate calls to /orders/id-x which can be cached and retrieved by id as a memoized function? Well if you had to pick only one the second is probably better, but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint.
  - flukus 6y ago
    If this is a common occurrence the former would almost always be better, the second would be absolutely glacial. > but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint. Unless a significant amount of requests have 100% cache hits then I doubt a local cache will make much of a difference at all, all it's saving is a bit of bandwidth.

by flukus 6y ago

YarickR2 6y ago

It very much depends. I've seen too many cases when business logic was moved to the database layer, with absolutely disastrous effects. Scaling application backends doing map-reduce on a pre-filtered datasets is way easier than scaling database to handle five-way joins. Yet,I've seen opposing cases too, when rdbms was used as a huge key-value storage , and was pushing hundreds of thousands of rows to tens of backends, sucking it's bandwidth dry.

noisy_boy 6y ago

Having worked with Hibernate recently on a smallish database (that isn't expected to grow too big), my take away is to minimize the network trips. If you have to check something for a list/set of things and all of that can be done from, say, two not so big tables, fetch the superset of records via a join with as much criteria pushed to predicates as efficiently possible and handle rest of the logic in application code. It is so much more quicker. Of course, like most things in software, conditions apply like network latency/total no. of records being fetched by the query/potential for data to suddenly grow etc.

bernawil 6y ago
Not to cache simple trips to the database is pretty uncontroversial, the are more controversial cases elsewhere. Say you consume a Restful API to hidrate order data in another system. Do you fetch orders as: /orders?ids=id-1,id-2,id-3 or separate calls to /orders/id-x which can be cached and retrieved by id as a memoized function? Well if you had to pick only one the second is probably better, but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint.
- flukus 6y ago
  If this is a common occurrence the former would almost always be better, the second would be absolutely glacial. > but the best would be abstracting away order fetching in application code to always fetch single orders and behind the scenes looking up the cache for singles and pooling all the misses into a single request to the plural endpoint. Unless a significant amount of requests have 100% cache hits then I doubt a local cache will make much of a difference at all, all it's saving is a bit of bandwidth.