Win a copy of TensorFlow 2.0 in Action this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Sorting Implementation - Java or Database

 
Ranch Hand
Posts: 431
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose we have to fetch some data from the table from Oracle. And, we need to support sorting. Then, where should we implement sorting logic - on Java DAO/Service layers or at DB end (queries)? What is the guideline for such scenarios?
 
Saloon Keeper
Posts: 12436
269
  • Likes 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Always try to do as much processing as possible on the database end. Usually the DBMS is better at processing data, and the result set may also be smaller, meaning you need less bandwidth to send the data to the application.
 
Vaibhav Gargs
Ranch Hand
Posts: 431
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Stephan. Does that mean if we have millions of rows in database, still we should perform sorting on DB end only?

Is there any guideline on table size/number of records which govern the implementation place.
 
Marshal
Posts: 70709
288
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Vaibhav Gargs wrote:. . . Is there any guideline on table size/number of records which govern the implementation place.

Yes. If you are sorting two lines, let the DMBS do it

Stephan is right. You write ORDER BY XYZ and the DBMS is highly optimised to carry out your query. Also, as Stephan says, you might reduce the time to download the results. Also, maybe the Java┬« program can't start sorting until after the last row of the result set has been downloaded.
 
Stephan van Hulst
Saloon Keeper
Posts: 12436
269
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why would the size matter?

It's simple. A database is specially built do handle data. So let it handle data.

Process data in your application backend when you want to transform data a second time when you already have results from a previous query. That way you can save a round-trip. Another reason could be that the operation is too complex to perform in a database query, but in that case you might also want to consider using a stored procedure.
 
Vaibhav Gargs
Ranch Hand
Posts: 431
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Stephan and Campbell.

Suppose the database returns the 10,000 records as query result. Should we store them in Java at the backend to save db queries. And, if we have 10000 users who are hitting this service and then won't it overkill the system. What are your suggestions?
 
Saloon Keeper
Posts: 6649
161
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Vaibhav Gargs wrote:Suppose the database returns the 10,000 records as query result. Should we store them in Java at the backend to save db queries.


That depends on what you intend to do with them. For example, if this is for a web app, then there may not be a need to retrieve 10000 records since you would display no more than, say, 100 or so at a time. But if further processing needs to be done on them that the DB can't do, you may have to.

if we have 10000 users who are hitting this service and then won't it overkill the system.


If you actually have 10K simultaneous users you may have to put in place a pool of servers. Is this a theoretical question, or an actual concern?
 
Campbell Ritchie
Marshal
Posts: 70709
288
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Stephan said, it is probably only worth keeping the results in memory if you are going to use them again.
You will have to do some profiling/benchmarking to see what the load on your server is. You might need multiple servers if the load is heavy. Don't know more than that.
 
Bartender
Posts: 2702
130
Google Web Toolkit Eclipse IDE Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If fetching data is primarily your motive and it comprises of a huge subset of data, I'll recommend running your query over views instead of the actual table. A view is managed by the database and can contain the sort and filter conditions that you know before-hand.
 
Vaibhav Gargs
Ranch Hand
Posts: 431
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to think about design principle to be followed in such scenarios. Suppose we hit the DB and fetched the data corresponding to the user, and it returns 10000 rows per user and we have say 1000/10000 users making such requests (all users will have different result sets). Yes, in web-app, we will never show all 10000 results to the user instead it will be utilizing pagination features. So, should we go to the DB again to fetch next page of data or what will be good design principle? If we go to the DB again, won't it perform the sorting operation (10000 total records / 100 results per page  = 100 pages) considering every fetch will get 100 records per fetch. What would be the ideal implementation strategy?
 
Tim Moores
Saloon Keeper
Posts: 6649
161
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's what the DB is there for, and what it's good at. 10000 is a small number for a DB.
 
We're being followed by intergalactic spies! Quick! Take this tiny ad!
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic