Whether you refer to federated systems , a linked server , or a database link, the principle is the same: in distributed queries, you are querying data that is not physically managed inside the server (or database to the Oracle crowd) you are connected to. Distributed queries are executed through complex mechanisms, especially for remote updates, in which transaction integrity has to be preserved. Such complexity comes at a very heavy cost, of which many people are not fully aware.
By way of example, I have run a series of tests against an Oracle database, performing massive inserts and selects against a very simple local table, and then creating database links and timing the very same operations with each database link. I have created three different database links:
A link made by connecting through inter-process communications—typically what one might do to query data located in another database[*] on the same host. No network was involved.
A link connecting through TCP, but specifying the loop-back address (127.0.0.1) to limit our foray into the network layers.
A link specifying the actual IP address of the machine—but once again without really using a network, so there is no network latency involved.
The result of my tests, as it appears in Figure 8-1, is revealing. In my case, there is indeed a small difference linked to my using inter-process communications or TCP in loop-back or regular mode. But the big performance ...