sort Strings on app or database side?

[Stephen Huey]

We have some queries that use ORDER BY, and our database guy is telling us to take them out and do them on the web app side to cut down on Oracle 10g's use of the TEMP table. Here's an example of a frequently run query that populates a quick access select box of their address book entries:

SELECT ReceiverID FROM ADDRESSBOOK WHERE AccountNbr = ? ORDER BY UPPER(ReceiverID)

We realized that ORDER BY is probably redundant there since the pair key consists of the AccountNbr and ReceiverID, so since it'll use the index they'll come across already sorted. However, when you go searching, you have queries like this, and even more complicated ones as well:

SELECT ReceiverID, CompanyName FROM ADDRESSBOOK WHERE AccountNbr = ? AND UPPER(CompanyName) LIKE UPPER(?)
 ORDER BY UPPER(CompanyName)

In that case, we need to sort, either in the database or on the application side. Typically, the results per account number are around 200 or less, but sometimes it's in the thousands. I've always thought you're supposed to make the database do stuff like this, but if the database is having to go out to disk to use the TEMP table, then maybe in this case it could be better to sort the ResultSet in Java...I was happy to find out that String already has a Comparator for that, so you can write:

Collections.sort(myList, String.CASE_INSENSITIVE_ORDER)

However, on the main address book page, they see about five fields of each entry, and they can sort by any of the five fields. So, say the field they want to sort by is CompanyName. Then I'll have to put all the CompanyNames into a List and also into a HashMap where they're a key to the rest of the fields, and then sort the List and pull the CompanyNames out one by one to get the rest of the fields whose info needs to go on the same line on the webpage. In other words, this seems to be the reason for using ORDER BY on the database side in the first place, so please correct me if I'm wrong...

[added line break in query so page doesn't scroll right]
[ January 27, 2005: Message edited by: Jeanne Boyarsky ]

[David Harkness]

I've always gone the other way: use database for queries; use code for sorting and any other UI-related needs like formatting, etc. The main motivation has always been DBs under heavy load and the ability to add more web servers or beef up thick clients for much less cost than improving DB hardware.

As for sorting rows as you have, you can create a JavaBean (simple POJO class with an attribute for each field and get/set accessors) to represent a row. Then create a Comparator for each field on which to sort. This allows you to simply put all the beans into a Collection (e.g. ArrayList) and sort them with any of the Comparators. No need for a separate HashMap or pulling apart columns.

Here's a simple example with no error checking.

public class AccountInfo
{
    private String acctNumber;
    private String receiverId;
    private String companyName;
 
    public String getAcctNumber() { return acctNumber; }
    public void setAcctNumber(String acctNumber) { this.acctNumber = acctNumber; }
    ...
 
    public class AcctNumberComparator implements Comparator
    {
        public int compare ( Object l, Object r )
        {
            AccountInfo left = (AccountInfo) l;
            AccountInfo right = (AccountInfo) r;
            return left.getAcctNumber().compareTo(right.getAcctNumber());
        }
    }
 
    public class ReceiverIdComparator implements Comparator
    { ... }
}

If you'll have a lot of these beans, you may want to use the Data Transfer Object / Value Object pattern to create something generic.