When querying for the top N results, I can ask the DB to sort the results OR I can sort them myself.
I read a lot about performance and memory advantage that the DB has over in-app sorting. However, assuming I write an optimal sorting code, isn't the performance equal in both options? Both are using the same CPU, both can allocate threads and both can allocate more space in memory to perform the sort.
All the answers I found in the subject are more of less the same - saying "just let the DB do it, it will do it better than you", or "the rule of thumb is do anything in the DB unless a specific need arises such as complex sorts..."
So, Why choose DB-sorting over in-app sorting (besides saving the network bandwidth by not asking for millions of table entries to sort upon)?
With an app sort you need transfer all the DATA, with a database sort you need just transfer N rows !
Database implement already the most efficient sort algorythm.
If index already exist, DMBS can return top N without sort the data.
Edit :
If your dataset is very small, it can be stored in memory client side, then you can ordered it by the app. Can be a good solution, if you need reorder data without refresh data from your DB.
In other case use DB sort.