1

Take the following statement for example:

'Get results for ALL Active students of a Course'.

Naturally, this query has 2 parts,

  1. Get all active students of the course.
  2. Get results of those active students.

The end result can be achieved with a single query using JOIN. Now, in some situations, some students may have their result missing (the teacher have not yet input mark). In such case, their result will be shown as 0 (or as , it doesn't matter). In this situation, the result of the 2nd query won't contain All the active students. But, it can be handled using LEFT JOIN to always show all students.

Now, the conditions to determine Active Students requires JOINING 2/3 tables now and may change in future. Similarly, logic for calculating mark may also change.

So, there are 2 ways to solve this. I am currently using Single Query approach as it uses a Single DB query to get all necessary data. Also, DBs are best at handling and generating data.

On the other hand, writing separate SQL queries to separately retrieve Students and Results and combining them on service logic would make maintaining the code very easy. But this will probably be slower, especially for large result set.

Another thing to consider is that there can be more queries in the form of "Get (some data) for All Active students of a Course". So, a change in requirements will affect ALL the queries.

So, considering execution speed and ease of coding what would be the better approach. I like the break and combine approach but unsure about the speed overhead. What would you do in this approach?

Platform: NodeJs, DB: MySQL

  • 2
    Questions: do you have performance criteria? What's the result set size for data you have to manually join? do you have memory requirements? When you start looking at all the criteria, you start to settle in to a design that you need. You'll find that databases are really good at fairly complex queries and once you hit a certain threshold (to be determined by testing) the process of joining in a back end service becomes a major bottleneck. – Berin Loritsch Dec 18 '19 at 20:15
  • Send the query to the database, not the data to the app. – Erik Eidt Dec 18 '19 at 20:47
  • Do the simpler first, your can refactor later when new requirements come – imel96 Dec 18 '19 at 20:53
  • Berin has good perspective on this I like it, also you should keep in mind that if you do get the data separately you may need to combine, compare, filter, etc which will create a lot of work for you because you need to handle that which in the other case just writing one query will give you the results you need. In my opinion, the Single Query Approach sounds better. – Did Dec 19 '19 at 11:07
  • @Berin The result size would not exceed hundreds. But this process may need to be done moderately frequently. I plan on doing some testing to get solid numbers. But I wanted to get outside opinion on design decision – Jahirul Islam Dec 20 '19 at 12:30
1

In the case of an SQL engine such as MySQL, the best option is to let the DB engine and its optimizer do the job: send the complex query and get the results.

There are several reasons for that:

  • SQL engines have in general an optimizer that is able to chose the best access strategy, taking into account dynamically the size of the tables, the existence of index, previous joins. The more complex the query, and the more the engine will outperform your handcrafted code.
  • Letting the engine do the work has also performance advantages: if you have some conditions in your query, you might have to transfer much more useless data between the DB and your app than if you subcontract the work to the DB engine
  • Of course managing smaller queries in the application services would facilitate database independence. But the tighter change control that you expect could also backfire: if you start to use aggregate functions and grouping, you will have to write much more code, so that the "easy maintenance" argument is offset by the much more code to maintain and longer development time.
  • Furthermore, some data-structure evolutions are easier to tackle on the SQL side

You can very well combine the benefits of both worlds:

  • to facilitate maintenance on the DB side, you can encapsulate frequently used joins, with database views.
  • not everything has to be joined every time. You could very well keep using lazy loading of complex domain objects, not using joins for an indivual domain object-by-domain object access.
  • however, for mass operation (like your student scores), you could consider a query pattern that exploits DB engine capabilities to their maximum extent. Dis does not prevent the use of a proxy objects.
| improve this answer | |
  • All very good points. So I guess the answer would be "it depends". The reason I wanted more DB independence is that the DB structure is a WIP and may very well go through some changes before being finalized. So, writing complex queries would mean that these queries need to be modified if some changes occur. And if that happens modifying complex queries would be a lot harder task than modifying simple queries and increase the possibility of making a mistake. – Jahirul Islam Jan 16 at 5:51
  • @JahirulIslam Yes, this is a very valid argument if you target a cross db platform, or if you target a document oriented like db like Mongodb, where the joins require more gymnastics. But is it a real intention or just a possible future? And what about delivering the features fast and refactor later if needed? (which is in fact the idea behind my “combination” suggestions) – Christophe Jan 16 at 6:47
  • Currently, there is no real plan to target a different DB. I just need to make sure the SQLs don't get overcomplicated and remain maintainable without too much effort. – Jahirul Islam Jan 16 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.