Php5 Class Performance

[JustinK101]

Recently I ran some tests running a loop of 250 MySQL inserts and 250 MySQL updates, 500 queries total.

 

The first test I used my nice Database abstraction classes, and a DatabaseQueries class. The test took 59 seconds on 3 occasions.

 

Then I ran the exact same test and queries this time running all code within the loop, for example building the sql string and passing it to mysql_query(). No external classes. This time 29 seconds on 3 occasions.

 

The first method, which is better programming practice and makes much cleaner code is SIGNIFICANTLY slower though. I did not expect this, is this consistent with what others have seen?

[corbin]

Sounds about right to me if you were reopening/creating everything.  OOP has lots of advantages, but it's heavier than procedural programming.  (Technically that could be a lie, but hopefully no one will come along wanting to nit-pick that statement.)

 

Can you show us the test code?

[JustinK101]

Well, not going to lie, my classes are heavy and everything broken out into separate classes, but I did it because the project is a very big web application, but I also don't want performance to suffer horribly. But, I suppose I can just offload the php processing to additional servers if I run into performance problems. Here are fragments of the classes I used though. WARNING, may be confusing, but this code is solid and well written.

 

DatabaseConfiguration.php

abstract class DatabaseConfiguration {
	const host = "localhost";
	const database = "db";
	const username = "dev";
	const password = 'pass';
	const port = 3306;
}

 

DatabaseConnection.php

class DatabaseConnection {
	private static $instance;

	private $db_connection;

	private $previous_query_affected_rows;

	private $numb_queries = 0;

	private $host_information;

	private $server_version;

	private function __construct() {}

	public static function singleton() {
		if(self::$instance == null) {
			self::$instance = new self;
		}
		return (self::$instance);
	}

	public function connect() {
		$this->db_connection = @mysqli_connect(DatabaseConfiguration::host, DatabaseConfiguration::username, DatabaseConfiguration::password, DatabaseConfiguration::database, DatabaseConfiguration::port);

		if(empty($this->db_connection)) {
			Error::show("DATABASE-CONNECTION-1", "#" . mysqli_connect_errno() . " - " . mysqli_connect_error());
		}

		$this->host_information = mysqli_get_host_info($this->db_connection);
		$this->server_version = mysqli_get_server_info($this->db_connection);
	}

	public function get_db_connection() {
		return ($this->db_connection);
	}

	////
	// Ex: Localhost via UNIX socket	
	////
	public function get_host_information() {
		return ($this->host_information);
	}

	////
	// Ex: 5.0.51a-community	
	////
	public function get_server_version() {
		return ($this->server_version);
	}

	public function smart_quote($sql) {
	   if (get_magic_quotes_gpc()) {
		   $sql = stripslashes($sql);
	   }
	   
	   if($sql == "NULL") {
	   	  return ($sql);
	   }
	   
	   if (!is_numeric($sql)) {
		  $sql = "'" . mysqli_real_escape_string($this->db_connection, $sql) . "'";
	   }
	   return ($sql);
	}

	public function query($sql) {
		$this->numb_queries++;
		$result = mysqli_query($this->db_connection, $sql);
		$this->previous_query_affected_rows = mysqli_affected_rows($this->db_connection);
		return ($result);	
	}

	public function get_previous_query_affected_rows() {
		return ($this->previous_query_affected_rows);
	}

	public function fetch_object($result) {
		return (mysqli_fetch_object($result));
	}

	public function numb_rows($result) {
		return (mysqli_num_rows($result));
	}

	public function error() {
		return ("#" . mysqli_errno($this->db_connection) . " - " . mysqli_error($this->db_connection));
	}

	public function get_numb_queries() {
		return ($this->numb_queries);
	}

	public function close() {
		if(!mysqli_close($this->db_connection)) {
			Error::show("DATABASE-CONNECTION-2", $this->error());	
		}
	}
}

 

DatabaseQueries.php

class DatabaseQueries {
	private static $instance;

	private function __construct() {}

	public static function singleton() {
		if(self::$instance == null) {
			self::$instance = new self;
		}
		return (self::$instance);
	}

	public function test_query1() {
		$sql = "SQL CODE HERE";
			  	 
		$result = DatabaseConnection::singleton()->query($sql) or Error::db_show("test_query1", DatabaseConnection::singleton()->error(), $sql);
		return ($result);
	}
}

 

[corbin]

In your testing code, were you creating the object each time you did a query, or did you use a single object?

 

 

Edit:  Oh, you might want to remove your SQL details, by the way.

[JustinK101]

Well its a singleton, so looked like:

 

for($i = 0; $i < 250; $i++) {
    DatabaseQueries::singleton()->test_query1();
    echo "query: " . $i;
}

[JustinK101]

In your testing code, were you creating the object each time you did a query, or did you use a single object?

 

 

Edit:  Oh, you might want to remove your SQL details, by the way.

 

Yeah originally I posted them on accident, DUHHHHH! I have changed the password for that user though now. Thanks for looking out.

[corbin]

I must say 0.118 seconds a query is bad x.x.

 

Just of curiosity, does the update statement use an index?

 

 

 

Anyway, there are a few things you could do to optimize your code:

 

 

1.  Make your class lazy.  You never know what information you are going to need, but why fetch all of the information every time?  Why not wait until your class asks for it?  Example:

 

//instead of
public function query($sql) {
	$this->numb_queries++;
	$result = mysqli_query($this->db_connection, $sql);
	$this->previous_query_affected_rows = mysqli_affected_rows($this->db_connection);
	return ($result);	
}

you could do

public function query($sql) {
	++$this->numb_queries;
	//++var is faster than var++.  It has to do with copying stuff....  Google it if you want the details.
	//this wouldn't make even a noticeable difference probably, but it's a habit of mine
	$this->result = mysqli_query($this->db_connection, $sql);
	return $this->result;
	//return is a language construct, therefore, it doesn't need parenthesis, and the parenthesis actually slow down execution
}

public function affected_rows() { 
    return mysqli_affected_rows($this->db_connection);
}

 

 

2.  Why the DatabaseQueries class?  Seems kinda unfair as far as benchmarks go. Why not just:

 

for(blah) {
    $sql = "SQL CODE HERE";
    $result = DatabaseConnection::singleton()->query($sql) or Error::db_show("test_query1", DatabaseConnection::singleton()->error(), $sql);
}

 

Also, are you assigning the mysql_query result to a variable?  If not, that's another unfair thing.

 

 

 

Oh, by the way, echo'ing can take quite a bit of time.  If you try the code without the echo 'query: ' part, it might be a little faster.

 

Takes my computer .04 seconds to do:

 

php -r "$s = microtime(true); for($i = 0; $i < 250; ++$i) { echo 'query: ' . $i; } $st = microtime(true); echo PHP_EOL . ($st - $s);"

[corbin]

Bored, so I just did some benchmarks my self.  Your class and mysql_* functions are a couple hundredths of a second different for 1000 queries.

 

 

Here's my full code (I modified your class a tiny bit):

 

<?php

abstract class DatabaseConfiguration {
const host = "localhost";
const database = "bm_test";
const username = "root";
const password = 'root';
const port = 3306;
}

class DatabaseConnection {
private static $instance;

private $db_connection;
private $previous_query_affected_rows;

private $numb_queries = 0;

private $host_information;

private $server_version;
private $result;

private function __construct() {}

public static function singleton() {
	if(self::$instance == null) {
		self::$instance = new self;
	}
	return (self::$instance);
}

public function connect() {
	$this->db_connection = @mysqli_connect(DatabaseConfiguration::host, DatabaseConfiguration::username, DatabaseConfiguration::password, DatabaseConfiguration::database, DatabaseConfiguration::port);

	if(empty($this->db_connection)) {
		Error::show("DATABASE-CONNECTION-1", "#" . mysqli_connect_errno() . " - " . mysqli_connect_error());
	}

	//$this->host_information = mysqli_get_host_info($this->db_connection);
	//$this->server_version = mysqli_get_server_info($this->db_connection);
}

public function get_db_connection() {
	return ($this->db_connection);
}

////
// Ex: Localhost via UNIX socket	
////
public function get_host_information() {
	return ($this->host_information);
}

////
// Ex: 5.0.51a-community	
////
public function get_server_version() {
	return ($this->server_version);
}

public function smart_quote($sql) {
   if (get_magic_quotes_gpc()) {
	   $sql = stripslashes($sql);
   }
   
   if($sql == "NULL") {
   	  return ($sql);
   }
   
   if (!is_numeric($sql)) {
	  $sql = "'" . mysqli_real_escape_string($this->db_connection, $sql) . "'";
   }
   return ($sql);
}

public function query($sql) {
	if(!$this->db_connection)
		$this->connect();

	++$this->numb_queries;
	$this->result = mysqli_query($this->db_connection, $sql);
	//$this->previous_query_affected_rows = mysqli_affected_rows($this->db_connection);
	return $this->result;	
}

public function get_previous_query_affected_rows() {
	return ($this->previous_query_affected_rows);
}

public function fetch_object($result) {
	return (mysqli_fetch_object($result));
}

public function numb_rows($result) {
	return (mysqli_num_rows($result));
}

public function error() {
	return ("#" . mysqli_errno($this->db_connection) . " - " . mysqli_error($this->db_connection));
}

public function get_numb_queries() {
	return ($this->numb_queries);
}

public function close() {
	if(!mysqli_close($this->db_connection)) {
		Error::show("DATABASE-CONNECTION-2", $this->error());	
	}
}
}


class DatabaseQueries {
private static $instance;
      
private function __construct() {}

public static function singleton() {
	if(self::$instance == null) {
		self::$instance = new self;
	}
	return (self::$instance);
}
            
public function test_query1() {
	$sql = "SQL CODE HERE";

	$result = DatabaseConnection::singleton()->query($sql) or Error::db_show("test_query1", DatabaseConnection::singleton()->error(), $sql);
	return ($result);
}
}

 

 

Then my two benchmarks:

 

$START1 = microtime(true);
//DatabaseConnection::singleton()->query($sql) or Error::db_show("test_query1", DatabaseConnection::singleton()->error(), $sql);
for($i = 0; $i < 1000; ++$i)
DatabaseConnection::singleton()->query("INSERT INTO bm_test VALUES ('a')") or Error::db_show("test_query1", DatabaseConnection::singleton()->error(), $sql);

$END1 = microtime(true);

echo $END1-$START1;

DatabaseConnection::singleton()->query("DELETE FROM bm_test"); 

 

$START2 = microtime(true);

$db = mysql_connect('localhost', 'root', 'root');
if(!$db) {
//blah
}
else {
mysql_select_db('bm_test', $db);
for($i = 0; $i < 1000; ++$i)
	mysql_query("INSERT INTO bm_test VALUES ('a')", $db);
}

$END2 = microtime(true);

mysql_query("DELETE FROM bm_test");

echo $END2-$START2;

[JustinK101]

Humm wonder what the slowdown is then for me? Perhaps my webhost? I am currently using a standard shared webhost, but using a dedicated mysql server at slicehost. The webserver/php app then communicates to the MySQL server. The web host might be bogged down and not getting great php performance. I say this because the MySQL slice while running the queries is using at most 12% cpu util, so clearly not MySQL hitting the roadblock, but PHP.

 

Though I must admit, my queries are a lot of complex then a simply insert, but still that can be factored out of the equation, since both cases were using the same exact queries.

[JustinK101]

Corbin,

 

Thanks for the suggestions, I modified my DatabaseConnection class to remove the extra calls for number of previous affected rows, and server information in the connect. Also, now my query function looks like:

 

public function query($sql) {
		++$this->numb_queries;
		return mysqli_query($this->db_connection, $sql);
	}