PHP Database connection practice
- by Phill Pafford
I have a script that connects to multiple databases (Oracle, MySQL and MSSQL), each database connection might not be used each time the script runs but all could be used in a single script execution. My question is, "Is it better to connect to all the databases once in the beginning of the script even though all the connections might not be used. Or is it better to connect to them as needed, the only catch is that I would need to have the connection call in a loop (so the database connection would be new for X amount of times in the loop).
Yeah Example Code #1:
// Connections at the beginning of the script
$dbh_oracle = connect2db();
$dbh_mysql = connect2db();
$dbh_mssql = connect2db();
for ($i=1; $i<=5; $i++) {
// NOTE: might not use all the connections
$rs = queryDb($query,$dbh_*); // $dbh can be any of the 3 connections
}
Yeah Example Code #2:
// Connections in the loop
for ($i=1; $i<=5; $i++) {
// NOTE: Would use all the connections but connecting multiple times
$dbh_oracle = connect2db();
$dbh_mysql = connect2db();
$dbh_mssql = connect2db();
$rs_oracle = queryDb($query,$dbh_oracle);
$rs_mysql = queryDb($query,$dbh_mysql);
$rs_mssql = queryDb($query,$dbh_mssql);
}
now I know you could use a persistent connection but would that be one connection open for each database in the loop as well? Like mysql_pconnect(), mssql_pconnect() and adodb for Oracle persistent connection method. I know that persistent connection can also be resource hogs and as I'm looking for best performance/practice.
