In my code, I have an abstract class with methods for retrieving and updating database tables. There is a child class for each table in my database.
One thing my abstract class requires from each child is an array containing all the column names of its table. I can acquire this programmatically with a database query:
$stmt = $db->prepare("
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '" . static::TABLE_NAME . "'
AND TABLE_SCHEMA = 'default_db'
AND generation_expression = ''
AND column_name != 'old_id'
");
$stmt->execute();
When I first created this structure, I ran this query inside of __construct(), resulting in a ->columns array in every object instance. Speeds were acceptable, but memory usage was high, causing a lot of "This webpage was reloaded because it was using significant memory" messages.
My response to this was to hard-code a ::COLUMNS array as a class constant in every child class, as below:
Class Address extends Dataset {
const TABLE_NAME = 'addresses';
const PRIMARY_KEY = 'address_id';
const COLUMNS = array(
'address_id',
'line1',
'line2',
'city',
'state',
'zip',
'zip4',
);
// other class methods
}
I didn't really test performance on this one. Every arduous minute of typing out information that every instinct told me was better retrieved programmatically caused me existential pain. This could have been the peak performance in terms of memory and speed, but it was far from DRY, and more importantly, vulnerable to errors as columns change over time. To that end, I kept the PDO query at the top of this post in __construct(), but only to check the database columns against the class constant and generate a warning if they didn't match.
However, the problems with this kept gnawing at me, until I considered a static method in the abstract class as a solution. So now I have public static function get_columns() that returns the results of the same old PDO query.
The problem is that the added database queries are enough to make my site's loading speeds SLOW. My first method loaded information-dense pages in under 1 second; this new method takes 5 - 10.
What is the best way to minimize memory usage, database queries, and superfluous code while achieving the same result?
I have dealt with this exact problem. What I read in your question is that you retrieve the columns for every object that you instantiate. That is very costly. It might even stop you from instantiating a new object, because you fear it would make your site even slower.
The solution I came up with was caching. A very simple form of caching:
class MyClass
{
private static array $columnsInfo = [];
public function getColumnsInfo(string $database,
string $tableName): array
{
// when the info doesn't exist we need to create it
if (!isset(self::$columnsInfo[$tableName])) {
$data = $database->query("SHOW COLUMNS FROM `$tableName`;");
foreach ($data as $column) {
$info[$column['Field']] = $column;
}
self::$columnsInfo[$tableName] = $info;
}
return self::$columnsInfo[$tableName];
}
}
I only use it when I need it, it's not stored in every object.
This is, of course, not my exact code, but the idea is simple: Have somewhere central to store the columns information of each table, and only retrieve it once from the database.
Note that $tableName should never come from an user, in any way. One just cannot bind table names.
I use caching a lot, really a lot, because it does wonders. Of course, a database does use caching as well, and it is pretty good, but nothing beats caching something yourself. I keep track of cache hits and misses and I can reach a hit / miss ratios of 10 to 20. That means, for instance, that for every 100 queries 90 are retrieve from memory, and since this memory is central it doesn't even take up that much space.