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?
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 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
posted 8 months ago
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.
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?
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?
posted 8 months ago
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.
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.
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?