Response with cached data, and how to refresh the cache form database, for a api application

Question

Hi could someone guide me in the following problem, there must be tons of guides on this problem but for some reason I can't get google to find a nice how to, to follow

I'm implementing this in aspnet core API, but I think the problem/solution could go for any language.

The problem, i have to call a view from a database that is painfully slow it takes about 15-30 seconds to return the ~ 300 rows It only returns the fields that are required. It joins from a lot of tables and multiple databases. (There are other applications that updates the data, I'm only interested in reading the result) The DBA says there is nothing he can do, so I have to find a solution, and why not, it could be fun

Now the real problem is there are about 250 autonomous clients requesting data, a client request data about every 2 minutes, and with the time it takes to select data it doesn't take long for the system to become unresponsive. It is the same data in the response for all requests

It would be acceptable to cache the rows for 5 minutes. Now how would I implement it so only one request select form the database and update the cache while all others read from a cache and perhaps are waiting if the cache is empty for a short period, while new data is being loaded to cache from the database view?

(I could write a script to be scheduled to execute every x minute, but it would be more fun to solve this in the application.)

I could perhaps make some cache tables in the database and let the api call check if the cache table is empty, if not get the data from the slow view, populate the cache database and return result. But then what would be a god solution to only empty the cache and populate the cache once and not multiple times when there are going to come multiple requests in the timeframe it takes to load data from the view.

And perhaps there are better alternatives that caching in a database table?

Hope anyone can help

Answer 1

Your question is very unspecific to a technology. So you are asking for an concept. In general

• check cache without locking
• return data if it is up to date
• perform lock
• check cache again
• update cache
• unlock and return data

You may read/use https://docs.microsoft.com/en-us/dotnet/core/extensions/caching

    // pseudo code
    async Result QueryFromCache()
    {
        // check cache is up to date - without lock
        var cacheData = await GetCacheData();   // latest data or null
        if (cacheData == null) 
        {
            // wait for cache data
            cacheData = await UpdateCache();
            return cacheData.Data;
        }

        // data is still up to date?
        if (cacheData.UpdateDate.AddMinutes(5) > DateTime.Now())
        {
            return cacheData.Data;
        }

        // Cache Update is necessary
        // Option 1: Start separate "fire and forget" Thread/Task to fill cache, but return old data
        Task.Run(UpdateCache());    // do not await
        return cacheData.Data;      // return immediatly with old data > 5 Minutes

        // Option 2: 
        cacheData = await UpdateCache();    // wait for an update
        return cacheData.Data;
    }


    async CacheData UpdateCache()
    {
        var lock = GetLock();   // lock, Semaphore, Database-Lock => depends on your architecture
        try 
        {
            // doubled check cache is up to date - with lock
            var cacheData = await GetCacheData();
            if (cacheDate != null && cacheData.UpdateDate.AddMinutes(5) > DateTime.Now())
            {
                return cacheData;
            }

            // Query data
            var result = await PerformLongQuery();

            // update cache
            var cacheData = new CacheData {
                UpdateDate = DateTime.Now(),
                Data = result;
            }
            await SetCacheData(cacheEntry);
            return cacheData;
        } finally {
            lock.Release();
        }
    }