Laravel: Performance Boost!
Tags: laravel, php, eloquent
Published: 2021-04-27 00:54:17

I recently started using phpdebugbar `start_measure(name, label)` and `stop_measure(name)` to see how I can make performance improvements to my platform.

If you didn't know, you can use phpdebugbar to measure how long a certain function takes to execute. To find this out you simply add `start_measure(name, label)` at the beginning of your function and `stop_measure(name)` at the end of your function and when you run that snippet of code in your browser you will get a result in the timeline tab, like below:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zswmipjdu4cjwhqjkzr8.png)

As you can see from the image above, this snippet of code is taking over 2 seconds to execute and it's using 203MB of memory 😳. This can be improved, a lot!

## Using Eloquent
Here is the snippet of code that is causing the issue:

```php
$results = $request->user()->incomes()->get();

$years = $results->unique(function($item){
    return $item['date_income_received']->year;
})->map(function($item){
    return $item['date_income_received']->year;
})->sort()->toArray();

return $years;
```

The goal of this code is to get a collection of incomes in the database and iterate through them to get the unique years for the incomes. So for instance, if I have 100 incomes in the database and the incomes are for both 2020 and 2021, the results will be an array with ['2020', '2021']. This way I can display the years to the user and they can filter through the incomes based on those years.

## What's the issue?
Well, the issue is with `$results = $request->user()->incomes()->get();`

The SQL statement being generated is as follow:
```
select * from `incomes` where `incomes`.`user_id` = 1 and `incomes`.`user_id` is not null
```
You can see in the image above that this query is getting over 70,000 models and a lot of columns that we don't need. So, how do we improve this query. By doing this:

```php
return Income::selectRaw("substr(date_income_received, 1, 4) as year")
        ->whereUserId($request->user()->id)
        ->groupBy('year')
        ->pluck('year');
```

The SQL statement for this is as follow:
```php
select substr(date_income_received, 1, 4) as year from `incomes` where `user_id` = 1 group by `year`
```

**Note: I originally had `->select([DB::raw('YEAR(date_income_received) as year')])` in the query but this made my tests fail due to YEAR() being incompatible with SQLite. After trial and error, I found `substr()` to be a good alternative. Although I think I'm planning to revert back to using MySQL for my tests, and in turn, I'll switch it back to YEAR()**

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x4emeyjxfawtx98cue1x.png)

This snippet of code takes 338ms and only uses 6MB of memory 🤯. That's a massive improvement and we're getting the same result.

Initial Query:
Over 2 seconds, 203MB of memory.

Improved Query:
338ms and only 6MB of memory.

## Conclusion
When writing queries it's always a good idea to have a way to see the performance of your query so that you can ensure that your writing optimal queries. It's easy to do something as simple as $request->user()->incomes()->get(), but as you can see from above this is not an optimized query and we can do better.