CODEWITHSUNDEEP
javamysqlsqlmultithreading
user2248702
user2248702

Reputation: 2988

Most effective way to multi-thread SQL (Java)

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

I am making a program that needs to synchronize data from a MySQL database every iteration of the main thread. I would like to multithread this program so that a long query will not hold the main thread up and slow it's 'tick' rate.

I am not great at explaining the solutions I have came up with in words so I have made this image which I hope explains them a bit better. enter image description here

Are any of these ways the 'correct' way of doing things?

I recall something about possibly sending multiple queries at once then waiting for a result at the end, is this possible and how many queries should be sent at one time?

Should a separate thread be used for each query and if so how could I make this faster as I understand the overhead for creating a thread is quite large.

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

Upvotes: 6

Views: 1092

Answers (1)

Alexei Kaigorodov
Alexei Kaigorodov

Reputation: 13525

MySQL can execute queries in parallel but not very much (I think 10-15). So I would create a pool of 10-15 threads, with common blocking queue for queries, each thread has its own database connection. Each working thread executes a loop: take next query, access DB, return result somehow. Play with number of threads to find optimum.

Upvotes: 1

Related Questions