I am trying to confirm best practice around architecture I am working with.
Frontend <-> API gateway <-> Service layer (Rest API) <-> DB
I have an incoming request which is asking for huge data set (>20000 rows) from multiple DB tables clubbed into one single data set. Now there are two school of thoughts and I want to know which one is better suited to this situation.
- Customize DB query to return only tailored data set as needed above. But this includes hard-coding at DB level where service layer calls a predefined query/ set of predefined queries always. Downside I see is any DB change leading to 2 sets of changes - one at DB & second at service logic
- Service layer is generating custom query on-the-run and calls DB with this query to get data set. This will ensure immunity to DB and service layer can tweak queries as needed without any hard-coding at DB level.
What is the best practice to follow, any addendum is also welcome.
P.S. As we have large data set & performance constraints - still I am in favor of approach no. 2