dalecosp;11019943 wrote:
a Singleton-patterned DB.class to my app in hopes that I'd save time by reusing the same connection instance.
The next thing I added was a "logging" class; because lots of logs will be written, it writes to one of several different DBs on the same server.
But, it's also using the Singleton DB instance.
Performance/speed has dropped ten-fold or more.
Well, without code it's impossible to say why it's slow. Log class doing too many inserts on index-heavy tables? Actually not reusing connections, due to an error, and instead creating a new connection every time? Etc
dalecosp;11019943 wrote:
and doing a lot of DB switching
Are you saying that you are using one connection resource in your singleton and actually reconnecting to different databases, thus changing the resource in the singleton? There should be no DB switching. One connection -> always goes to the same DBMS. Two connections -> always goes to the same two DBMSs. Or possibly both to the same DBMS with different privs of course.
I'd create a class that can deal with all needed connections. There are basically two ways of doing this. One is to create new instances of the class itself, where each instance keeps the connection resource as a member property. The other is to only use the class as a means of accessing refernces to PDO (or whatever you use) instances.
bradgrafelman;11019987 wrote:That's why I wouldn't view such a class as a DB connection, but rather a DB manager. The same manager instance could, for example, maintain two separate connections to the DB - one for general purpose, and one for logging.
But in my view, even a db manager wouldn't be keeping logic to decide which connection is to be used for what things, at least not internally after the call to a factory / getter function. The logger may tell the db it wants the "logger_connection", and the db returns something (connection, self instance, whatever), but this is as far as the manager should be managing stuff. If different things are supposed to be logged in different places, I'm betting my money that a log manager would know this, thus possibly breaking the logger into a logger and a logmanager. If you later want to change where / how things are logged, you change that in the LogManager, which means the LogManager can still keep using the dbmanager to get connections to relevant dbs, while using new dbs, file system or whatever for some logs. If you want to change what's logged and how, you change the logger class, which still uses the logmanager.
Not sure what the first way would be called, but it keeps references to itself, in a registry-type way, making it in essence a registry of singletons - a Multipleton?
class DbManager
{
# factory / registry getter
#
# If you in other classe want to be able to set a db connection in the constructor:
# $this->db = DbManager::connect('some_db');
# and then use $this->db on subsequent member method calls for that instance,
# then return by reference is needed.
#
# If you always (in each call to a member method) start with
# $db = DbManager('some_db');
# Then return by value is ok (can drop the &).
public static &connect($dbms)
{
if (isset(self::$c[$dbms]))
{
return self::$c[$dbms];
}
# option 1: return an instance of THIS class. Keep the pdo instance in a member property.
# See constructor below
self::$c[$dbms] = new self($dbms);
return self::$c[$dbms];
}
# Constuctor only callable through public connect function
private function __construct($dbms)
{
$dsn = self::$dbms[$dbms];
$user = self::$user[$dbms];
$pass = self::$pass[$dbms];
$this->db = new PDO($dsn, $user, $pass);
}
}
While the second approach is simply a registry of PDO instances.
class DbRegistry
{
public static &connect($dbms)
{
if (isset(self::$c[$dbms]))
{
return self::$c[$dbms];
}
$dsn = self::$dbms[$dbms];
$user = self::$user[$dbms];
$pass = self::$pass[$dbms];
# option 2: create no instance of this class. just keep references to PDO instances
self::$c[$dbms] = new PDO($dsn, $user, $pass);
return self::$c[$dbms];
}
# hide constructor to prevent instantiation
private function __construct() {}
}
Certainly, each call to a member method takes a little bit of time, which you would avoid if you managed your connections yourself and passed them around as needed. But enough to slow your program to a halt? I doubt it.
dalecosp;11019975 wrote:But that would be so UN-21st century! What would I do with my big logger class? :p 😃
Well, would you really need the file system for temporary storage or could you hold the information in ram? Or at least retain logging info in ram for a while and not insert on every log message, but on every 10 messages if that improves speed?
But do note that with approach one above, i.e. returning instances of DbManager, you could also easily keep several statement objects
public function prepare($q, $stmt_name = 'stmt') {
if (isset($this->stmt[$stmt_name])) {
$this->stmt[$stmt_name]->closeCursor();
}
# PDO may throw, or will not throw, depending on the db driver used iirc.
# Thus, you may want to try/catch this
$this->stmt[$stmt_name] = $this->db->prepare($q);
}
public function execute($args = array(), $stmt_name = 'stmt') {
# Definitely add error handling here
$this->stmt[$stmt_name]->execute($args);
}
public function fetchAll($stmt_name = 'stmt' $fetch_type = PDO::FETCH_ASSOC) {
return $this->stmt[$stmt_name]->fetchAll($fetch_type);
}
so you can easily avoid having to parse the same query several times.
$db_one->prepare('SQL QUERY here', 'log1');
$db_one->prepare('SQL QUERY here', 'log2');
$db_one->prepare('SQL QUERY here', 'log3');
$db_one->execute($params, 'log1');
# other code here
# later - new parmas
$db_one->execute($params, 'log1');
$db_one->execute($params_two, 'log2');
# other code here
# later - new parmas
$db_one->execute($params, 'log1');
