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.

Load Data with Vertical Scroll

Thu, 16 Sep 2010 00:26:45 +0000

In contrast to traditional pagination method to browse through data, you can use vertical scroll as alternative way to load data. It is done by set parameter in set_scroll() to true. When scroll position changes, phpGrid makes ajax call in the background and refresh the content in the grid. As a result, pagination is disabled automatically when this method is used.

Note that it does not load everything at once and only hold all the items from the start through to the latest point ever visited. This improves page loading time while prevents memory leaks from happening.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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

$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");

// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");

// set export type
$dg -> enable_export('EXCEL');

// enable integrated search
$dg -> enable_search(true);

// set height and weight of datagrid
$dg -> set_dimension(800, 600);

// increase pagination size to 40 from default 20
$dg -> set_pagesize(40);

// use vertical scroll to load data
$dg -> set_scroll(true);

$dg -> display();

See Live Example!

The post Load Data with Vertical Scroll appeared first on phpGrid - PHP Datagrid.

Load data Archives | phpGrid - PHP Datagrid

Loading from Large Database Table with 3 Million Records!

Load Data with Vertical Scroll