How We Built a FinTech Solution Boosting Database Performance [Case Study]
Nobody loves freezes and long loading times. Being a software development company, we can't stand them either. Recently, we’ve decided to modify our financial CRM looking to solve two main problems: load time and navigation speed.
Here's what we're going to talk about:
In a nutshell, it was taking too much time to load tables and hop between data-filled pages in accounting software that we used. To solve those issues, we’ve built a database solution that’s capable of storing over 1 million records and loading them fast during page-to-page views. Here’s a prototype, test it on your own.
Prototype’s capabilities
Project Challenges
We haven’t found any appropriate ready-made libraries as we needed a solution with a database, back end, and front end. There are some frameworks that could be helpful for us, but they load memory running in the background. In our solution, we wanted to focus on optimization and speed boost instead.
Learn how we address challenges in projects following our software development workflow.
Anyway, here are the main challenges we faced during development:
- Advanced pagination
- Filter and search
- Caching
Let me catch you up:
We had to know the overall number of records in order to figure out how many pages to display on the bottom panel to make the pagination work. The default number is 1 million. But when you enter a search query, the amount changes to the number of matching elements. We decided to call the count only when the search query changes. In other cases, the amount remains unchanged.
We’ve added a Covering Index to the database to let users search and filter information covering all fields. We used this data structure to avoid additional lookups to the table for getting requested data. In other words, it's another move needed to speed up the loading.
Caching is yet another solution to accelerate data loading. It caches queries on the back end to avoid loading the same data over and over again. This means that users get their data loaded even faster when making duplicate queries.
Tech Stack Used for This Project
We’ve used several technologies to make it work:
- MySQL
- .Net Core 3.1 for back end
- Angular for front end
- AWS EC2
- AWS RDS
It’s an open-source relational database management system backed by Oracle. MySQL works across all platforms including Linux, Mac, and Windows.
.NET is a framework developed by Microsoft. It’s designed for developing web and desktop applications.
Angular is an open-source web application framework developed at Google. It’s TypeScript-based and maintained by Google and community of individuals.
Amazon Elastic Compute Cloud is a web service providing computer capacity in the cloud. In this project, we used AWS EC2 to host our app.
Amazon Relational Database Service is a distributed relational database. It was used as a database for the project.
Tech stack used for the project
Wrapping Up
This solution can be used for financial operations as in our case. Still, it’s useful for virtually any project as every database is being filled up with data over time. It'll especially be useful for projects that are heavily focused on statistics and analytics.
