The Downside of Iterative External Resource Calls within a Loop

In order to produce an array or hash of information, frequently we need to get this data from external sources such as a databases, socket connections to a data stream or a files from disk. In doing so, we might end up iterating calls to any of these resources resulting into latency issues, too many unnecessary connections and spawns of garbage in the backend. I call this practice of writing code, "Iterative External Resource Calls".

Let's examine the following database call. Note that I used PHP to make an example but this is applicable to any programming language.



Assuming that $db is an instance of a SQL database adapter object with "query" method to process any type of SQL queries and return an array of results.

$results = array();
for($i = 0; $i < 50; $i++) {
    $sql = "SELECT * FROM TABLE WHERE ID = {$i}";
    $records = $db->query($sql);
    $results[] = $records[0];
}

Obviously, the code above will make a new database call every iteration. And regardless if it's using persistent connection or not, latency and performance issues will be observed. This is because each call spawns a new thread and similar meta information is passed through the network each iteration thus consuming both machine and network CPU resources.

So, considering the above, it should be written this way instead.

$results = array();
$sql = "SELECT * FROM TABLE WHERE ID > 0 AND ID < 50"
$results = $db->query($sql);

The above code will only make one database call but will produce the same results as the previous. This saves both network bandwidth, memory and CPU resources.

In conclusion, you must evaluate your code first and see if you can avoid an iterative external resource call. I'm pretty sure there should be a lot of ways to do it.

Related Links:

• Related Links Widget for Blogspot