Luis Henrique Vibranovski

When I started to work on this project, the application had several performance bottlenecks. The performance issues made an awful impression on the company's clients, even though the product was very rich and capable of covering all the business logic and legal requirements. We removed the worst bottlenecks related to the SQL Server and made the user experience much more enjoyable and productive.

In this short-term project, working as a Toptal talent, I had the opportunity to apply the techniques I know to solve the main problems in customer databases. These databases had tables with tens of millions of records, and their queries were built when the database was very small. Fixing indexes and queries resolved the customer's issues and prepared the database for future growth. The client, who had already doubled the database server size, could return the server to the original size and still had spare capacity.

I was called to be a consultant on this project because the SQL Server, installed in a virtual machine (VM) on the cloud, worked with 100% CPU during business hours. The decision to upgrade the VM was already made but not deployed yet. I suggested making a diagnosis before the company doubled its monthly fee to the cloud provider. After completing the diagnosis, we found that the problem was one single high-used SSRS report with many bad queries. We approached the problem by changing the report to create database views and new indexes. As a result, the CPU utilization dropped from 100% to 60% during business hours, and the hardware upgrade was canceled without being deployed.

On many occasions, I was called to solve the Entity Framework performance issues. The process is always the same when improving the Entity Framework with zero or very few code changes. First, I identify the query that is being generated, then diagnose the problem, and finally find and test a solution.

The solution, in most cases, is to create or modify the correct index to support the query or change this specific Entity Framework's call for a direct call to the database without using the Entity Framework.

A very few cases need a different approach. A loop in the application with a number of Entity Framework calls inside it may be solved by creating a stored procedure and transferring the process to the database. But this solution involves more resources and should be used with care.

On this hourly short-term Toptal project with MySQL, the client's website was freezing and sometimes going down because of the traffic increase.

In the first phase, using only Amazon RDS Performance Insights and Amazon CloudWatch, I could identify and fix many queries, only adding the correct indexes to support them. This approach was enough to eliminate the website freezes.

In the second phase, the client gave me access to his application (Git repository), and I fixed the queries that could not be fixed only with indexes.

The final result, from an AWS RDS point of view, was the database's use dropped from 10 CPUs to less than 2 CPUs. The website has been very stable and fast since this project, and the client could decrease the RDS server size.

On this middle-size Toptal assignment on PostgreSQL, the client—a PPC startup—already had a complete website developed. From a business perspective, I was doing a successful job acquiring new clients almost every week.

From a database perspective, the central table of the software, which controls all the keywords for every client, was multiplying its size daily, hurting the client's dashboard performance.

In the diagnostic phase, we identified that the software was built over layers of functions and views, which was the problem.

As the client decided to solve the problem with minimum intervention and take advantage of that, the data was updated only once a day. More than 80% of the keywords were not receiving any data. We decided to add a new layer between the dashboard and the base tables, work with materialized views, and let the dashboard data be prepared to be fetched by the clients as soon as they start using the dashboards.

The dashboards started to return data at a comfortable level, and the application that was also receiving timeout messages was stabilized.