Suppose we have a DB with a table holding

first name
last name 

The DB is a black box. And there are a lot of get and post requests for these records. We cannot change the DB but only calling its API. The API is very slow. How can we come up with a design to reduce the impact of slow DB access?

My initial thought is Use LRU cache for get requests. Message Queue for post/put requests to be processed asynchronously.

Second thought, maybe we can use Bloom filter for the get requests based on last and first name combined with LRU cache. But the post/put requests will still be processed asynchronously.

Is this the right approach? Please share your thought.

  • 2
    It really depends. Caching may not work if there's no reason to access a person more than once. Would it make sense to do asynch? Possibly - it depends on your problem. Sadly no "one true way" so you'll have to describe more of the situation (including why you can't change the DB). – LoztInSpace Nov 25 '19 at 22:41
  • It's hypothetical. Let's say each person is accessed repeatedly. There are a lot of get, post, and put requests. Some records maybe accessed more often than others. – user3837980 Nov 25 '19 at 22:47
  • 1
    Then a cache would work, yes. If you have patterns you know are consistent (such as processing in ID order for example) you can prime your cache with a background task. Depending on the capability of your DB/API you might be able to do something similar with names and/or getting in bulk. Asynch can work depending on what you need to do with the response and if it can be "fire & forget". If not, you may have to put the entire function that contains the updates into a thread. – LoztInSpace Nov 25 '19 at 23:01
  • 1
    R U sure the DB is the only problem? We had (C#) code significantly manipulating data from straight forward DB fetches. In one case we threw it all away and re-wrote as an SQL procedure, time went from hours to seconds. seriously. Any code acting like a wanna-be database is a code smell (i.e. it's suspicious) in my opinion. – radarbob Nov 26 '19 at 1:51
  • 2
    At some point your cache becomes the actual runtime database, with the slow database becoming more of an eventual backup. Not necessarily a bad solution, but I'm not sure if that's what you're after. – Roger Nov 26 '19 at 15:25

If possible, start simple (or spend more time analysing the actual usage patterns - eg read vs write). It might be that you can put a simple cache on the front to help with only read performance:

  1. a read pulls from the cache. If not cached, loaded from db into cache first
  2. a write writes through to db directly but also clears the cache entry

This approach is simplistic and the devil may be in the detail but if it is appropriate, it might be the best result for the least effort. A bloom filter is the opposite of the simplistic approach, but could be used in conjunction.

| improve this answer | |
  • How can write through cache speed up POST/PUT requests? It will still have to wait until DB is updated? Right? – user3837980 Nov 28 '19 at 22:50
  • If all your post/put requests modify the database then my suggestion won't help. It will however help the get requests. I assumed your "slow database access" problem would be a combination of read and write contention but perhaps that is an incorrect assumption. – Paul Jowett Nov 30 '19 at 13:17

Fixing the database should be a priority.

Thoughts: Most data access follows the 80:20 rule. 80% of the requests use 20% of the data. And in that 20% the same applies. 80% of its accesses use only 20% of it's data.

So caching can help. But now you have cache coherency issues. Does user A have a different cache from user B? Sometimes you don't care. If a database is read only, then separate caches are ok.

You also need to know what the correlation is between users. If both User A and User B want fiscal report 2019, quarter 3 data then even for read access you want a single cache. So caching may be best at the department or division level, not the worker level.

Write operations are a different kettle of fish. There is a lot of arcane magic in database programming and in the design of the database itself to handle multiple people wanting to write the same data. Doing this in your front end is the same order of difficulty as writing a database from scratch, especially as you don't know the mechanisms in the black box.

If not having perfectly up to date reports is tolerable, then you can write transactions to a log file, then have a separate process commit them to the database. But now you have the issue of what happens when both UserA and UserB have pulled a copy of record 1234 and have modified one field in it. Same field? Different field? The actual database is usually designed with some form of record locking:

UserA grabs record 1234. UserB also grabs it. UserB wants to make a modification. Database locks record. UserA's client is notified that record 1234 is locked. B's modifcation is written to the database. A is given the updated record. If A tried to modify the record while B had it locked, A would receive a 'locked' error.

Not too bad when users are squabbling over an individual record. What happens when User B is doing a bulk update. Imagine a photo database, and User B wants to change the spelling of a keyword, or apply a copyright notice to 5 million image records.

You then have to consider relative speed. At one point in the Bad Old Days we had a box with some 256 MByte of ram in it. Our desk top machines had 4 MByte. It worked out that the network was faster than disk, so for small data access (Under 4K) it was faster to set set up the big box as a memory server.

| improve this answer | |
  • It's hypothetical. Just assume we cannot change the DB. What are the best practices to increase speed ? I'm thinking use bloom filter to filter out records that do not exist in DB. Then if it does exist, look it up in the LRU. If it's not in the LRU, then go to the DB and update LRU. How about write requests? How do we handle the cache with a lot of requests creating, updating, and deleting records? – user3837980 Nov 26 '19 at 23:05
  • I think you need to spell out more details about the back end, and the types of reads/writes are going on, what size chunks are being pulled, how many concurrent users – Sherwood Botsford Nov 27 '19 at 4:28

I had a similar problem with a Java application. Using a DB with superslow speeds which I could do nothing about, and those speeds basically made the application unusable if the queries were synchronous.

I solved it by creating a cache, where all reads went to the cache, when the cache did not have the element in question, it loaded and returned it.

When a write was made, the action was added to a queue. A thread was started, that had a while loop, e.g while queue not empty do operation if the queue became empty the thread was suspended and woken again upon things being added to the queue. If the write was a deletion, all other writes on that element were removed from the queue, the delete taking the position of the first item (this could save a bit of IO)

Changes made from the GUI were shown instantly although if the operation then failed the changes were reverted and a small warning about connection problems shown.

This worked very well, however:

  • It was a single-user program so only one client would ever be running
    • the program assumed direct db connection
    • the implementation used Singleton, which was probably ill advised.
| improve this answer | |
  • Cool! But what if there are many users sending requests concurrently? – user3837980 Nov 28 '19 at 22:51
  • I'm going to assume that what you mean is: what of there are many clients sending requests concurrently. Well, if that is the case I'd implement this wrapper in an service (web api probably) which handles talking to the database, and then use the API from the client. This way it still serves. As a common cache for all the clients. One thing I forgot to say is: when one has updated the database, the model must of course he 'reloaded' to reflect the change – rasmus91 Nov 28 '19 at 22:58
  • There's also the consideration of what type of program this is. In my situations, the above solution was acceptable, especially as there were no other software altering the contents of the database, and because it was super critical that the changes were made; e.g. if the connection died while there were still unfinished operations, it wasn't a total disaster – rasmus91 Nov 29 '19 at 5:31

Web api might not be the best suited candidate, perhaps rather a TCP service: thing is: you want an ongoing connection. Every client needs to know when one of it's models has a value updated

| improve this answer | |
  • This was supposed to be a comment to my other answer, not a new answer. Sorry. – rasmus91 Nov 29 '19 at 5:25

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.