In one of our applications we are using Executor framework to send 30 SQL queries in parallel to achieve high performance, which in turn opens 30 database connections at the same time. The application might receive 300 hits in a second, which translates the total number of DB connections to be 9000 (May be little less). What should be our approach to implement a hybrid solution where some queries will run in parallel and some in sequence. Or is there any other framework that can be leveraged. We are using Spring JDBC template for fetching the results.
Well, it is hard to answer in details as your question is not very detailed.
I usually use some combination of methods marked with @Scheduled and @Async. E.g. some method is scheduled to launch every second and call several asynchronous methods (sometimes I use atomic counters of work in progress).
the total number of DB connections to be 9000
Just make sure you use connection pool properly to avoid hitting performance with reopening every single connection
If you are using the Executor frameweork correctly, then you shouldn't be using that many database connections.
First, The executor executes the tasks on a Thread pool. The size of the Thread pool is fixed (or can be limited). So, once you have created a ThreadPool. So, by definition, your ThreadPool will limit how many tasks will execute concurrently. If you are creating a Thread Pool for every hit, then you are making a big mistake. You should have a single thread pool executor that executes all tasks from all hit on the application. You can tune the thread pool to be as large as the hardware can handle. However, don't keep instantiating and destroying thread pool. Thread are costly things, and since Thread Pools encapsulate a bunch of threads, Thread Pools are even costlier
Second, you should use a connection pool. Each task shouldn't open and close it's own connection. Instead you should have a single connection pool, and each task should borrow a connection from the connection pool, and return it back. Opening a a connection is a costly operation. A connection pool ensures that connections don;t open and close every time. Generally, speaking, your database can handle a lot more connections when you use a connection pool in the application. This is because it doesn't have to take the overhead of establishing connections
If you do what to run some queries in sequence, your simplest solution is to just have one task that execute all those queries in sequence. You might want to look at Spring batch, that allows you to sequence steps, and some steps can be run in parallel. However, Spring Batch may be overkill for you.