Yii ArrayDataProvider and lots of recordsby Ascendro 12.06.2014, updated 12.06.2014
In one of our applications we have a page listing patients of a practice. As the practices grew their business, we noticed a slowdown when loading that page.
The initial implementation was using a CArrayDataProvider with the data retrieved using an SQL query. Therefore the slowdowns' cause was that on every page load the data was retrieved from the database, and after only the 10 needed results were taken out and displayed. The solution was replacing the implementation with CSqlDataProvider :
// Create a new CSort object $sort = new CSort(); // One attribute for each column of data $sort->attributes = array( 'nameLine', 'phoneNumber', 'dateOfBirth', 'credit', 'balance' ); // Set the default order $sort->defaultOrder = array( 'nameLine' => CSort::SORT_ASC, ); return $dataProvider = new CSqlDataProvider($sql, array( 'id' => 'patients', 'sort' => $sort, 'totalItemCount' => $itemCount, //'caseSensitiveSort' => false, 'pagination' => array( 'pageSize' => 10, ), ));
Notice that 2 queries are executed : the count query (SELECT COUNT(id) ...) and the actual query that retrieves the 10 records (Yii adds the LIMIT and OFFSET to the SQL query automatically, depending on the current page and the pageSize parameter.
In our case, with over 70.000 records, the page load difference was huge, from 2.3 seconds to a couple hundred milliseconds.
This can be a great optimization if you initially implemented dataproviders using CArrayDataProvider.