Reducing Database Calls on a Web Page

One of the things I always try and implement at the code design stage when developing web pages and applications is keeping the number of database calls to an absolute minimum. In an ideal world this should always be just once on every page, but in reality this is not always possible. There are several techniques to minimise the number of calls made to a database on a web page and below I have given an example of how to combine queries to improve site speed and reduce server overhead significantly.

The analogy I use for this technique is ‘McDonalds’. Consider this, when you order food at your nearest drive-thru, Do you pick up a burger, go home, drive back order some fries, return home, drive back to McDonalds and grab a coke ?  Answer: No, you order burger, fries and coke and take them home at the same time because that is the most efficent way of doing things otherwise you end up having to make three round trips wasting petrol and time (resources) not to mention the fact your food would be cold by the time you got to eat it. So why should querying a database be any different given that every call to a database eats up valuable server resources and takes time. When dealing with web pages it is important to think in terms of nano seconds rather than just seconds.

Below is a code snapshot showing a simple filter form that retrieves three drop down lists from a database.

<form action="results.php" method="post">
<legend>Find</legend>
<p><label>Region</label><select name="region">
<?
echo "<option value=\"\">Select</option>";
    
$query = mysql_query("SELECT DISTINCT region_id,region FROM region ORDER BY region ASC");
while($rs = mysql_fetch_object($query)){
      echo "<option value=\"".$rs["region_id"]."\">".$rs["region"]."</option>";}
?>
</select></p>
<p><label>Job</label><select name="job">
<?
echo "<option value=\"\">Select</option>";
    
$query = mysql_query("SELECT DISTINCT job_id, job_type FROM job_types ORDER BY job_type ASC");
while($rs = mysql_fetch_object($query)){
     echo "<option value=\"".$rs["job_id"]."\">".$rs["job_type"]."</option>";}
?>
</select></p>
<p><label>Position</label><select name="position">
<?
echo "<option value=\"\">Select</option>";
    
$query = mysql_query("SELECT DISTINCT position_id, position FROM positions ORDER BY position ASC");
while($rs = mysql_fetch_object($query)){
     echo "<option value=\"".$rs["position_id"]."\">".$rs["position"]."</option>";}
?>
</select></p>
<p><input type="submit" name="action" value="Search"/></p>
</form>

Now there is nothing particularly wrong with the code; it works and it should return all the information fairly quickly for one user by querying the database three times on the page. But what if there were 100 concurrent users making 300 calls to the database, could the server cope ? Is there a better way of doing things – could we get all the information from the database in one go, and reduce the number of database calls by two thirds?

Look at the revised code below, it does exactly the same as the code above, but by making use of an SQL UNION query it combines three datasets into one. The secret to using this technique is making sure you apply a unique identifier to each dataset in this case a numeric value ‘typ’ which is always the first sort field, so when you loop through it to build your drop down boxes you know the order of each dataset will be as expected i.e 0,1,2.

<form action="results.php" method="post">
<legend>Find</legend>
<?
//Join all the drop down queries into one query using UNION ALL.
//Specify an identifier (typ) to ensure the sort order is correct
$typ   = "";
$query = mysql_query("(SELECT DISTINCT 0 AS typ,'Region' AS title,region_id AS option_value,
region AS option_text FROM region) 
UNION ALL (SELECT DISTINCT 1,'Job',job_id, job_type FROM job_types) 
UNION ALL (SELECT DISTINCT 2,'Position',job_id, job_type FROM job_types) ORDER BY typ,option_text ASC");
while($rs=mysql_fetch_object($query))
    {
    //Create a new drop down box 
    if ($typ!=$rs["typ"]){
        echo ($typ!=""?"</select></p>\n":""), //Close existing drop down
        "<p><label>".$rs["title"]."</label><select name=\"".strtolower($rs["title"])."\"><option value=\"\">Select</option>";
	 $typ=$rs["typ"];}
    
    //Loop through the drop down options
    echo "<option value=\"".$rs["option_value"]."\">".$rs["option_text"]."</option>";
    }
echo "</select></p>\n"; //Close the last drop-down box
?>
<p><input type="submit" name="action" value="Search"/></p>
</form>

Follow Up
Since posting this article, there has been quite a lot of debate so it seemed sensible to clarify one or two things.

The whole focus of the article was to show a way of combining relatively small queries and avoid unnecessary database requests for small amounts of data. It is not meant to be a one size fits all solution, and may not suit every application. It is the sort of thing that could be particularly useful on a shared hosting platform where the database is not running on the localhost and handling a lot of requests.

The type of database connection used in the example was not really relevant to the article but given that PHP’s mysql API is now deprecated perhaps we should have used mysqli instead.

Nothing seems to spark debate amongst PHP coders like outputting HTML in PHP tags and it almost feels like being accused of herecy for doing so. Whilst it may increase memory usage slightly there does not seem to be any concrete evidence that it is so bad a thing to do now and again and personally I find it can be cleaner solution in some circumstances.

The use of a UNION ALL query in our query should not really effect performance as it is just combining three relatively small datasets and sorting them. Whilst I agree that UNION queries can kill performance on large datasets because of the need for temporary indexes to remove duplicates, in this example I think it is ok especially given that the goal is to only make one trip to the database and the combined query being returned will be relatively small.