Optimization issues in multitier architecture w/ PDO queries

Question

I've been working on a PHP project for about 6 month where I'm using a multitier architecture (Data Access Layer , Business Logic Layer, ...).

One of the page of my website has a really long loading time, so I investigated and I realised it was the function from a BLL file that took a longer time to process.

Here's a example of a function in my DAL (here called DAL_Releve) files :

public static function getReleveByAffectationID($id){
    $conn = MgtConnexion::getConnexion();

    $sql = "SELECT *"
        . "FROM releve "
        . "WHERE Affectation_ID = :id "
        . "AND Date_Releve >= :date ;";

    $req = $conn->prepare($sql);
    $req->bindValue(":id", $id);
    $req->bindValue(":date", $_SESSION["year"]-1 ."-01-01");
    $req->execute();

    MgtConnexion::closeConnexion($conn);

    return $req;
}

Here's a example of a function in my BLL (here called MgtReleve) files :

public static function getReleveByAffectationID($id){

    $req = DAL_Releve::getReleveByAffectationID($id);

    $releveArray = array();

    while($row = $req->fetch(PDO::FETCH_ASSOC)){
        $releveArray[] = new releve($row);
    }

    return $releveArray;
}

As you can see, I return the PDOStatement object $req from the DAL and then fetch it in the BLL.

My problem is here : In my interface file, I'm looping through a array of Affectation and each of them have Releves (so each Releves have an attribute Affectation_ID), like this:

$lesAff = MgtAffectation::getAllAffectation(); 
//This doesn't return an array of objects but just an Array of arrays like : $array[<number>]["key"] = value;

foreach ($lesAff as $affect){
    $lesReleves = MgtReleve::getReleveByAffectationID($affect["Affectation_ID"]);
    foreach ($lesReleves as $rel){
        //DO STUFF HERE
    }
}

FYI : The page loads in 6.622 seconds with the code above and in 0.014 seconds if I remove everything inside the first foreach (except for html code which is not showed here).

Now I'm starting to question my understanding of the multitiers architecture :

If I decide not to fetch $req in my BLL file but in my interface (to loop only once in my queries results instead of twice), what's the purpose of the BLL then if only to return the PDOStatement object? Is there another way to return the data with more efficiency ?

and what are the numbers? how much "affectations" and "releves" you have here? do you really need them all? — Your Common Sense, Nov 14 '17 at 13:43
@YourCommonSense Oh I thought the numbers were irrelevant but here you go : I have 67 Affectations and each have approximately 20 Releves. Yes, I need them all : it's a administration page where someone else have to check each affectations and releves, she asked me not to separate those in pages, since she wants to search any line using `Ctrl + F` — Natty, Nov 14 '17 at 13:49
Well, given it takes 0.01 sec for one query you are lucky it takes only 6 seconds, not 12 for 1200 — Your Common Sense, Nov 14 '17 at 14:00

score 2 · Accepted Answer · answered Nov 14 '17 at 14:07

2

There are many areas of optimization:

Technological optimization: instead of connecting to the database every time you are running a query, you should connect only once and then use this connection through the entire application. I wrote an article on the common errors of Database wrappers, you may find it interesting to read, and especially the part on the database connection.
Architectural optimization. It seems that you could benefit from lazy loading when instead of selecting database rows one by one you could do it in batches. Check this answer on how to run a PDO query with multiple ids at once. So it will take you to run only 68 queries instead of 1200
Database optimization. Make sure that all queries involved are running fast.

answered Nov 14 '17 at 14:07

Your Common Sense

156,878
40
214
345

Thank you for your help, my problem was that I created a PDO connection for each queries... I've added the parameter `PDO::ATTR_PERSISTENT => TRUE` to my PDO object, wich increased the loading speed by a lot. Although, it's still new to me : Does it always prevent the creation of a new connection ? Because I still call the functions that create and delete the PDO Object in each queries. – Natty Nov 15 '17 at 10:06
This lazy solution with persistent connection is a HUGE land mine under your site. I guess that when it go live and pick up some users you will start getting "Too many connections" error, because, you can guess, your site is creating too many connections this way – Your Common Sense Nov 15 '17 at 10:09

Optimization issues in multitier architecture w/ PDO queries

1 Answers1