I begin my question with a description of a user requirement.
I need to show a table of records. The records are formed by information from various models (by relational AR).
The webpage also has option to sort (from dropdown list) and change no of records per page (from dropdown list).
I uses AR model to retrieve the data. I uses CPagination and CSort but I realize that each request is a database call to retrieved the data.
My question is
Can I hold the retrieved data in a certain place (cache, session) so that subsequent requests (eg. paging, to sort by a certain value) can just manipulate the data and I dont have to make database call?
In using Yii, what should be proper design to store state data between requests?
It is totally fine to perform a SQL call each time when sorting by a certain column.
Do not complicate your design by prefetching all the results. If the performance is found as a problem later on, you can use data caching techniques to cache the query results based on the SQL statements.
It is also a very Very VERY VERY bad idea to select every record from the Database and then just manipulate and sort it with the PHP.
This kind of design logic causes a HUGE bottle neck on the PHP side. PHP is very slow when it comes to managing large amounts of data (when compared perl, for example). The database server, on the other hand, is very fast at managing this same amount of data (order of magnitudes faster than PHP).
Let the database do it’s job of data retrieval and sorting, then let PHP do it’s job of data parsing and display. If this database call begins to cause trouble in the future, make sure your Indexes are set up properly and look into query caching techniques. But if you have enough load on the DB server that a properly formed select and sort query is causing issues, chances are that moving the load to PHP would cause even more problems.
