A Few Tips for Speeding Up PHP Code - Consolidate Queries

[ganeshbala]

Consolidate Queries

Consider an application that lists some information about a service quote and then displays an editable grid of line items for that quote.  This is a common enough scenario in thick applications, in which users change what info they want and don't have to worry about submitting  after changing each row. I was charged with building such an application for the Web. In order to update the grid data, I have to keep track of the field names for each row by prepending a unique identifier to each field within the row. And when I'm actually processing the results, I have to generate a separate UPDATE statement for each existing row, a DELETE statement for any rows whose delete checkboxes have been checked, and an INSERT statement for a new row if applicable. That's on top of updating the general information about the quote. On large quotes, this can amount to 60 or 80 queries.

There are two ways of handling these queries. We can execute each one with a separate call to the appropriate _query() function or we can concatenate them into one string and send them all with one call to the _query() function, cutting down some overhead that occurs with each call to the _query() function. The examples below use my database library, but they're sufficiently concise and clear that they should be easy to follow and replicate within your own libraries. The following code block connects, selects a database, and executes 200 queries one at a time with individual calls to the query() function. On my system, this executes in about 225 milliseconds.

$db=new DB("server","username","password","database","sybase");
$db->connect();
$db->usedb("database");

for($i=0; $i<200; $i++){
  $sql = "UPDATE

SET [Field]='" . $i . "' WHERE [No.]='50000003';\n";   $db->query($sql); } $db->disconnect(); The block below connects, selects a database, and creates a single string composed of 200 queries. It then sends that string to the server in one call to the query() function. It takes about 92 milliseconds to execute. $db=new DB("server","username","password","database","sybase"); $db->connect(); $db->usedb("database"); for($i=0; $i<200; $i++){   $sql .= "UPDATE SET [Field]='" . $i . "' WHERE [No.]='50000003';\n"; } $db->query($sql); $db->disconnect(); The overhead differential in this example in particular is fairly minimal in terms of actual milliseconds saved, but when you're trimming fat to speed up a resource hog of an application, every little bit helps. It's always nice to make something run more than twice as fast as it did before. One caveat: if you send too much info in the concatenated string, some of your queries might fail. I imagine this happens because the buffer fills up and queries after the "full" point are truncated and not executed. If you know you'll be sending a lot of long queries using this method, you might want to consider pushing them onto an array and joining segments of the array to send smaller batches of queries rather than adding to a string.