A Few Tips for Speeding Up PHP Code - Filter Database Results
(Page 2 of 5 )
There are several ways to fetch database results, depending on your database server type and your PHP configuration. For example, using the sybase drivers to connect to an MS SQL server, my PHP version will let me use the sybase_fetch_array() function but not the sybase_fetch_assoc() function. Sybase_fetch_array() returns both text and numeric indices for each row returned, however, effectively doubling the data I have to pass around when processing the results. In order to reduce the amount of data I have to work with, I added a function to my database library that lets me choose which type of index I wish to use -- text, numeric, or both. Consider the following code:
$db=new DB("server","username","password","database","sybase");
$db->connect();
$db->usedb("database");
$db->set_return("both");
foreach($db->fetch("SELECT TOP 200 * FROM [mainview]") as $row){
print_r($row);
print "<br><hr><br>\n";
}
$db->disconnect();
Forget for a moment that I'm using a database library you don't have access to. It's plain enough that I'm connecting to a database, invoking the "set_return()" function, and then looping through an array of row arrays to print the results. My [mainview] happens to be a view containing some 300 columns. Multiply that times 200 and we're working with a fair amount of data. Now the default behavior of my database class is to return both numeric and text indices, and it consistently takes about 55,000 milliseconds to run this query. Only about 8% of that time is spent actually pulling results from the database; the remaining 92% is devoted to processing the results. But when I invoke 'set_return("text")', the results are somewhat improved. Total runtime is about 41,000 milliseconds, with 40% of the time spent in retrieving results and 60% processing them; and when I set the return to "numeric," the results are even better: 31,000 milliseconds with a 20/80 split. So what did I do to increase performance?
I wrote a function within my database library that checks the value of the member variable "return_type," runs through the row results, and returns all numeric or text indices based on the value of that member variable:
function format_results($rows){
//$rows is assumed to be a multi-dimensional
//array composed of arrays of row results.
if($this->return_type=="both" || !$this->return_type){ return $rows; }
$newrows=array();
foreach($rows as $r){
$vals=array();
//Get all array keys from the row of row arrays passed to function.
$keys=array_keys($r);
//For each key, check return type and set vals[$key] to the appropriate modulo value.
for($i=0; $i<sizeof($keys); $i++){
switch($this->return_type){
case "numeric":
if($i%2==0){
$vals[$keys[$i]]=$r[$keys[$i]];
}
break;
case "text":
if($i%2==1){
$vals[$keys[$i]]=$r[$keys[$i]];
}
break;
//Default case, just return $rows as it was passed.
default:
$i=sizeof($keys) + 5;
return $rows;
}
}
//Push reformatted single row array onto array of row arrays.
array_push($newrows,$vals);
}
return $newrows;
}
The result of adding this function is of course that we spend more time formatting the results before returning them to the calling function, but we spend less time processing the results after the fact. In other words, by weeding out duplicate values once up front, we dodge working with twice the data in any loops in our calling script and we stand to save significant time. If your PHP config is such that you can opt to use functions that will return only numeric or text indices, you may not need to write such a function. The lesson, though, is that you should get only the type of results you'll actually be working with. So if you're using text indices only in your code and you happen to be able to use sybase_fetch_assoc(), be sure you use it rather than using the more top-heavy sybase_fetch_array(). I also benchmarked this on a mysql database with similar results.
Next: Consolidate Queries >>
More PHP Articles
More By Daryl Houston
