mysqli_stmt wrapper performing worse than mysql_ functions

[RogierP]

Hey everyone,

 

I'm running into a performance issue with a database wrapper class which is supposed to make working with the mysqli extension a little easier. According to the benchmark on this site: http://www.johnjawed.com/benchmarks/, mysqli prepared statements ought to outperform both mysql_query and mysqli_query, so naturally, I decided to go with mysqli_stmt.

 

However, using prepared statements requires you to bind the result set to variables which will the be 'filled' upon calling the fetch method in your mysqli_stmt object. Since I want to simulate the mysql_fetch_assoc function which has always served me well, I have to come up with something that will bind an arbitrary number of variables, depending on the amount of columns in the result set. I found some examples on the php.net site in the comments under the mysqli_stmt::fetch function reference, and added it in my ResultSetMysqli class like this:

 

<?php
class ResultSetMysqli extends ResultSet
{
/* The prepared statement and various state and information variables. */
protected $stmt;
protected $isclosed;
protected $numrows;
protected $metadata;
protected $results;
protected $bindVarArray;

/**
 * Function:	__construct (constructor)
 * Return:		none
 * Arguments:	A valid mysqli_stmt object on which 'execute' has already been called.
 * Description:	Constructs a new MySQLi Result Set.
 */
public function __construct( $stmt )
{
	$this->isclosed = false;
	$this->results = array();
	$this->bindVarArray = array();

	/* Now we call the initialization method . */
	$this->parse( $stmt );		
}

/**
 * Function:	__destruct (destructor)
 * Return:		none
 * Arguments:	none
 * Description:	Destroys the result set, closing the prepared statement as well.
 */
public function __destruct()
{
	$this->release();
}

/**
 * Function:	parse
 * Return:		none
 * Arguments:	The statement object to get data from
 * Description: 'Parses' (or simply: gets) metadata from the prepared statement.
 */
protected function parse( $stmt )
{
	/* Set the stmt variable. */
	$this->stmt  = $stmt;

	/* Check if there is metadata (whether it was a select query, in other words). */
	if ( ( $this->metadata = $this->stmt->result_metadata() ) == NULL )
	{
		$this->hasSet = false;
		$this->numrows = $this->stmt->affected_rows;
		return;
	}

	/* The result is a set, thus update the $hasSet variable. */
	$this->hasSet = true;
	$this->numrows = $this->stmt->num_rows;

	/* We only need to bind the variables once, so we might as well do it here. */
	$fields = $this->metadata->fetch_fields();
	foreach ( $fields as $field )
	{
		$this->bindVarArray[] = &$this->results[$field->name];
	}
	call_user_func_array( array( $this->stmt, "bind_result" ), $this->bindVarArray );
}

/**
 * Function:	release
 * Return:		none
 * Arguments:	none
 * Description:	Closes the current prepared statement, releasing its resources.
 */
public function release()
{
	if ( !$this->isclosed )
		$this->stmt->close;

	$this->isclosed = true;
}

/**
 * Function:	getNumRows
 * Return:		An integer
 * Arguments:	none
 * Description:	Based on the type of query, this will either return the number of
 *				affected rows (INSERT, UPDATE, DELETE) or the number of rows in the
 *				result set (SELECT).
 */
public function getNumRows()
{
	return $this->numrows;
}

/**
 * Function:	fetchAssoc
 * Return:		One row as an associative array.
 * Arguments:	none
 * Description:	Fetches one row of data from the buffered mysqli_stmt. Use this method as if you
 *				were using mysql_fetch_assoc.
 */
public function fetchAssoc()
{	
	/* If the result is not a set, return false. */
	if ( !$this->hasSet ) return false;

	if ( $this->stmt->fetch() != NULL )
		return $this->results;
	else
		return NULL;
}		

/**
 * Function:	fetchObject
 * Return:		A row as an object.
 * Arguments:	none
 * Description:	Uses fetchAssoc to construct an object representation of the fetched row.
 */
public function fetchObject()
{
	$var = $this->fetchAssoc();
	return ( $var == NULL ? false : (object)$var );
}
}?>

I've listed the entire class as the use of fetchAssoc() also depends on actions executed earlier in the parse method. This class won't work on itself, rather it is an object that gets returned from the query method of another database class. It is therefore safe to assume that mysqli_stmt::execute and mysqli_stmt::store_results have already been called before this class is instantiated. Now, using the following testscript, I get the weird result that my wrapper class actually performs worse than the standard mysql_ functions (with mysql_query + mysql_fetch_array performing nearly twice as fast):

 

<?php
require_once "../backend/database/database.class.php";

$db = Database::get( Database::DB_MYSQLI );
$db->connect( "localhost", "root", "" );
$db->setDatabase( "redrepublic" );

$starttime = microtime( true );

$res = $db->query( "SELECT account_id FROM forums_posttracker" );
while ( $r = $res->fetchAssoc() );	

echo ( microtime( true ) - $starttime ) . "<br /><br />";

$db->disconnect();

$con = mysql_connect( "localhost", "root", "" );
mysql_select_db( "redrepublic", $con );	

$starttime = microtime( true );


$query = mysql_query( "SELECT account_id FROM forums_posttracker" );
while ( $result = mysql_fetch_array( $query ) );	

echo ( microtime( true ) - $starttime );

mysql_close( $con );
?>

 

The following lines in this code snippet use the class posted earlier:

$res = $db->query( "SELECT account_id FROM forums_posttracker" );
while ( $r = $res->fetchAssoc() );

Where $res is an instance of the ResultSetMysqli class.

 

I suspect something in my own code is taking up precious processing time because when I try the same snippet, only without using my wrapper class but the raw mysqli extension, the results actually are as they should be.

 

Thanks in advance for any help

[RogierP]

bump