CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
ptownbro
ptownbro

Reputation: 1284

How Database Query Data Processed Between 3 Computers?

I'm trying to understand how data is really processed and sent between servers, but am unable to find a step by step detailed explanation. To explain using an example:

Assume you have 100M records that's 10GB in size and you need to transfter the data between 2 database servers over a LAN using a separate desktop computer to execute the script. In total you have three computers:

  1. Server A has the source database you are selecting the data from.
  2. Server B has the destination database you are writing the data to.
  3. Desktop C is where you are executing your script from, using a client tool like SQL Developer or TOAD.

The SQL would look something like this:

Insert Into ServerBDatabase.MyTable (MyFields) 
Select MyFields 
From ServerADatabase.MyTable

Can someone explain where the data is processed and how it is sent? Or even point me to a good resource to read up on the subject.

For example, I'm trying to understand if it's something like this:

  1. The client tool on Desktop C sends the query command to the DBMS on Server A. At this point, all that is being sent over the LAN is the text of the command.

  2. The DBMS on Server A receives the query command, interprets it, and then processes the data on Server A. At this point, the full 10GB data is loaded into memory for processing and nothing is being sent over the LAN.

  3. Once the full 100M records are done being processed on Server A, the DBMS on Server A sends both the text of the query command and the full 100M records of data over the LAN to the DBMS on Server B. Given bandwidth constraints, this data is broken up and sent in chunks over the LAN at some amount of bits per second and loaded on Server B's memory in those data chunk amounts.

  4. As the DBMS on Server B recieves the chunks of data over the LAN, it then pieces it back together on Server B in it's memory to retun it back to it's full 100M records state.

  5. Once the 100M records are fully pieced back together by the DBMS on Server B, the DBMS on Server B then executes the query command to insert the records from it's memory in the destination table one row at a time and writes it to disk.

All of this are assumptions, so I know I could have it all wrong which is why I'm seeking help. Please help correct my errors and/or fill in the blanks.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

This is your query:

Insert Into ServerBDatabase.MyTable (MyFields)
    Select MyFields
    From ServerADatabase.MyTable;

Presumably, you are executing this on a client tool connected to Server A (although the same reasoning applies to Server B). The entire query is sent to the server, and Server A would need to have a remote connection to Server B.

In other words, once the query is sent, the client has nothing to do with it -- other than waiting for the query to be sent.

As for the details of inserting from one database to another, those depend on the database. I would expect the data to be streamed into the table on Server B, all within a single transaction. The exact details might depend on the specific database software and its configuration.

Upvotes: 1

Related Questions