I'm building a web app that hits up some APIs to get JSON dumps of data. I'm running across a problem. I can either grab all the data, dump it into a DB, then pull it from the DB as needed. OR I can just keep hitting the relevant API every time I need the data. (I have upwards of 7 different functions, each of which require a different set of input data which could be any combination of APIs, never more than three though).

I suppose it should be noted that the JSON dump changes daily. Not the keys, just the values (and total number of entries). So it's not like a one time dump into the DB, I'd have to refresh the DB every day or so.

So my question is, which is better? I'm guessing that constantly calling an API uses more bandwidth and thus takes longer, but I was also taught that one should avoid making tons of SQL requests if possible. Near as I can tell, I'd be making about the same number of calls/requests either way. So in terms of speed and bandwidth, which option would be better?

  • 4
    I'll take caching for 200 please Alex. – Jon Raynor Mar 15 '16 at 15:15
  • In terms of speed and bandwidth the database part would be faster (Assuming that the data actually is used several times later on). Avoid making tons of SQL requests? Well, you program will have to make all the requests necessary for it to work. (You should avoid making a lot of single requests if you can as well get all data you need right now with a single request but that's another matter). Also databases are highly optimized and can handle a lot of requests. (Also depending on your needs some alternate data storage may be interesting like Redis maybe) – thorsten müller Mar 15 '16 at 15:16

What you are essentially asking is, "to use a cache or not to use a cache".

I'm heavily leaning towards "to use a cache", but not in mysql. If you have the RAM for it (and RAM is cheap nowadays) use an in-memory cache like Redis.

To keep the data fresh, you have two options. By keeping a timestamp along the cached data and when/if that's from the last day, go call all those APIs and assemble your response, update the cache, serve response - done.

Or you can run a separate program/service that just keeps updating the cache whenever needed.

Both have advantages and disadvantages.

Updating on the fly

The first option would be the easiest to implement, but that first request will be slower than the subsequent ones.

Dedicated updater

The other adds complexity and more moving parts into your system. On the other hand, it separates your system and the reading part only reads, a dedicated writer writes and you get blazing fast responses every time.

The choice is yours!

Reasons to use a cache

  1. Less processing, bandwidth and time spent on your server. In other words, you can serve more requests and do it faster.
  2. You are being nice to the API you are consuming, you are saving them time, money and processing power.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.