CODEWITHSUNDEEP
laravellaravel-5.6
Sandeep Nambiar
Sandeep Nambiar

Reputation: 1676

Increase the speed of laravel 5.6 API speed

I am using laravel 5.6 for creating API for my website. But API response is slow and it is about to taking around 16 sec to load. How can I increase the API response speed? I am running below SQL operations inside API call

$master_data        =   DB::table('native_visitors_main AS nvm')
                                    ->leftJoin('ad_campaigns AS ac','ac.id','=','nvm.ad_campaign_id')
                                    ->select(
                                            'ac.id as campaign_id',
                                            'ac.campaign_name as campaign_name',
                                            'nvm.current_cpc as cpc',
                                            DB::raw("SUM(CASE WHEN nvm.gmt_time BETWEEN '".$start_date."' and '".$end_date."'  THEN 1 ELSE 0 END) as total_click"),
                                            DB::raw("SUM(CASE WHEN nvm.is_unique > 0 AND nvm.gmt_time BETWEEN '".$start_date."' and '".$end_date."'   THEN 1 ELSE 0 END) as unique_click"),
                                            DB::raw("SUM(CASE WHEN nvm.is_unique > 0 AND nvm.pixel_status!=''  AND nvm.gmt_time BETWEEN '".$start_date."' and '".$end_date."'  THEN 1 ELSE 0 END) as ap_click"),
                                            DB::raw("SUM(CASE WHEN nvm.pixel_status   IN('TY','cake')  AND nvm.pixel_fired_on BETWEEN '".$start_date."' and '".$end_date."'  THEN 1 ELSE 0 END) as result"),
                                            DB::raw("SUM(CASE WHEN nvm.is_unique > 0 AND nvm.pixel_status  IN('LP','TY','cake')  AND nvm.pixel_fired_on BETWEEN '".$start_date."' and '".$end_date."' THEN 1 ELSE 0 END) as lp_clicks"),
                                            DB::raw("ROUND(SUM(CASE WHEN nvm.gmt_time BETWEEN '".$start_date."' and '".$end_date."' THEN nvm.current_cpc ELSE 0 END  ),2) as  total_cpc")
                                        )
                                        ->groupBy('ac.id')
                                        ->havingRaw("total_click >0")
                                        ->havingRaw("campaign_name != ''")
                                        ->get();

Upvotes: 0

Views: 685

Answers (1)

Mysteryos
Mysteryos

Reputation: 5791

Your SQL query is by its own nature, slow to execute.

Taking the reactive approach, we employ caching mechanisms to save the result for future requests.

You may either:

  1. Code the cache system manually: https://laravel.com/docs/5.7/cache
  2. Rely on a library to do intelligent caching: https://github.com/spiritix/lada-cache

Upvotes: 1

Related Questions