I am planning to run the above SQL Query every week using Spring boot scheduler. As seen above, the column `DATAQUERY` contains bunch of SQL queries. Each of these queries are long running queries which could take 2-3 hours.
To handle these long running queries, I am planning to use ActiveMQ and do the following:
1) Send `SELECT * FROM TABLE 1` as first message to the Queue on the broker.
2) Send `SELECT * FROM TABLE 2` as second message to the Queue on the broker.
Similarly, 3rd and 4th message for remaining SQL queries.
My consumer (a different springboot app) will consume each of these messages one by one sequentially and then write the resultset to a CSV file somewhere on the server.
Before implementing this approach, I am trying to figure out whether the above approach is a good and efficient one
Should I consider sending all the 4 SQL queries inside the `DATAQUERY` column in one message and then handle them concurrently in my consumer springboot app somehow? Basically, I could do something like this to send
them all in one message. My single message would look like this: `SELECT * FROM TABLE 1#SELECT * FROM TABLE 2#SELECT * FROM TABLE 3#SELECT * FROM TABLE 4`.
Then my consume app could grab each of these SQL statements which are separated by `#` symbol and I could have multi threaded application handling all the 4 or maybe more (if there are in future) SQL queries concurently.
Please advise which scenario would be more efficient?
Sending four short text messages through JMS? Even without inquiring about your network configuration I would bet it could be done in under a second. Given that executing the queries is going to take much longer than one second, there's no point in trying to improve on it.
posted 2 weeks ago
Paul Clapham wrote:Sending four short text messages through JMS?
Yes, that's one approach I am thinking. Sending 4 different SQL queries in the form of a string message to the queue. But in this scenario, I will have to sequentially retrieve the message from the queue and wait until the first one finishes? It doesn't make much difference here since I am scheduling it on a weekly basis.
Other approach I was thinking about was the sending all 4 SQL queries as a single message and then somehow handle the 4 SQL queries concurrently.
According to you, which approach would you go with? Thanks
Jack Tauson wrote:Sending 4 different SQL queries in the form of a string message to the queue. But in this scenario, I will have to sequentially retrieve the message from the queue and wait until the first one finishes?
No, just set up an ExecutorService and have the JMS consumer submit the SQL queries to it.
You'll notice that both of your options ended with the question "how do I handle the 4 SQL queries concurrently?" What I just wrote is how you do that. Since that's your actual problem, I recommend choosing the simpler option to send the requests.
posted 1 week ago
Paul Clapham wrote:
No, just set up an ExecutorService
Thanks. I am trying to understand what would be the difference between setting up an executor service versus using a spring scheduler. In case of spring scheduler, if I understood correctly, I can schedule a task to run on a weekly basis. And this task will send either all of the SQL query in a single message to a particular queue on the broker OR send 4 different messages to a particular queue on the broker. By just setting up executor service, were you referring to some other approach, I mean something other than the one I mentioned in my post above?
have the JMS consumer submit the SQL queries to it
Here, you mean JMS Producer submit SQL queries to it, right? Were you referring to 4 SQL queries in one message or 4 different queries in 4 different messages?
You'll notice that both of your options ended with the question "how do I handle the 4 SQL queries concurrently?"
If I am sending 4 different messages to the queue on the broker, I might not run into handling of 4 SQL queries concurrently because, my consumer will sequentially consume these messages. So, as long as my first query isn't finished, my consumer won't consume second message containing second query. Please correct me if I misunderstood something. Thanks!
Jack Tauson wrote:If I am sending 4 different messages to the queue on the broker, I might not run into handling of 4 SQL queries concurrently because, my consumer will sequentially consume these messages. So, as long as my first query isn't finished, my consumer won't consume second message containing second query. Please correct me if I misunderstood something. Thanks!
Yes, you misunderstood. My suggestion is this: when the message consumer receives a message containing an SQL query, it should pass that query to SOMETHING ELSE which will then go away and process the query. That way the consumer is immediately ready to deal with the next message and the SOMETHING ELSE will be busy running the query.
You asked about Spring scheduling. I spent a minute reading up on scheduling tasks in Spring and it looks to me like you can schedule things to happen at specific times, or at specific frequencies. That is not your use case. My recommendation was ExecutorService, which I invite you to read about. And perhaps I wasn't clear enough -- my recommendation was for the message consumer to have an ExecutorService available, and when it receives an SQL query it will construct a task which processes that query and submit it to the ExecutorService.
If you're gonna buy things, buy this thing and I get a fat kickback:
global solutions you can do in your home or backyard