Pros and cons of sorting data in DB?

Question

Let's assume I have a table with field of type VARCHAR. And I need to get data from that table sorted alphabetically by that field.

What is the best way (for performance): add order by field to the SQL-query or sort the data when it's already fetched?

I'm using Java (with Hibernate), but I can't tell anything about DB engine. It could be any popular relational database (like MySQL or MS Sql Server or Oracle or HSQL DB or any other).

The amount of records in table can vary greatly but let's assume there are 5k records.

UPD: how well does 2nd level hibernate cache (EHCache for example) support sorted data?

Answer 1

If this field is indexed, then the average DB would be much more efficient in this task than Java. Also note that you normally wouldn't retrieve all those rows at once if it's for pure display, but rather retrieve a subset of it so that it can be shown by pagination. You can do this at DB level as well. Sorting the data in Java would require the entire table being hauled into Java's memory, you don't want to do that.

---

In Hibernate you can order the results using Criteria#addOrder() and paginate using Criteria#setFirstResult() and Criteria#setMaxResults(). E.g.

List users = session.createCriteria(User.class)
    .addOrder(Order.asc("username"))
    .setFirstResult(0) // Index of first row to be retrieved.
    .setMaxResults(10) // Amount of rows to be retrieved.
    .list();

Answer 2

Sort the data in the database - that's (part of) what it's there for. The database engine is probably better at sorting this data than you are.