PHP OOP Efficient DB Read Reduction

Question

Six years ago I started a new PHP OOP project without having any experience so I just made it up as I went along. Anyway, I noticed that my rather powerful mySQL server sometimes gets bogged down far too easily and was wondering what the best way to limit some db activity, when I came up with this, as an example...

private $q_enghours;
public function gEngHours() {
    if ( isset($this->q_enghours) ) {
    } else {
        $q = "SELECT q_eh FROM " . quQUOTES . " WHERE id = " . $this->id;
        if ($r = $this->_dblink->query($q)) {
            $row = $r->fetch_row();
            $r->free();
            $this->q_enghours = $row[0];
        }
        else {
            $this->q_enghours = 0;
        }
    }
    return $this->q_enghours;
}

This seems like it should be effective in greatly reducing the necessary reads to the db. If the object property is populated, no need to access the db. Note that there are almost two dozen classes all with the same db access routines for the "getter". I've only implemented this change in one place and was wondering if there is a "best practice" for this that I may have missed before I re-write all the classes.

Let's start with the fact that your code is 100% open to SQL injection (bind your variables, do not paste them in the SQL!). — h2ooooooo
– h2ooooooo, Commented Sep 27, 2015 at 6:20
While a valid point, that's not really applicable to the question I asked. I have SQL injection handled in a different part of the application. — DevlshOne
– DevlshOne, Commented Sep 27, 2015 at 23:08

Your Common Sense · Accepted Answer · 2015-09-27 06:34:48Z

2

I'd say that this question is rather based on wrong premises.

If you want to deal with "easily bogged down" database, then you have to dig up the particular reason, instead of just making guesses. These trifle reads you are so concerned with, in reality won't make any difference. You have to profile your whole application and find the real cause.

If you want to reduce number of reads, then make your object to map certain database record, by reading that record and populating all the properties once, at object creation. Constructors are made for it.

As a side note, you really need a good database wrapper, just to reduce the amount of code you have to write for each database call, so, this code can be written as

public function gEngHours() {
    if ( !isset($this->q_enghours) ) {
        $this->q_enghours = $this->db->getOne("SELECT q_eh FROM ?n WHERE id = ?", quQUOTES,  $this->id);
    }
    return $this->q_enghours;
}

where getOne() method is doing all the job of running the query, fetching row, getting first result from it and many other thinks like proper error handling and making query safe.

edited Sep 27, 2015 at 6:34

answered Sep 27, 2015 at 5:18

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DevlshOne Over a year ago

I'm not a DBA, so I don't have the necessary knowledge to drill into mySQL logs and find out where my root cause is. I've tweaked every parameter I could find good information on. As for the db wrapper, I'd still be doing a complete rewrite.. just to eliminate code that's already written.

DevlshOne Over a year ago

And, yes, there are several places where I fetch the entire object at once but for places where I only need specific information about the object, I use these classes.

Collectives™ on Stack Overflow

PHP OOP Efficient DB Read Reduction

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related