Execute queries concurrently or one by one

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
FROM VW_UI_GET_EMP_DATA_LIST
WHERE
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

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

I hope, I understood you correctly: you take all data from table with select and than save in file. And any backup takes 2-3 Hours, that means tables have a lot of records (millions? billions?).

So I wouldn’t to do. At least with PHP. I don’t know, possibly Java can do this.

Please find my updated post below the original one. Please let me know if I can answer more questions. Thanks

Ah… Sorry, that’s not a technolody I work.

No problem. Thanks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.