I am using Oracle database and I have 4 SQL queries in a table which looks like this :
EMPDATAID | EMPDESC | DATAQUERY | FILENAMEPATH 201 | Description 1 | SELECT * FROM TABLE 1 | file1.xlsx 202 | Description 2 | SELECT * FROM TABLE 2 | file2.xlsx 203 | Description 3 | SELECT * FROM TABLE 3 | file3.xlsx 204 | Description 4 | SELECT * FROM TABLE 4 | file4.xlsx
I am going to run each of the SQL queries once a week.
Each of the SQL queries are going to take 2-3 hours to complete. I have an option to execute it one by one in a sequential manner OR concurrently. I am not much concerned about the time it takes to complete since I am running them on a weekly basis.
If I go with concurrent option, could there be a scenario of data locks? Which option would you prefer, complete first query
SELECT * FROM TABLE 1 and then start
SELECT * FROM TABLE2 etc or execute all 4 concurrently? Just trying to figure out which would be best approach? Thanks
Updated my post below to explain what I am trying to achieve.
I have the following SQL query:
SELECT EMPDATAID, EMPDESC, DATAQUERY, FILENAMEPATH
ISSCHEDULED = ‘Y’;
which gives me the following results:
EMPDATAID | EMPDESC | DATAQUERY | FILENAMEPATH
201 | Description 1 | SELECT * FROM TABLE 1 | file1.xlsx
202 | Description 2 | SELECT * FROM TABLE 2 | file2.xlsx
203 | Description 3 | SELECT * FROM TABLE 3 | file3.xlsx
204 | Description 4 | SELECT * FROM TABLE 4 | file4.xlsx
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:
SELECT * FROM TABLE 1as first message to the Queue on the broker.
SELECT * FROM TABLE 2as 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?