The most efficient way of multi-threaded SQL (Java)

Question

First, I know that this is a bit broad and opinion-based, but I just want a simple answer to the best multithreading practice of an application that uses SQL queries in Java.

I am making a program that should synchronize data from a MySQL database at each iteration of the main thread. I would like this program to be multi-threaded so that a long request does not hold the main thread and slow down the tick speed.

I do not really understand the decisions that I came up with words, so I made this image, which, I hope, will explain them a little better.

Is any of these methods the “right” way to do something?

I remember something about the possibility of sending several requests at the same time, and then waiting for the result at the end, is it possible and how many requests should be sent at a time?

If a separate thread is used for each request, and if so, how can I do it faster, since I understand that the overhead for creating a thread is quite large.

Thank you for reading my terribly worded and extremely long question, thanks in advance for any help.

Answer 1

MySQL can execute queries in parallel, but not really (I think 10-15). Therefore, I would create a pool of 10-15 threads, with a common queue lock for requests, each thread has its own database connection. Each worker thread executes a loop: accepts the next query, accesses the DB, returns the result in some way. Play with the number of streams to find the best one.