0

We have the following use case to be developing:

A web application that shows a data table / data grid with up to 100000 rows and 30-50 columns. Each column is filterable and sortable. The web application is based on Angular and PrimeNG (with PrimeNG Table) and the backend is based on ASP.Net Core WebApi, but i think that doesn't matter.

The users a very picky, so performance is a top priority and the application should be scalable. Filtering a 100000 rows table should take ideally less than 5 seconds.

There are two special things in our environment:

  • The data set for the web application is generated in a SQL-Stored procedure. So the complex logic to prepare the Data is not in the API itself
  • The web application will implement real-time communication via SignalR. So when a user changes some data, all other users get this changes instant

What we can't do

Currently i think that it is not a good idea to download the whole data set from the API and let the client handle all the filtering / sorting stuff. Javascript is slow and not scalable.

So we need some of the good old paging stuff.

Our idea

I have this process in mind:

Web Client sends request to API -> API calls SQL-Stored Procedure and caches the result using a Cache Store like Redis or something and then returns the first page (like the first 100 rows) to the client

Now when the user filters / sorting the data or switches to another data page:

Web Client sends request to API with the "CacheId" (or something to identify the data set in the cache) -> When the API gets a CacheId it first tries to load the data from the cache instead of calling the SQL Stored Procedure again. -> The API applies filter, sorting, paging etc. and then returns the result to the client

This way the SQL-Stored Procedure (the expensive part) is not called every time and also only small amount of the data is transfered to the client.

Our problem:

How to handle the real-time communication with this architecture? When someone changes something in the data all the cached data sets must be updated. It is not an option to invalidate the data sets in the cache and generate them new from scratch because data changes happen very often (every minute). If we invalidate the whole cache every time, this would be very expensive.

So we need something like this:

User changes data and send "DataChanged-Event" -> Cached data sets are read from the cache -> Cached data sets will be updated and then are written back to the cache

Our questions

  • Do you think we are on the right path with our solution or are we doing something completly wrong?

  • Where should we handle the cache-updating process? Maybe a Azure Function / Console Application or something that receives all the "DataChanges Events" and then updates the cache?

Improve this question
4
  • 1
    Getting 100 records from a database table with 100,000 rows doesn't sound all that expensive. A properly written query on any reasonably good database system should take less than a second to run. Why not just run the query for every page request, and dispense with the cache?
    – Robert Harvey
    Nov 4, 2020 at 15:27
  • Alternatively, use an in-memory data store that is periodically saved to disk.
    – Robert Harvey
    Nov 4, 2020 at 15:32
  • The SQL Query takes place in a Stored Procedure and this Stored Procedure is complex. The data we display to the user is thus not directly available in a separate database table. We collect the data from several tables and transform them. This is very expensive
    – OPunktSchmidt
    Nov 5, 2020 at 7:35
  • Your requirements seem impossible. The users expect instant filter changes and instant data updates, against an expensive analytical query. It can't be done, unless you can find some new way to optimize the query or give up something.
    – Robert Harvey
    Nov 5, 2020 at 13:46

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.