Mobile Development Tips: Preventing Unbounded Result Sets

An issue that too many times goes overlooked is the possibility that database queries return more data (rows) than what your application can handle.

It is typical for applications to send a query to the database and process each row of the result set. Often times, this processing is accompanied by the creation of a data structure (list, collection, etc.) that holds a one or more business objects derived from the query results.

Unbounded result sets are a threat to your mobile application

Do you know what would happen if your mobile application sent a query to the database and received a million rows instead of a hundred? Or if it made a request to a web service and received a collection with ten thousand objects?  A cause of countless problems on application servers, unbounded result sets are also a threat to your mobile applications. Given the memory and processing constraints in mobile devices, paying attention to this issue is of particular importance.

Result sets without bounds are more likely to occur when your application makes a request without specifying what type of response it can accept and how much of it can process. You are also asking for trouble when your application blindly trusts the systems it calls, database server included.

An ounce of prevention…

So, what are some of the measures you can take to prevent the occurrence of unbounded result sets?

Limit the number of retrieved rows. If you have control over the data access code, limit the number of rows you retrieve in your queries. Remember Murphy’s_Law. Even when you know that a table will never have more than fifty rows, write your SQL code so it retrieves no more than fifty rows.

-- MySQL

select client_id, client_name from clients

limit 50

Limit the number of elements in retrieved collections. Design your web services routines so they accept a parameter indicating the maximum number of elements you are prepared to process.

int maxNumberOfClients = 50;

List<Client> clients = service.GetClients(maxNumberOfClients);

Break out of loops. When looping over elements of a result set or a collection, break out when you reach the maximum number of elements you can process. Although this measure does not avoid the retrieval of all the results, it stops the pernicious effects from propagating to other areas of your application.

int clientsCount = 0;

int maxClients = 50;

foreach (DataRow clientRow in clientsTable.Rows) {

    Client client = Client.FromDataRow(clientRow);

    clientsList.Add(client);

    clientsCount ++;

    if (maxClients == clientsCount ) break;

}

Conclusion

I hope you find this helpful. As usual, let me know your thoughts!