Arun Sudhakaran Arun Sudhakaran - 22 days ago 8
Java Question

Which one gives a better perfomance - getting a sorted list from databse or sorting an obtained list using Java Code

I'm retrieving a list from database using HQL query, in which I'm giving order by clause to get it sorted. Now there are more than 20,000 records in the table and it's causing the speed of the execution. So what I did is, first retrieve the list without sorting and then sort it by using Comparable in the Java code. I ran the application but I didn't find that much of a difference in performance, so what in your opinion will give a better performance.

Answer

A rule of thumb is to sort in the database since the rows are being processed there anyway. The order by may simply cause the database engine to choose one operation over the other that results in the proper order, but have no effect on performance (for example an index scan forward vs. backward).

It's not all about performance either. If all of your database calls in code are followed by a sorting operation, then you're not writing smart code.

As with everything: don't guess, test. You can look at the generated SQL queries, then ask the database to see what operations it will do and how they will perform. Know your database or it won't matter where you do the sorting since your performance will be crippled by all the other, more relevant, bad choices that you make.