1

We have all kinds of Lookups in our application, stored in a separate table, LOOKUP_T.

Should the application load them all on startup, and keep them in a global lifecycle variable -- or is it OK to keep making small trips to the DB to fetch these lookups depending on user action? In many cases these will be duplicated trips.

Are DB trips for lookup fetches expensive in today's world?

gene b.
  • 315
  • 1
  • 7
  • 2
    `Are DB trips for lookup fetches expensive in today's world?` -- Only your performance tests can answer that question. You also have to define what "expensive" means to you. – Robert Harvey Apr 27 '18 at 20:23
  • what @RobertHarvey, how important is performance and what are your times for performing these lookups? Both scenarios are suitable depending on the situation – Mr Dog Apr 28 '18 at 03:16
  • `Are DB trips for lookup fetches expensive in today's world?` Think of GPRS vs 4G. – Thomas Junk Apr 28 '18 at 07:10

2 Answers2

2

It is proven practice not to optimize programs before you don't have

  • indications for a noteable performance problem
  • you know for sure the place where you are optimizing is really the root cause of that problem

Said that, if you have an indication that at least one of the lookup values is causing performance losses, it could be simple in your case to provide a generic solution which provides a general preloading strategy for all lookup values (maybe simpler than providing an individual strategy for just one value).

If that is the situation, implementing such a preloading may be fine. Be aware, this does not come for free: preloading hardcodes the assumption into your program that those lookup value never ever change during the life time of your application process, and if that assumption becomes wrong, this will cause additional development effort to mitigate this.

This is well known as cache invalidation, and often mentioned as one of the two hardest problems in Computer science.

Are DB trips for lookup fetches expensive in today's world?

This question of yours expresses a fairly common misunderstanding: that it can be answered sensibly in general. To my experience the only sensible answer is: it depends (on the DB, the application, the whole system, the network etc.).

But let me answer that question differently: yes, it is possible that lots of DB roundtrips can become expensive in a system, especially when the network has a high latency (which is not uncommon when accessing a DB over a wide area network, with lots of intermediate routers, VPN channels, data encryption etc.) However, nothing except you own measurements and observations can tell you if this matters in your system.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
2

My advice is to prepare your application for preloading.

Don't implement the actual preloading, however, unless you have a demonstrated performance problem. Preloading has many problems, including how to notify the application to update the data that is stored in the database.

So, don't go around spreading database queries everywhere around the codebase in the expectation that preloading will not be needed. Instead, have a well-defined location for fetching the values with an API that is compatible with preloading.

With modern low-latency high-bandwidth networks, SSDs and big RAM in the database server, chances are your database won't be a bottleneck currently. You just need to be prepared for success of your application. What if load levels increase 1000-fold?

It's definitely a good thing that you have considered preloading. Don't implement it yet, however. Just prepare your codebase for the eventual implementation.

juhist
  • 2,579
  • 10
  • 14