Loading from Large Database Table with 3 Million Records!

Thu, 28 Sep 2017 12:21:15 +0000

The total record count is usually one of the most expensive database operations due to sequential scan reading information of every row. When the table is very large, the performance takes a huge hit.

With a large database, we can simply disable the counting operation by calling enable_pagecount() and pass parameter “false”.

1	$dg->enable_pagecount(false);

Below is the entire code for the demo. Note that the table salaries has nearly 3 million records!

1
2
3
4
5
6
7
8
9

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/path/to/conf.php");

$dg = new C_DataGrid("SELECT * FROM salaries", "emp_no", "salaries")
$dg->enable_edit('INLINE')->set_dimension('800px');
$dg->enable_pagecount(false);
$dg->enable_export(true);
$dg -> display();

In general, to improve overall performance of very large database tables, it is advisable to:

Use either an INT or BIGINT datatype than say a GUID
Partitioning may reduce the size of indexes, effectively reducing the table size
More memory or even SSD drive can work wonders!
Remove any unnecessary indexes on the table
Remove SELECT COUNT(*) and always include a query filter.

Run large database live demo (3 Million Rows)!

The post Loading from Large Database Table with 3 Million Records! appeared first on phpGrid - PHP Datagrid.

enable_pagecount()

Thu, 28 Sep 2017 12:20:32 +0000

* This function is available in commercial licenses.

Parameters:
- $page_count: boolean: true or false.
Description:
- Enable page count in the pager. Default to true. Set to false to optimize performance for very large (e.g. millions) database table.
Example:

1	$dg -> enable_pagecount(false);

The post enable_pagecount() appeared first on phpGrid - PHP Datagrid.

Large dataset Archives | phpGrid - PHP Datagrid

Loading from Large Database Table with 3 Million Records!

enable_pagecount()