Filtering Lists

Filtering Lists: In Database or in Our Java Code?

Often when programming it is often necessary to filter and sort lists.  Often by default the database is relied on for this filtering and sorting.  However, this may not always be the most efficient way to write code.

The reason we rely on the database to sort and filter data is because all of the code to do so already there because we are retrieving the data from the database in the first place.  It is simple to add a "where clause" to filter or an "order by" to sort, and databases are very inefficient at filtering and sorting this data. This is definitely a database job.

However, there are many cases where it is better to retrieve the data from the database one time and to filter and sort the data from that point onward directly in the Java code, or any front end code for that matter.

When Filtering Should be Done in the Code and not the Database

Her are some simple examples based on code used in PrimeAgile CMS.

One List Filtered Based on a Selection from Another List

In this example, initially all topics are available, and all categories are available.  However, when a category is selected, the topics is limited to topics that match or are available in the specified category.  In this case the entire data set is initially returned from the database. When writing the code it is really simple to have the database queried again on each selection of a category.  However, it is faster and more efficient to avoid multiple trips to the database.  Since all of the data is available originally, when the page loads, it is most efficient to just filter that list in java as the categories are changed, rather than re-querying the data.

An image Set or Page List is Filtered Based on Categories, Keywords, or Tags.