Page 1 of 1
Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 11:52 am
by matthijs
Ok, I know there's probably no single "best" way, but still I'm interested in your thoughts:
when you have a web app dealing with datasets you want to show in different forms on the pages. And then not one kind of display on one page, but multiple displays per page.
To give a concrete example: say you have some page which shows different stats on your (hundreds or thousands) of users. In maybe 10 or 20 different tables and diagrams. Do you query the db once, retrieve the users data and use that single dataset and process it in PHP to show the different views (diagrams, etc)? Or do you have a method behind each diagram which gets it's own data, meaning for one page load the db might be queried 20 times?
I guess for reusability and flexibility separate methods (and thus queries) are better. But on the other side, querying the db 20 times for almost exactly the same data seems redundant..
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 1:29 pm
by Eran
What I usually do in cases like this is create an internal cache in the class that fetches the data and that let it handle successive calls to distribute the data according to the parameters. For example:
Code: Select all
class Stats extend SomeDbAbstraction {
/**
* Internal cache
* @var array
*/
protected $_data = null;
/**
* Prime the cache if empty and return view data by key
* @param mixed $viewKey (integer/string)
* @return array
*/
public function getData($viewKey) {
if(is_null($this -> _data)){
$data = $this -> _db -> query($complicatedSqlString);
$this -> _data = $this -> handleSet($data);
}
if(isset($this -> _data[$viewKey]) {
return $this -> _data;
}
return false;
}
/**
* Manipulate database result set for the different statistics views
* @param array $data
* @return array
*/
protected function handleSet(array $data) {
//Manipulate result set to prepare it for the different views
return $data;
}
}
You can also create additional methods with descriptive names (such as getFrontpageStatistics() ) that internally return the right view data.
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 1:34 pm
by inghamn
In my stuff, I load the data in the controller. Then, pass the data to each of the view partials that would render it however they like.
That way, the data's only loaded from the database once.
Code: Select all
$stats = new UserData();
$template = new Template();
$template->blocks[] = new Block('userGraph',array('stats'=>$stats));
$template->blocks[] = new Block('userDiagram',array('stats'=>$stats));
$template->blocks[] = new Block('userVennDiagram',array('stats'=>$stats));
echo $template->render();
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 1:45 pm
by Christopher
I think it depends on where the page is. If it is a page that will get some traffic I might do some optimization, cache, etc. But if it is a administrative report that is really not run that often then I would code it in the DRYest, most modular, most obvious way and just let it be a little slow and use more resources. I still say code it with the clearest, cleanest design and then optimize later. As pytin shows, there are many clever ways to optimize while maintaining an clean interface.
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 2:34 pm
by matthijs
Thanx for the great feedback! Some good ideas.
The internal cache in a class holding the data might be a good idea. I'll have to think about it some more, it'll probably depend on the specific situation and what data I need. But most often it'll be a single dataset and then a few ways of viewing that and a few subsets of that dataset.
Say user's A delicious links. You'd want to get his links from the db (the main dataset), then post a list of his most recent links, a list of his top links, the top posted category links, etc etc, all subsets of that first dataset.
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 2:55 pm
by inghamn
I would treat those all as different datasets. I mean, you don't want to select * from table, and then try and filter through all that in PHP. You want to select only the data from the database that you want.
In other words, you don't want to select all the user's links, only to write PHP code to sort through the results to what you consider "Top Links" or "Recent Links". Only select the "Top Links" to begin with.
Writing functions for things like this in your User model would go a long way. I try and write my models with functions that make controller code easy. I like to end up with functions that can be called like:
Code: Select all
$user = new User($user_id);
$recentLinks = $user->getMostRecentLinks(4);
$topLinks = $user->getTopLinks(10);
For my stuff, in cases like this, I find it's easier just to pass the User object to the view, and let the view call the functions directly from the model. (Although I know there's quite a debate about whether views should pull data or not).
Anyway, a controller might look like:
Code: Select all
$user = new User($user_id);
$template = new Template();
$template->blocks[] = new Block('users/topLinks',array('user'=>$user));
echo $template->render();
And a topLinks view partial might look like
Code: Select all
<ul>
<?php
foreach($this->user->getTopLinks(10) as $link)
{
echo "<li><a href=\"$link->getURL()\">$link</a></li>";
}
?>
</ul>
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 3:07 pm
by Kieran Huggins
I'd keep a lot of the logic in the DB, even at the expense of extra queries. SQL engines are far more optimized towards sorting large datasets than PHP.
Try it both ways and see whats faster.
Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 3:21 pm
by matthijs
Ok, those are good arguments. Certainly from the coding view it's easier to spend some time writing one "complicated" query to get a specific dataset, then to write that same logic in php.
Somewhat related to this: what do you think about using javascript on the client side to handle datasets? In a project of mine I retrieve the total dataset for a user and then use a pretty cool jQuery plugin to make the large table sortable and allow for pagination. Maybe the first query is a bit slower (although that probably will be negligible until you're really talking large sets), but then on the client side it's really really fast and snappy.
It's also neatly degradable when someone has no js. He just gets a very large table

Re: Best way to handle larger datasets in the view
Posted: Thu Jun 12, 2008 3:26 pm
by VladSun
matthijs wrote:Maybe the first query is a bit slower
I think that it is not the SQL query execution slow, but ... data transfer from DB server to web server, PHP parsing, data transfer to the client, browser rendering and Javascript execution.
Imagine a user with a 1GHz laptop ...