• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sort Strings on app or database side?

 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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:



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:



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
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic