Comparing Files and Databases with PHP Benchmarking Applications - Reading data from a sample database table (
Page 2 of 4 )
To continue testing the benchmarking capabilities offered by PHP, I will first perform a simple comparison between fetching rows from the sample "USERS" database table that you saw in the previous article and retrieving the same data, but this time coming from a flat text file.
In doing so, it'll be possible to determine which approach is faster, depending on the benchmarking conditions (remember that all the examples will be tested on a local server). Given that, first let me show you the script that fetches rows from the mentioned database table in conjunction with the corresponding classes used by the code snippet listed below. Take a look, please:
// define 'Timer' class
class Timer{
private $elapsedTime;
// start timer
public function start(){
if(!$this->elapsedTime=$this->getMicrotime()){
throw new Exception('Error obtaining start time!');
}
}
// stop timer
public function stop(){
if(!$this->elapsedTime=round($this->getMicrotime()-$this->elapsedTime,5)){
throw new Exception('Error obtaining stop time!');
}
return $this->elapsedTime;
}
//define private 'getMicrotime()' method
private function getMicrotime(){
return microtime(true);
}
}
// define 'MySQL' class
class MySQL{
private $conId;
private $host;
private $user;
private $password;
private $database;
private $result;
const OPTIONS=4;
public function __construct($options=array()){
if(count($options)!=self::OPTIONS){
throw new Exception('Invalid number of connection parameters');
}
foreach($options as $parameter=>$value){
if(!$value){
throw new Exception('Invalid parameter '.$parameter);
}
$this->{$parameter}=$value;
}
$this->connectDB();
}
// connect to MySQL
private function connectDB(){
if(!$this->conId=mysql_connect($this->host,$this->user,$this->password)){
throw new Exception('Error connecting to the server');
}
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
}
}
// run query
public function query($query){
if(!$this->result=mysql_query($query,$this->conId)){
throw new Exception('Error performing query '.$query);
}
return new Result($this,$this->result);
}
}
// define 'Result' class
class Result {
private $mysql;
private $result;
public function __construct($mysql,$result){
$this->mysql=$mysql;
$this->result=$result;
}
// fetch row
public function fetchRow(){
return mysql_fetch_assoc($this->result);
}
// count rows
public function countRows(){
if(!$rows=mysql_num_rows($this->result)){
throw new Exception('Error counting rows');
}
return $rows;
}
// count affected rows
public function countAffectedRows(){
if(!$rows=mysql_affected_rows($this->mysql->conId)){
throw new Exception('Error counting affected rows');
}
return $rows;
}
// get ID form last-inserted row
public function getInsertID(){
if(!$id=mysql_insert_id($this->mysql->conId)){
throw new Exception('Error getting ID');
}
return $id;
}
// seek row
public function seekRow($row=0){
if(!is_int($row)||$row<0){
throw new Exception('Invalid result set offset');
}
if(!mysql_data_seek($this->result,$row)){
throw new Exception('Error seeking data');
}
}
}
try{
// instantiate 'Timer' class
$timer=new Timer();
// start timer
$timer->start();
// connect to MySQL
$db=new MySQL(array
('host'=>'host','user'=>'user','password'=>'password','database'=>'database'));
$result=$db->query('SELECT * FROM users');
while($row=$result->fetchRow()){
echo 'ID: '.$row['id'].' Name: '.$row['name'].' Email: '.$row['email'].'<br />';
}
// stop timer
$elapsedTime=$timer->stop();
// display elapsed time
echo 'Time spent in fetching database rows was '.$elapsedTime.' seconds';
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
/* displays the following:
ID: 1 Name: user1 Email: user1@domain.com
ID: 2 Name: user2 Email: user2@domain.com
ID: 3 Name: user3 Email: user3@domain.com
ID: 4 Name: user4 Email: user4@domain.com
ID: 5 Name: user5 Email: user5@domain.com
ID: 6 Name: user6 Email: user6@domain.com
ID: 7 Name: user7 Email: user7@domain.com
ID: 8 Name: user8 Email: user8@domain.com
ID: 9 Name: user9 Email: user9@domain.com
ID: 10 Name: user10 Email: user10@domain.com
Time spent in fetching database rows was 0.00544 seconds
In this case, you can see that the process for retrieving the ten rows stored in the above "USERS" database table took approximately 0.0054 seconds. The program demonstrates how easy it is to benchmark a particular script by using the "Timer" class listed above.
Well, now that you know how long it took to retrieve the previous rows from the respective database table, let's find out if the above performance test can be improved by retrieving the same records from a flat file. Sounds pretty interesting, doesn't it?
Therefore, if you want to learn how this benchmarking test will be performed, jump into the following section and keep reading.
