Multiple duplicate HTTP requests/queries from single requests in Azure with Laravel

Question

I have a Laravel app being served on Azure. I am using an AJAX request to poll data for a javascript chart.

The AJAX requests a URL defined in my routes (web.php), thus:

Route::get('/rfp_chart_data', 'DataController@chart_data')->name('chart_data');

That controller method runs a postgresql query and returns a JSON file. This all works fine.

However, after experiencing some performance issues, I decided to monitor the postgres queries and discovered that the same query was running 3 times for each request to this URL.

This happens regardless of whether I:

• access the URL via an AJAX request

• go directly to the URL in a browser

• access the URL via cURL

This (AFAIK) eliminates the possibility that this is some sort of missing img src issue (e.g. What can cause a double page request?)

Thanks very much for any help...

EDIT:

Image of the duplicate queries in postgres pg_stat_activity -- this is from 1 web request:

EDIT:

Full controller code:

<?php

namespace App\Http\Controllers;

use App\AllRfpEntry;
use DB;
use Illuminate\Http\Request;
use Yajra\Datatables\Facades\Datatables;

class DataController extends Controller {
    /**
     * Displays datatables front end view
     *
     * @return \Illuminate\View\View
     */

    //|| result_url || '\">' || result_title || '</a>'

    public function chart_data(Request $request) {

        $binding_array = array();

        $chart_data_sql = "SELECT relevant_dates.date::date,
            CASE WHEN award_totals.sum IS NULL
            THEN 0
            ELSE award_totals.sum
            END
            as sum

            ,


            CASE WHEN award_totals.transaction_count IS NULL
            THEN 0
            ELSE award_totals.transaction_count
            END
            as transaction_count FROM

            (
            SELECT * FROM generate_series('" . date('Y-m-01', strtotime('-15 month')) . "'::date, '" . date('Y-m-01') . "'::date, '1 month') AS date
            )  relevant_dates

            LEFT JOIN

            (
            SELECT extract(year from awarded_date)::text || '-' || RIGHT('0' || extract(month from awarded_date)::text, 2) || '-01'  as date, sum(award_amount)::numeric as sum, COUNT(award_amount) as transaction_count FROM all_rfp_entries

            WHERE awarded_date >= '" . date('Y-m-01', strtotime('-15 month')) . "'

            AND awarded_date <= '" . date("Y-m-d") . "' AND award_status = 'AWARDED'
            AND award_amount::numeric < 10000000000";

        if ($request->get('rfp_company_filter')) {

            $binding_array['rfp_company_filter'] = $request->get('rfp_company_filter');

            $chart_data_sql .= " AND company = :rfp_company_filter";

        };

        if ($request->get('rfp_source_filter')) {

            $binding_array['rfp_source_filter'] = $request->get('rfp_source_filter');

            $chart_data_sql .= " AND rfp_source = :rfp_source_filter";

        }

        if ($request->get('exclude_fed_rev')) {

            $chart_data_sql .= " AND rfp_source != 'US FED REV' ";

        }

        if ($request->get('rfp_year_filter')) {

            $binding_array['rfp_year_filter'] = $request->get('rfp_year_filter');

            $chart_data_sql .= " AND year = :rfp_year_filter";

        }

        if ($request->get('rfp_priority_level_filter')) {

            $binding_array['rfp_priority_level_filter'] = $request->get('rfp_priority_level_filter');

            $chart_data_sql .= " AND priority_level = :rfp_priority_level_filter";

        }

        if ($request->get('rfp_search_input_chart')) {

            $binding_array['rfp_search_input_chart'] = $request->get('rfp_search_input_chart');

            $chart_data_sql .= " AND search_document::tsvector @@ plainto_tsquery('simple', :rfp_search_input_chart)";

        }

        $chart_data_sql .= " GROUP BY extract(year from awarded_date), extract(month from awarded_date)
        ) award_totals
        on award_totals.date::date = relevant_dates.date::date

        ORDER BY extract(year from relevant_dates.date::date), extract(month from relevant_dates.date::date)
        ";

        return json_encode(DB::select($chart_data_sql, $binding_array));


    }

    public function data(Request $request) {

        $query = AllRfpEntry::select('id', 'year', 'company', 'result_title', 'award_amount', 'edit_column', 'doc_type', 'rfp_source', 'posted_date', 'awarded_date', 'award_status', 'priority_level', 'word_score', 'summary', 'contract_age', 'search_document', 'link');

        if ($request->get('exclude_na')) {

            $query->where('all_rfp_entries.company', '!=', 'NA');

        }

        if ($request->get('clicked_date')) {

            $query->where('all_rfp_entries.awarded_date', '>', $request->get('clicked_date'));

            $query->where('all_rfp_entries.awarded_date', '<=', $request->get('clicked_date_plus_one_month'));

        }

        if ($request->get('filter_input')) {

            $query->whereRaw("search_document::tsvector @@ plainto_tsquery('simple', '" . $request->get('filter_input') . "')");

        }

        $datatables_json = datatables()->of($query)

            ->rawColumns(['result_title', 'edit_column', 'link'])

            ->orderColumn('award_amount', 'award_amount $1 NULLS LAST')
            ->orderColumn('priority_level', 'priority_level $1 NULLS LAST');

        if (!$request->get('filter_input')) {

            $datatables_json = $datatables_json->orderByNullsLast();

        }

        if (!$request->get('filter_input') and !$request->get('clicked_date')) {

            $count_table = 'all_rfp_entries';

            $count = DB::select(DB::raw("SELECT n_live_tup FROM pg_stat_all_tables WHERE relname = :count_table "), array('count_table' => $count_table))[0]->n_live_tup;

            $datatables_json = $datatables_json->setTotalRecords($count);

        }

            $datatables_json = $datatables_json->make(true);

        return $datatables_json;

    }


}

EDIT:

An even crazier wrinkle...

I have a method in Laravel on this server pointed at a postgres database on another server to ingest new data. I just found out that even THAT method (the one pointing at the external server) is generating multiple queries on the external postsgres server!

Unless I'm missing something, this obviates an nginx issue or an issue with my provider (Azure), or a problem with any one specific method. Even a straight database connection over port 5432 (I'm assuming that's how Laravel accesses external databases) is generating the multiplier effect, so it must be something screwy with my Laravel installation... but no closer to figuring out what.

Answer 1

The best way to debug this is to start a debug session with xdebug and stepping through the code while you keep an eye on the stdout/logging output in a separate window.

Set a breakpoint on the first line in your controller, when it breaks there should be 0 queries done. If not you know something weird is going on in routing/the request. Then step through the function calls used in building the query.

It might be that one of the functions you use triggers executing the query (as suggested by Aaron Saray) or methods are missing (as suggested by Diogo Gomes) but this is hard to tell without knowing the code or stepping through an execution context step-by-step.

If you do not have a debugger you can always use dd($data); at any line to stop processing too and dump the data given. It will just take a little longer because you'll be doing a new request for each step in the code.