database - How many consecutive queries are too many? With Example

Question

Let's assume we have 1 database and we need information from 3 of its tables.

Table A has 40 columns and 500 000 rows. Table B has 30 columns and 2 000 000 rows. Table C has 10 columns and 500 000 rows.

Table C effectively holds the relation between Tables A and B and we want to execute a query such as this:

SELECT * FROM (
SELECT <10 columns>, row_number() over (order by A.column1) as rowNumber FROM A,B,C
WHERE A.column1 = C.column1
AND B.column1 = C.column2
AND A.column3 = true
AND rowNumber = ?
)
ORDER BY rowNumber

Essentially, we're making 500 000 consecutive queries with less than a second between each two (rowNumber is a variable that will be increased by 1 in a non-parallel loop).

You can assume that all the columns followed by the WHERE clause, except the rowNumber obv, are indexed.

How big of an impact could such a number of consecutive queries have on a database? Will it have impact on any other queries that can be executed? How drastically would the impact increase if we send 10 queries at the same time instead of just 1?

I'm considering such a solution because this'll be a one-time thing (data migration) and the data that will be migrated needs to be mapped to data from another database so even if I use pagination for one of the DBs I'll still have to do 1 query per entry on the other.

I'm aware it's possible to run 1 query to multiple DBs but as I mentioned this is a one-time thing and unless there are serious concerns with running this many consecutive queries it's preferable to not open that can of worms. I'm interested in how big of a problem it would be to send this many queries to a server that's already under the usual load. I know it depends on the machines running the DB but I can't provide such information and am hoping you can provide me with roughly estimated information based on the numbers provided since I'm unaware what volumne of operations a database usually handles and what the potential issues are.

question from:https://stackoverflow.com/questions/65599924/how-many-consecutive-queries-are-too-many-with-example

Answer 1

Waitting for answers