John Davidson

Message:

i am trying to optimize the below code to speed up the ttfb time that is actually 11 second (tooo high).
The json contain thousands of geo coordinates used to print polylines on Google Maps.
There is something wrong on the code or i need to increase server resources?
Thanks

public function getTrackingData($scheduleId)

{

$segmenti = DB::select(DB::raw("SELECT a.segmentid,b.colore FROM schedules_trackings a left join devices b on a.device_id=b.id where schedule_id = :scheduleId  group by a.segmentid,b.colore,b.sigla"), array(

  'scheduleId' => $scheduleId,

));

$resultArray = json_decode(json_encode($segmenti), true);



$tracking = array();

foreach ($resultArray as $seg) {

  $geodata = DB::select(DB::raw("SELECT a.latitude, a.longitude FROM schedules_trackings a left join devices b on a.device_id=b.id where schedule_id = :scheduleId and segmentId = :segmentId  order by a.created_at"), array(

    'scheduleId' => $scheduleId,

    'segmentId' => $seg['segmentid']

  ));



  $tracking[] = array(

    'segment' => $seg['segmentid'],

    'color' => $seg['colore'],

    'geodata' => $geodata

  );

}

return response()->json($tracking, 200);

}

---

My Answer:
To optimize the code and speed up the TTFB (Time To First Byte) time, you can consider the following suggestions:

1. Use Eager Loading: Instead of making multiple database queries inside the loop, you can use eager loading to fetch all the necessary data in a single query. This will reduce the number of database queries and improve performance.

2. Use Caching: If the data doesn't change frequently, you can cache the results to avoid fetching the data from the database every time. Laravel provides caching mechanisms like Redis or Memcached that you can use to store and retrieve data efficiently.

3. Use Pagination: If the JSON response contains a large amount of data, consider implementing pagination to limit the number of records returned in each request. This will reduce the response size and improve the TTFB time.

4. Optimize Database Queries: Make sure that your database queries are optimized by adding indexes to columns used in the WHERE clause and ORDER BY clause. This will help in faster retrieval of data from the database.

5. Use Queues: If the data processing is time-consuming, consider using Laravel queues to offload the processing to background jobs. This will help in improving the response time for the initial request.

By implementing these optimizations, you should be able to improve the TTFB time and overall performance of your Laravel application.