I have trouble deciding what would be the optimal solution for my web app, which is accessing (mainly reading) many times the same user data on every session.
Should I retrieve all user data at once (about 40 fields) to $_SESSION when opening a new session or should I keep a persistent PDO (mysql) connection and query just the parameters I need from the database on every script execution instead?
ALSO:
Would there be a big difference in performance between reading/updating a lot of fields at once (whith a custom query) or one by one (with a custom combination of generic queries) in the same transaction? e.g.
$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));
$fieldlist='';
foreach ($fields as $i=>$field){
$fieldlist.=$field['name'].':field'.$i.',';
}
rtrim($fieldlist,',');
$dbh->prepare("UPDATE user SET ".$fieldlist." WHERE name=:name");
foreach ($fields as $i=>$field){
$stmt->bindValue(':field'.$i, $field['value'], PDO::PARAM_STR);
}
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$dbh = null;
vs.
$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));
$dbh->beginTransaction();
foreach($fields as $field){
$stmt=$dbh->prepare("UPDATE user SET ".$field['name']."=:field WHERE name=:name");
$stmt->bindValue(':field', $field['value'], PDO::PARAM_STR);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
}
$dbh->commit();
$dbh = null;
Would there be a big difference in performance
I would say there is a difference in common sense.
Why to repeat one by one while you can do it at once? Is there any reason to write more code for the same task?
It seems you're looking for troubles not where they really are.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With