Parallel SQL queries using completable future

Question

My problem is I'm having a table and 7 child table. I'm fetching details using querying the main table first then querying the child tables one by one sequentially. To improve performance I decided to execute all the seven queries parallely using completable future.

 StoryDetail storyDetail = new StoryDetail();
 CompletableFuture<IStoryDetail> iStoryDetailCompletableFuture = CompletableFuture
 .supplyAsync(() -> storyRepository.getStoryDetails(id), executorService);
 CompletableFuture<List<Comment>> iCommentFuture = CompletableFuture
 .supplyAsync(() -> commentRepository.getComments(id), executorService);
 CompletableFuture<List<Images>> iImageFuture = CompletableFuture
 .supplyAsync(() -> imageRepository.getImagesByStoryId(id), executorService);

Here we are executing all the queries sequentially:

CompletableFuture.allOf(iCommentFuture, iStoryDetailCompletableFuture, iImageFuture)
 ;

And waits for all of them to finish then sets the value in the StoryDetail object:

 storyDetail.setComments(iCommentFuture.get());
 storyDetail.setImages(iImageFuture.get());
 mapStoryDetail(iStoryDetailCompletableFuture.get(), storyDetail);

Is the approach correct?

• Mast: "is the approach correct" Did you test it?
• user3878073: it seems to be working fine
• Clockwork-Muse: So that you see this (and to make it a little more clear) - @mtj's answer contains a very large "But..."; generally, the database server is already going to execute (some or all) of your query in parallel. There are further weird tricks it can do, as well. Except in some extreme circumstances, a database query with multiple JOINs is going to beat manually executing multiple per-table statements. That's actually the whole point of SQL - to abstract access patterns, in favor of declaring the connections required of the data.

Answer 1

Yes, your approach is correct.

However, you parallelize various queries, which probably go to a remote backing database. As you told us nothing about the runtime of these queries, the nature of the database, the complexity of the underlying statements, I can only ask you to measure.

Parallelism induces overhead - in the runtime AND in program complexity. Check, whether it is worth it, and where your bottlenecks lie.

In my experience from enterprise applications, real big data transfers are normally not made better by parallelism, as the bottleneck is often the network between application server and database.