SQL queries loaded twice, serverside and ajax

vikas-wadhwa commented 1 year ago

Hi there,

Our app runs many effective datatables, but the one issue we have is that the serverside rendering hits the page, but it immediately does a refresh and asks for the same exact data again.

Our reporting could really use the efficiency of just one request, is there a way to prevent the serverside queries on page load? Or prevent the ajax from firing on page load?

matt-riemer commented 1 year ago

Hey vikas,

Thanks for your question!

The effective_datatables should not make any AJAX server requests on first render. Only when you do some kind of search/sort will it make any AJAX requests.

Maybe there's some kind of application JS thing that's making the initializer do some ajax action, or calling draw() on the datatable? Or like a second jquery ready event, or a turbolinks load event?

I wish my old heroku datatables demo app was still working, I could give you a direct link, but if you install:

https://github.com/code-and-effect/effective_datatables_demo

you can dig into just the datatable itself.

Here I visit the page for the first time, and it makes just 3 requests, 1 for the page, 1 for the javascript, and one for the favicon. There is no AJAX until I start searching or sorting.

CleanShot 2023-10-04 at 10 00 40

Hope this helps!

vikas-wadhwa commented 1 year ago

Well I've narrowed it down to right when the initialization happens, if there's any help you could provide it would be great!

We love the gem here really hoping we can keep tuning it. Heres an example config:

{
  "ajax": {
    "url": "/datatables/datatables-users_datatable-082647867262.json",
    "type": "POST"
  },
  "autoWidth": false,
  "buttons": [
    {
      "text": "Reset",
      "className": "datatable-reset-button"
    },
    {
      "extend": "colvis",
      "postfixButtons": [
        {
          "extend": "colvisGroup",
          "text": "Show all",
          "show": ":hidden",
          "className": "buttons-colvisGroup-first"
        },
        {
          "extend": "colvisGroup",
          "text": "Show none",
          "hide": ":visible"
        },
        {
          "extend": "colvisGroup",
          "text": "Show default",
          "hide": ":not(.colvis-default)",
          "show": ".colvis-default"
        }
      ]
    },
    {
      "text": "Refresh",
      "className": "datatable-refresh-button"
    },
    {
      "text": "Download Excel (visible rows)",
      "extend": "excel",
      "bom": true,
      "className": "secondary",
      "filename": "table_export",
      "extension": ".xlsx",
      "title": null,
      "exportOptions": {
        "columns": ":visible :not(.col-actions)",
        "format": {}
      }
    },
    {
      "text": "Download Excel (ALL rows)",
      "extend": "excel",
      "bom": true,
      "className": "secondary",
      "filename": "table_export",
      "footer": true,
      "extension": ".xlsx",
      "title": null,
      "exportOptions": {
        "columns": ":visible :not(.col-actions)",
        "format": {}
      }
    }
  ],
  "columns": [
    {
      "className": "col-integer col-user_id colvis-default",
      "name": "user_id",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_user_id\" data-column-name=\"user_id\" data-column-index=\"0\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>User ID</span>",
      "visible": true
    },
    {
      "className": "col-string col-last_name colvis-default",
      "name": "last_name",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_last_name\" data-column-name=\"last_name\" data-column-index=\"1\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Last Name</span>",
      "visible": false
    },
    {
      "className": "col-string col-first_name colvis-default",
      "name": "first_name",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_first_name\" data-column-name=\"first_name\" data-column-index=\"2\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>First Name</span>",
      "visible": false
    },
    {
      "className": "col-email col-email colvis-default",
      "name": "email",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_email\" data-column-name=\"email\" data-column-index=\"3\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Email</span>",
      "visible": true
    },
    {
      "className": "col-string col-locale",
      "name": "locale",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_locale\" data-column-name=\"locale\" data-column-index=\"4\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Locale</span>",
      "visible": false
    },
    {
      "className": "col-string col-profession",
      "name": "profession",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_profession\" data-column-name=\"profession\" data-column-index=\"5\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Profession</span>",
      "visible": false
    },
    {
      "className": "col-string col-professional_title",
      "name": "professional_title",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_professional_title\" data-column-name=\"professional_title\" data-column-index=\"6\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Professional Title</span>",
      "visible": false
    },
    {
      "className": "col-string col-degree",
      "name": "degree",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_degree\" data-column-name=\"degree\" data-column-index=\"7\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Degree</span>",
      "visible": false
    },
    {
      "className": "col-string col-timezone colvis-default",
      "name": "timezone",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_timezone\" data-column-name=\"timezone\" data-column-index=\"8\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Timezone</span>",
      "visible": false
    },
    {
      "className": "col-string col-affiliation",
      "name": "affiliation",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_affiliation\" data-column-name=\"affiliation\" data-column-index=\"9\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Affiliation</span>",
      "visible": false
    },
    {
      "className": "col-string col-me_number",
      "name": "me_number",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_me_number\" data-column-name=\"me_number\" data-column-index=\"10\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Me Number</span>",
      "visible": false
    },
    {
      "className": "col-string col-npi_number",
      "name": "npi_number",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_npi_number\" data-column-name=\"npi_number\" data-column-index=\"11\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>NPI Number</span>",
      "visible": false
    },
    {
      "className": "col-string col-taxonomy_code",
      "name": "taxonomy_code",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_taxonomy_code\" data-column-name=\"taxonomy_code\" data-column-index=\"12\" type=\"text\" /></div>",
      "sortable": false,
      "title": "<span>Taxonomy Code</span>",
      "visible": false
    },
    {
      "className": "col-string col-practice_states",
      "name": "practice_states",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_practice_states\" data-column-name=\"practice_states\" data-column-index=\"13\" type=\"text\" /></div>",
      "sortable": false,
      "title": "<span>Practice States</span>",
      "visible": false
    },
    {
      "className": "col-string col-last_sign_in",
      "name": "last_sign_in",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": null,
      "sortable": true,
      "title": "<span>Last Sign In</span>",
      "visible": false
    },
    {
      "className": "col-string col-phone_mobile",
      "name": "phone_mobile",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_phone_mobile\" data-column-name=\"phone_mobile\" data-column-index=\"15\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Phone Mobile</span>",
      "visible": false
    },
    {
      "className": "col-boolean col-phone_mobile_validated",
      "name": "phone_mobile_validated",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_phone_mobile_validated\" include_null=\"true\" data-column-name=\"phone_mobile_validated\" data-column-index=\"16\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"true\">true</option>\n<option value=\"false\">false</option></select></div>",
      "sortable": true,
      "title": "<span>Phone Mobile Validated</span>",
      "visible": false
    },
    {
      "className": "col-string col-work_address",
      "name": "work_address",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_work_address\" data-column-name=\"work_address\" data-column-index=\"17\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Work Address</span>",
      "visible": false
    },
    {
      "className": "col-string col-work_address_country",
      "name": "work_address_country",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_work_address_country\" data-column-name=\"work_address_country\" data-column-index=\"18\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Work Address Country</span>",
      "visible": false
    },
    {
      "className": "col-string col-personal_address",
      "name": "personal_address",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_personal_address\" data-column-name=\"personal_address\" data-column-index=\"19\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Personal Address</span>",
      "visible": false
    },
    {
      "className": "col-string col-personal_address_country",
      "name": "personal_address_country",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_personal_address_country\" data-column-name=\"personal_address_country\" data-column-index=\"20\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Personal Address Country</span>",
      "visible": false
    },
    {
      "className": "col-integer col-messaging_preference",
      "name": "messaging_preference",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_messaging_preference\" include_null=\"true\" data-column-name=\"messaging_preference\" data-column-index=\"21\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"0\">not_specified</option>\n<option value=\"2\">sms</option>\n<option value=\"3\">whatsapp</option>\n<option value=\"1\">opt_out</option>\n<option value=\"4\">opt_out_via_sms</option></select></div>",
      "sortable": true,
      "title": "<span>Messaging Preference</span>",
      "visible": false
    },
    {
      "className": "col-boolean col-reportable colvis-default",
      "name": "reportable",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_reportable\" include_null=\"true\" data-column-name=\"reportable\" data-column-index=\"22\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"true\">true</option>\n<option value=\"false\">false</option></select></div>",
      "sortable": true,
      "title": "<span>Reportable</span>",
      "visible": true
    },
    {
      "className": "col-belongs_to col-role",
      "name": "role",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_role\" option_text_method=\"datatable_option_text\" include_null=\"true\" data-column-name=\"role\" data-column-index=\"23\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"superuser\">superuser</option>\n<option value=\"admin\">admin</option>\n<option value=\"representative\">representative</option>\n<option value=\"frontend\">frontend</option>\n<option value=\"guest\">guest</option></select></div>",
      "sortable": true,
      "title": "<span>Role</span>",
      "visible": false
    },
    {
      "className": "col-boolean col-emailable",
      "name": "emailable",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_emailable\" include_null=\"true\" data-column-name=\"emailable\" data-column-index=\"24\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"true\">true</option>\n<option value=\"false\">false</option></select></div>",
      "sortable": true,
      "title": "<span>Emailable</span>",
      "visible": false
    },
    {
      "className": "col-boolean col-active",
      "name": "active",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><select class=\"effective_select form-control\" id=\"datatable_search_active\" include_null=\"true\" data-column-name=\"active\" data-column-index=\"25\" data-input-js-options=\"{&quot;method_name&quot;:&quot;effective_select&quot;,&quot;theme&quot;:&quot;bootstrap&quot;,&quot;minimumResultsForSearch&quot;:6,&quot;width&quot;:&quot;style&quot;,&quot;placeholder&quot;:&quot;&quot;,&quot;allowClear&quot;:true}\"><option value=\"\"></option>\n<option value=\"true\">true</option>\n<option value=\"false\">false</option></select></div>",
      "sortable": true,
      "title": "<span>Active</span>",
      "visible": false
    },
    {
      "className": "col-string col-primary_specialty",
      "name": "primary_specialty",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_primary_specialty\" data-column-name=\"primary_specialty\" data-column-index=\"26\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Primary Specialty</span>",
      "visible": false
    },
    {
      "className": "col-string col-work_main_phone",
      "name": "work_main_phone",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_work_main_phone\" data-column-name=\"work_main_phone\" data-column-index=\"27\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Work Main Phone</span>",
      "visible": false
    },
    {
      "className": "col-string col-personal_main_phone",
      "name": "personal_main_phone",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_personal_main_phone\" data-column-name=\"personal_main_phone\" data-column-index=\"28\" type=\"text\" /></div>",
      "sortable": true,
      "title": "<span>Personal Main Phone</span>",
      "visible": false
    },
    {
      "className": "col-has_many col-communities colvis-default",
      "name": "communities",
      "responsivePriority": 10000,
      "search": null,
      "searchHtml": "<div class=\"form-group\"><input class=\"form-control\" id=\"datatable_search_communities\" data-column-name=\"communities\" data-column-index=\"29\" type=\"text\" /></div>",
      "sortable": false,
      "title": "<span>Communities</span>",
      "visible": true
    }
  ],
  "searchCols": [
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null
  ],
  "deferLoading": null,
  "deferRender": true,
  "displayStart": 0,
  "iDisplayLength": 5,
  "language": {
    "sEmptyTable": "No data available in table",
    "sInfo": "Showing _START_ to _END_ of _TOTAL_ entries",
    "sInfoEmpty": "Showing 0 to 0 of 0 entries",
    "sInfoFiltered": "(filtered from _MAX_ total entries)",
    "sInfoPostFix": "",
    "sInfoThousands": ",",
    "sLengthMenu": "&nbsp;with _MENU_ per page",
    "sLoadingRecords": "Loading...",
    "sProcessing": "Processing...",
    "sSearch": "Search:",
    "sZeroRecords": "No matching records found",
    "oPaginate": {
      "sFirst": "First",
      "sLast": "Last",
      "sNext": "Next",
      "sPrevious": "Previous"
    },
    "oAria": {
      "sSortAscending": ": activate to sort column ascending",
      "sSortDescending": ": activate to sort column descending"
    },
    "buttons": {
      "colvis": "Show / Hide"
    }
  },
  "lengthMenu": [
    [
      5,
      10,
      25,
      50,
      100,
      250,
      500,
      9999999
    ],
    [
      "5",
      "10",
      "25",
      "50",
      "100",
      "250",
      "500",
      "All"
    ]
  ],
  "order": [
    1,
    "asc"
  ],
  "processing": true,
  "responsive": false,
  "serverSide": true,
  "stateSave": true,
  "stateDuration": 1209600,
  "scrollCollapse": true,
  "pagingType": "simple_numbers",
  "dom": "<'dt-settings-buttons inline'B><'dt-settings-pagination'p><'dt-processing'r><'dt-table't><'dt-settings-info'i><'dt-settings-length'l><'dt-settings-pagination'p>"
}

matt-riemer commented 1 year ago

The JS you provided isn't really helpful. I think this is what the gem might produce to call the datatables library itself.

The javascript to initialize the effective_datatable, and turn the raw table into a datatable start here:

https://github.com/code-and-effect/effective_datatables/blob/master/app/assets/javascripts/effective_datatables/initialize.js.coffee#L204

^ This is coffeescript lol sorry. But that $ -> initializeDataTables() is just shorthand for "on jQuery ready" call the initializeDataTables() function defined above.

You could try adding some console.logs on jQuery ready, and on turbolinks:load to debug before or after where the ajax event is being triggered.

The JS is setup to only initialize a table once, even if called multiple times. When it runs, it turns the table data into a datatable. It loads html and does a bunch of stuff. It does not make any ajax requests in the initializer at all.

You might need to git clone the gem, and then develop it locally gem 'effective_datatables', path: '~/projects/effective_datatables' then make sure your assets are fresh by running rake assets:clobber and then you can add console.logs in that initialize.js inside the gem to debug further.

Did you take a look at the effective_datatables_demo app info I provided. Did the datatables there make an extra ajax request like your app does?

Thanks

code-and-effect / effective_datatables

SQL queries loaded twice, serverside and ajax #178