• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

Figuring out best way to send SQL queries through JMS

 
Jack Tauson
Ranch Hand
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have the following SQL query:




which gives me the following results:




Scenario #1:


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

OR

Scenario #2


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?

Thanks



 
Paul Clapham
Sheriff
Posts: 24635
56
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jack Tauson
Ranch Hand
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
Paul Clapham
Sheriff
Posts: 24635
56
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Jack Tauson
Ranch Hand
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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!
 
Paul Clapham
Sheriff
Posts: 24635
56
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
He does not suffer fools gladly. But this tiny ad does:
professionally read, modify and write PDF files from Java
https://products.aspose.com/pdf/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!