Page 1 of 1

OOP and massive database reading

Posted: Tue Aug 25, 2009 3:46 pm
by lorenzo-s
Hi everyone. I'm Lorenzo, from italy (sorry for my bad english) :mrgreen:

I've worked with PHP for years, using only procedural programming, writing lots of functions and storing DB records in arrays. Now I want to start working with objects, but I have some perplexities.

Let's suppose that I have to develop a large community, where a single page can require the reading of hundreds of user records from the database. Until now, I stored records in an array of arrays, and I used simple functions to manipulate them: no problems. But now i have the User class, with lots of properties and lots of methods (load, save, edit, delete, get_friends, and so on). So, when I read users from the database, it's obvious to create an array of User objects.

Will the PHP program became too heavy? This method to work with DB and objects can affect performance significatively? I think that instantiate hundreds of objects will be much heavier than create hundreds of associative arrays... What do you think? How do you work with DB and classes? Is my worry vain?

Thank tou all,
Lorenzo. :roll:

Re: OOP and massive database reading

Posted: Tue Aug 25, 2009 4:35 pm
by Darhazer
Working with array of arrays and array of objects is the same... The difference in memory used and performance won't be significant, if the objects are implemented correctly. Using objects you can implement lazy loading, improving your memory usage.

Just to give a clue... obviously the User (or other object) have to be able to load itself from the database, but when you need to load lot of such objects, you have to provide alternative initialization. I mean you read the records with one query, and then for each row you create an instance of the object, passing it to the row. In this way you won't have x queries to load x instances of the object.

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:34 am
by Christopher
My first question is: Why are you loading hundreds of user records at once?

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 5:06 am
by lorenzo-s
arborint wrote:My first question is: Why are you loading hundreds of user records at once?
For example, I want to print the "user list" page, with 100 users per page. Maybe on the left there is a chart of the 10 most popular users, and a box with your 20 friends... Don't know...
Darhazer wrote:Just to give a clue... obviously the User (or other object) have to be able to load itself from the database, but when you need to load lot of such objects, you have to provide alternative initialization. I mean you read the records with one query, and then for each row you create an instance of the object, passing it to the row. In this way you won't have x queries to load x instances of the object.
Yes, It was not my intention to launch a DB query for each user. I thought about something like:

Code: Select all

class User{
    function __construct($id_or_row) {
        if (is_array($id_or_row)) {
            // Copy properties from the $row
        } else {
            // Query for the user with ID = $id
            // Fill in user object properties
        }
    }
} 
And then:

Code: Select all

    $users = array();
    $res = mysql_query("SELECT * FROM users LIMIT 0, 100");
    while ($rec = mysql_fetch_assoc($res)) $users[] = new User($rec);
Another idea comes from discovering this function, mysql_fetch_object(), and its optional $class_name parameter.
$class_name: The name of the class to instantiate, set the properties of and return. If not specified, a stdClass object is returned.
So, what is the result if I do something like???

Code: Select all

    $users = array();
    $res = mysql_query("SELECT * FROM users LIMIT 0, 100");
    while ($obj = mysql_fetch_object($res, 'User')) $users[] = $obj;
I think I will do some tests, watching execution time and memory usage...

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 11:06 am
by Darhazer
The properties will be public, and mysql_fetch_object is not intended to do OOP stuff..

For listing users, maybe you have to take a look at Table Data Gateway pattern.
OOP frameworks usually have some class to deal with recordset / arrays of objects instead of single objects, at it's really common, especially if you build user management in the admin panel.

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 11:38 am
by lorenzo-s
Umh, I understood... But I will make a great mistake if I use one of the methods I explained? I say the alternative constructor or the mysql_fetch_object? I want to keep all simple: a class for User, and nothing more. Properties can be public, for direct access. I will be the only programmer, so I don't need these things. I want to avoid frameworks and MVC pattern (it can be correct and useful, but I think it's a little intricate for simple projects).

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 1:13 pm
by Eran
For example, I want to print the "user list" page, with 100 users per page. Maybe on the left there is a chart of the 10 most popular users, and a box with your 20 friends... Don't know...
Then you should limit your query to the 100 users you want to fetch. And a separate query for getting the 20 friends etc. It is very important to limit the results of queries - not just from the PHP side, but the database as well will experience heavy load. 100 rows is not a lot for PHP to handle

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:31 pm
by Darhazer
lorenzo-s wrote:Umh, I understood... But I will make a great mistake if I use one of the methods I explained? I say the alternative constructor or the mysql_fetch_object? I want to keep all simple: a class for User, and nothing more. Properties can be public, for direct access. I will be the only programmer, so I don't need these things. I want to avoid frameworks and MVC pattern (it can be correct and useful, but I think it's a little intricate for simple projects).
I don't think it's great mistake, it's depends of the size and complexity of what you are doing. Of course, always keep the separation of data and presentation... you can skip the controller part for small projects.

I've also started from PHP 4 and my first OO implementation was:

Code: Select all

 
class Item
{
    public function __construct($id, $data = array())
    {
        // if $data is passed, load it, else if $id is passed load from database
    }
 
    public function load()
    {
       // load from database
    }
 
    public function save()
    {
       // saves to database, based on $id determines if should insert or update
    }
 
    public function delete($id = null)
    {
       // deletes a record
    }
    
 
    public static funciton getAll($start, $end)
    {
       // returns array of Item[] objects, prepopulating the data
    }
}
pytrin wrote:Then you should limit your query to the 100 users you want to fetch. And a separate query for getting the 20 friends etc. It is very important to limit the results of queries - not just from the PHP side, but the database as well will experience heavy load. 100 rows is not a lot for PHP to handle
Look at his code, there is a limit ;)

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:32 pm
by Eran
It's interesting you used PHP5 syntax in PHP4.. did it work?
Look at his code, there is a limit
yeah, I saw that.. not sure what the problem is then. 100 rows is nothing major

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:33 pm
by Darhazer
pytrin wrote:It's interesting you used PHP5 syntax in PHP4.. did it work?
I meant I started working with PHP 4 and my first PHP 5 code was... :oops:

P.S. Of course there is classes in PHP 4 as well, and we've used them just to couple the data with the code that uses it... we never though in objects actually, and the class was "Page" with listUsers(), addUser(), etc... there were no classes for each entity/unit

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:34 pm
by Eran
oh :lol:

well it looks nice (somewhat similar to active-record)

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:36 pm
by Darhazer
pytrin wrote:oh :lol:

well it looks nice (somewhat similar to active-record)
Yeah, and I read about Active Record 2 years later :)

Re: OOP and massive database reading

Posted: Wed Aug 26, 2009 3:59 pm
by lorenzo-s
Thank you Darhazer for all of your tips. All I can do now is some tests.

It's obvious I will try to keep separation from data and presentation. I wanted to use array of objects (instead of printing user infos in a more performant while ($user = fetch()) statement) just because I want to get all the data before and then pass it to Smarty template engine, that will do all things about HTML.

About your simple Item class, it's exactly the way I want to work. Little time ago I realized some classes of the same type in C# in order to work with items from a database (SQLite in that case), and I saw how much this approach is powerful :mrgreen: