I am building an Angular web application that retrieves part of its data from a Azure SQL Database table via APIs developed in Azure Functions (with Azure API Management at the API Gateway). The data in the table (80k records) do not change for at least 24 hours. The web app needs to display this data in a grid (table structure) with pagination and users can apply filter conditions to retrieve and show a subset of the data in the grid (again with pagination). They can also sort the data on a column in the grid. The web app will need to be accessed by few hundred users on their iPad/tablet with 3G internet speed. Keeping the latency in mind, I am considering one of these two options for optimum performance of the web app:

1) Cache all the records from the DB table in Azure Redis Cache with cache refresh every 24 hours, so that the application will fetch the data to populate the grid from the cache, thus avoiding expensive SQL DB disk I/O. However, I am not sure how the filtering based on a field value or range of values will happen from Redis Cache data. I have read about using Hash data type for storing multivalued objects in Redis and SortedSet for storing sorted data, but I am particularly not sure about filtering data in Redis based on the range of numeric values (similar to BETWEEN clause in SQL) in Redis Cache. Also, is it at all advisable to use Redis in this way for my use case?

2) Use in-memory OLTP (memory optimized table for this particular DB table) in Azure SQL DB for faster data retrieval. This will allow to handle the filtering and sorting requests from the web app with plain SQL queries. However, I am not sure if it's appropriate to use memory optimized tables for improving just table read performance (from what I read, Microsoft suggests to use it for insert-heavy transactional operations).

Any comments or suggestions on the above two options or any other alternative way to achieve performance optimization?

  • How big are your 30k records? – Telastyn Jun 2 at 15:21
  • Sorry it's 80k records (30k was a typo which I corrected now). The total data size of this table on SQL DB is 33 MB. – Tanmoy Sengupta Jun 2 at 18:20
  • 1
    33 MB? Toss it in ram. No need to make things complicated. – Telastyn Jun 2 at 18:22
  • Which RAM? Did you see the tech stack? – Tanmoy Sengupta Jun 2 at 18:28
  • You don't have 33 MB available to play with? – Robert Harvey Jun 2 at 18:39

The answer, as with most performance related questions, is to measure. With 3G speeds in mind, the data transmitted between server and client is your bottle neck.

Do whatever is quickest from a development standpoint. Focus more on optimizing and measuring the data transferred to the device. You are almost better off downloading the data one page at a time to keep the payload small. The smaller the payload, the less network latency affects performance.

Network latency is your problem to solve here. Implement whichever backend solution is the fastest route to butting your head against optimizing for a lousy network. I don't care what the marketing hype ever said about 3G. It was always crap.

| improve this answer | |

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.