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?
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.