Slow loading table

webpointz

I have a products table and one customer is taking between 10-20 seconds to load with only 4,000 records.

Here is my PHP. The products table has indexes on productid and customerid. I'm passing the customerid in the where clause.

<?php
session_start();

$thisCustomerid = $_SESSION['customerid'];

/*
 * Editor server script for DB table table_products
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'table_products', 'id' )
    ->fields(
        Field::inst( 'table_products.id' ),
        Field::inst( 'table_products.productid' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::unique' ),
        Field::inst( 'table_products.lotnumber' ),
        Field::inst( 'table_products.manufacturedate' ),
        Field::inst( 'table_products.expirydate' ),
        Field::inst( 'table_products.productname' ),
        Field::inst( 'table_products.description' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'table_products.grouping' ),
        Field::inst( 'table_products.stockwarning' )
        ->validator( 'Validate::notEmpty', array(
        "message"  => "Please provide a number (0 minimum)" ) ),
        Field::inst( 'table_products.equipment' ),
        Field::inst( 'table_products.miscellaneous' ),
        Field::inst( 'table_products.notes' ),
        Field::inst( 'table_products.unitcost' )
        ->validator( 'Validate::notEmpty', array(
        "message"  => "Please provide a number (0 minimum)" ) ),
        Field::inst( 'table_products.unitmeasurement' ),
        Field::inst( 'table_products.active' )  
            ->options( 'table_active', 'id', 'active' ),
        Field::inst( 'table_products.customerid' )
            ->setValue($thisCustomerid),
        Field::inst( 'table_products.upc_code' )
    )
    ->where( 'table_products.customerid', $thisCustomerid )
    ->leftJoin( 'table_active', 'table_active.id', '=', 'table_products.active' )   
    ->process( $_POST )
    ->json();

colin

This section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin

webpointz

I've tried all of those options with no success. The same query run in MySQL takes 4 seconds to load.

Here's my JS:

/*
 * Editor client script for DB table table_products
 * Created by http://editor.datatables.net/generator
 */

var editor; // use a global for the submit and return data rendering in the examples

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        "ajax": "php/table.table_products.php",
        "table": "#table_products",

        "i18n": {
            "edit": {
            "button": "Edit Product",
            "title":  "Update Product",
            "submit": "Update Product"
            },
            "create": {
            "button": "Add Product",
            "title":  "Add New Product",
            "submit": "Add Product"
            }}, 
        
        "fields": [
            {
                "label": "Product ID:",
                "name": "table_products.productid"
            },
            {
                "label": "Name:",
                "name": "table_products.productname"
            },
            {
                "label": "Description:",
                "name": "table_products.description",
                "type": "textarea"
            },
            {
                "label": "Grouping:",
                "name": "table_products.grouping"
            },
            {
                "label": "Stock Warning:",
                "name": "table_products.stockwarning",
                "def": "0"
            },
            {
                "label": "Equipment:",
                "name": "table_products.equipment"
            },
            {
                "label": "Miscellaneous:",
                "name": "table_products.miscellaneous"
            },
            {
                "label": "Notes:",
                "name": "table_products.notes",
                "type": "textarea"
            },
            {
                "label": "Unit Cost:",
                "name": "table_products.unitcost",
                "def": "0"
            },
            {
                "label": "Unit Measurement:",
                "name": "table_products.unitmeasurement"
            },      
            {
                "label": "Active:",
                "name": "table_products.active",
                "type": "select",
                "def" : "1"
            },
            {
                "label": "UPC code:",
                "name": "table_products.upc_code"
            }               
        ]
    } );
    
       
    function callModal(aData) {
        $("#productid").val(aData);
        $("#productIdHere").text(aData);
        $("#myModal").modal({
        persist: true       
            });
    }

    function callModalItems(aData) {
        //alert(aData);
        var name = aData;        
        if (name) {
            window.location = 'add-product-items.php?id=' + name;
        }       
    }

    var table = $('#table_products').DataTable( {
        "dom": "Tfrtip",
        "deferRender": true,
        "ajax": "php/table.table_products.php",
        "order": [[ 1, 'asc' ]],
        "columns": [
            {
                "data": "table_products.id"
            },
            {
                "data": "table_products.productid"
            },
            {
                "data": "table_products.description"
            }           
        ],
        
        "tableTools": {
            "sRowSelect": "os",
            "aButtons": [
                { "sExtends": "editor_create", "editor": editor,
                  "sButtonText": "New Product" },
                { "sExtends": "editor_edit",   "editor": editor,
                  "sButtonText": "Edit Product" },
                {
                    "sExtends": "select_single",
                    "sButtonClass": "marginLeft",
                    "sButtonText": "Add Image",
                    "fnClick": function () {
                        if (this.fnGetSelected().length===1) {
                        var aData = table.cell('.selected', 1).data();
                        callModal (aData);
                        }
                    }
                },
{
                    "sExtends": "select_single",
                    "sButtonClass": "marginLeft",
                    "sButtonText": "Product Items",
                    "fnClick": function () {
                        if (this.fnGetSelected().length===1) {
                        var aData = table.cell('.selected', 0).data();
                        callModalItems (aData);
                        }
                    }
                }                                                 

            ]
        }
    } );
} );

}(jQuery));

allan

tableTools

Wow - that's old! What version of DataTables are you using? I guess TableTools will work with the current versions, but it was replaced with Buttons and Select in 2015 or thereabouts.

That won't effect the speed though. Can you give me a link to your page so I can take a look and see why it is taking so long please? It could be download time, data retrieval from the database, slow rendering or anything else. I'd need a test case to understand what's going wrong.

Thanks,
Allan

webpointz

Sorry for the late reply. It was the database. I upgraded to the latest version of MariaDB and it's good now.