Two Laptop Bag*
The moose likes JDBC and the fly likes Optimization:Operations at Database level or Application level? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimization:Operations at Database level or Application level?" Watch "Optimization:Operations at Database level or Application level?" New topic
Author

Optimization:Operations at Database level or Application level?

Ash Ashwini Joshi
Greenhorn

Joined: Feb 16, 2012
Posts: 2


Hi All,

I have an optimization related query.Some things can be achieved at either at Database level or at Application level.

e.g. If I am accessing database field which has say 3 possible values:A,B,C.

Each one has some mapping which I need to show to user.(like A-> "ASDFG"; B-> "BRTRYC" ; C->"CRTYER")

For this purpose, I can either use oracle DECODE() function or I can do the mapping in my program after retrieving from database.Which way is better one?

On Similar grounds : i need output from database in sorted on some columns, shall I use "order by" or take the data as it is and use various optimized data structures like trees or use Collection.sort()?

As per my guess, database level should be better choice,especially in case of sorting if database is indexed on those columns.But I need confirmation on this and what about non-sorting cases like the DECODE i mentioned?

Thanks in advance.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I believe that the DECODE thing is too insignificant to make a difference performance-wise. I'd try to put where it fits best from the design point of view. For example, if it is presentation logic, I'd definitely do it at Java side in the presentation layer.

The sorting is probably insignificant too. Both database and Java have efficient sorting algorithms, though the database can sometimes utilize an index, as you've mentioned. I usually use ORDER BY whenever possible, just because it is sometimes a bit simpler than sorting at the Java side.

That said, there of course are situations when this kind of decision actually matters. For example, if the processing at the database side can significantly reduce the amount of data to travel over network (which is not true in the cases you've mentioned), certainly do it in the database.
Ash Ashwini Joshi
Greenhorn

Joined: Feb 16, 2012
Posts: 2
Thanks Martin that was useful.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Optimization:Operations at Database level or Application level?
 
Similar Threads
long post IBM.158
Which sorting is better, Code Level Vs Database level?
Help!!!! URGENT
Passed 700,701 and 340 last Thursday
287 Pre-assessment test questions - Please answer