The Repetitive Database Access



  • I've started noticing a pattern on the rather large PHP/Perl (yeah) application I'm supporting now.

    There's a class called DBAccess that is used to connect to the database (which is PostgreSQL) and perform queries. The class is something pretty simple and doesn't do much. Now, one would think that one instance of DBAccess would be enough for all our needs. That connecting once to the database is the best approach. But not the original developer of this... thing. (who, btw, is also my boss)

    $getData1 = new DBAccess(DBSERVER1);
    $getData1->setSQL($sqlQuery1);
    $getData1->Open();
    $getData1->disconnect();
    // Do something with $getData1
    
    $getData2 = new DBAccess(DBSERVER1);
    $getData2->setSQL($sqlQuery2);
    $getData2->Open();
    $getData2->disconnect();
    // Do something with $getData2
    
    $getData3 = new DBAccess(DBSERVER1);
    $getData3->setSQL($sqlQuery3);
    $getData3->Open();
    $getData3->disconnect();
    // Do something with $getData3
    
    $getData4 = new DBAccess(DBSERVER1);
    $getData4->setSQL($sqlQuery4);
    $getData4->Open();
    $getData4->disconnect();
    // Do something with $getData4
    
    // ... snip ...
    
    $getData12 = new DBAccess(DBSERVER1);
    $getData12->setSQL($sqlQuery12);
    $getData12->Open();
    $getData12->disconnect();
    // Do something with $getData12


  • I've seen this exact pattern before.



  • On Windows, that would be pretty close to the correct pattern (correct would be each in its own block and disposed properly). Connection pooling would ensure that at most one connection is really opened, and constantly releasing allows other threads or even applications to share the connection.



  • @Jaime said:

    On Windows, that would be pretty close to the correct pattern (correct would be each in its own block and disposed properly). Connection pooling would ensure that at most one connection is really opened, and constantly releasing allows other threads or even applications to share the connection.

    True, but in PHP/Perl it's an absolute disaster. This is less of a WTF if they're using persistent connections because then the connections will pool (although pconnect has its own problems and I would always avoid using it.) Even in that case, they should be using some kind of factory so the DBAccess object itself isn't constructed from scratch for every single query.

    Edit: nevermind, persistent connections would probably make this worse.


Log in to reply