Case Study – How we managed to drastically improve API Performance

Recently we got the chance to look into the load speed issue for one of the project, we are required to improve API performance for a listing screen. The project was built in AngularJS and API being created in Laravel.

Two of the major listing pages of the application has become hell slow with time, 10 seconds to be exact. The application itself is quite big and is going under heavy development for last 2 years. As it’s a SAAS based so data is growing rapidly. In this article, we are going to present the steps we took to improve API performance and how we managed to reduce the Load time of the Laravel API for listing screens which were taking more than 8 sec to just a sec.

Database Indexing

This is one of the most common mistakes, especially with those teams which don’t have a special database administrator assigned in the project to manage and create the database, and the task of creating the migration is left for the developer.  While creating the migrations for the database, or schema in general, developers tend to ignore creating indexes and they generally get away with it for quite long, Until your application is live and data grows to a point where you start feeling the performance issues, those long spinning loaders drops a hint.

Identification

How we identified which indexes we needed? We logged the queries for the slow API and identified the slowest running query which was alone taking 8 seconds.  That was a one complex query which can be broken down as follow

  1. Primary Table with about 100k records
  2. 6 joins
  3. 5-6 where conditions and some basic sorting

So next for me was to identify which of these is actually making the query slow. We started off with taking the conditions and sorting which didn’t make any significant difference.  and then removing join one by one and taking a note of the time it took to run the query. And yup found the join which reduced the query to milliseconds from 8 seconds. And you guessed the reason right. It was a very simple join but the missing index made it crawl. Created the index, ran the whole query again and it executed in milliseconds this time. F

It’s funny how one index only can improve API performance to an acceptable limit, while developers were ready to do a whole lot of refactoring. Plans were made to cache the query results, reduce the number of joins by denormalization of the data, and even storing some of the data in JSON format in primary tables, just to avoid the extra table.

Not to mention how much effort this must have caused.  Time spent identifying the actual reason was worth. With proper indexes on other parts as well, we managed to bring all our queries under 0.2 secs. But still, there were a lot of queries being run which were making the page appear slow.

General areas of implementation of indexes

  1. Join Columns:- You must ensure having indexes on columns on which you join. These are generally ID columns. For example post_id column of comments table.
  2. Columns part of where condition – Sometimes when you are working on a large dataset, you may need to create an index on columns which are being used on where part. Not every column needs an index on it, with every index being added to a table,  adding/modifying records becomes slow. But sometimes reading speed is more important than writing. Choose wisely.

Repeating of Queries in loop

Again from the logs, we were able to identify that a certain set of queries was running repeatedly. We were performing a loop on the loaded dataset from DB before for further processing. Not everything can be achieved from query alone, so that was sort of required. But still, there were areas which we had to fix.

1.) Problem with lazy loading.

This becomes a serious problem when performed within a loop. See this simple scenario

$jobs = Job::where('active', 1)->take(20)->get();

foreach($jobs as $job){

	//lazy load state
	$state = $job->state; 

	// do something with state. 
}

Changing above to

$jobs = Job::where('active', 1)->with('state')->take(20)->get();

foreach($jobs as $job){
	
	$state = $job->state; 
	// do something with state. 
}

Making that change will reduce about 19 queries. and in our actual project, this number was way more.

Querying Collection Instead

Sometimes for smaller data sets of 20-30 records, it’s easier to load the complete dataset into a collection before the loop. and then query the collection  instead of the model within the loop.

this helped us further reduce our queries.

Storing the compiled/Calculated data beforehand

We pre-calculated the data wherever we could to make reading faster. Some examples.

1.) No of pending appointments for a job. So instead of calculating that on the listing screens for each job. We persisted that number in the job table itself and kept it updated on whenever appointments were made.

2.) Many other flags for the job which were based on other complex conditions, we moved this processing from listing screens to other appropriate places.

The following two tabs change content below.

Ankit Kumar

Team Leader at Logiciel Solutions
This is my personal blog. I post about Laravel, Angular, SQL and Web Technologies here. I have been into web development for 7 years and learning new things always interest me. Looking forward to find a teacher in you all.