Evaluate how the structure of the SQL query should look like regarding ideal performance and maintainability

Solution Ideas

1. One single query that uses LEFT JOIN
   • Pros:
     • Only one query is sent to the database and not multiple
     • The database's query analyzer optimizes a single query better than multiple
     • No additional work to join results programmatically
     • Less complex Java code to join the results programmatically; the Java 8 stream API can help to reduce the complexity
2. Perform one dedicated query for each entity
   • Pros:
     • Multiple queries have better readability compared to one very huge SQL statement
       • Maintainability might be better
     • A single query with LEFT JOIN s might perform badly since there exist no foreign key relations for history data which slows down joining
     • Depending on the number of LEFT JOINs (> 1), less data is transferred between the database and application server
       • LEFT JOINs produce exponentially more data since the result will be a cartesian product that takes longer to transfer and requires more memory and computing power on the application server-side
     • Lower complexity of SQL queries; the SQL complexity of adding more 1:n relations is lower

Decision

We should go for solution #2. Running multiple SQL queries instead of one large one reduces complexity on the SQL side but increases complexity on the Java side. Although one single query can be better optimized by the query optimizer of the database server, foreign key relations are missing in historical tables. This might lead to a high query execution time due to full table scans. Currently, we have the requirement to join only two entities, which means that the number of results does not increase exponentially. However, we might extend the feature and want to add more 1:n relationships at a later point, meaning more than two entities will have to be joined. This would result in an exponentially growing number of results, as shown in the example. Suppose the database server returns a huge amount of results on the application server-side, it can lead to exhausting its resources, and in particular to out-of-memory exceptions. If we create a solid technical foundation now, we don't need to solve this problem in the future, and extending the export feature will be significantly simplified.