One major con to using a single server would probably be with respect to the number of workers available. Since you are using 11.0.1, the number of workers is fixed (i.e. there is no MPL support prior to SA 12). Now consider the following:
- you connect to the server and begin the insert-select
- the local connection gets a worker assigned to it to process the request and that worker will remain assigned to the connection for the duration of the request (i.e. for the entire insert-select)
- the remote data access layer now makes a remote connection and executes the select portion
- the remote connection will now get its own worker assigned whenever it is active (i.e. whenever it returns the next block of rows)
- if the server decides to execute the "select" portion in parallel, then it could utilize several workers to fetch the rows from the "remote"
So, for the duration of the insert-select, the "remote query" will utilize at least 2 workers and possibly many more than 2 workers. Having the remote database on a separate server will change things so that the local server only utilizes one worker for the entire insert and the remote server would utilize 1 or more workers for the select. If the local server is also servicing other connections, then pushing the select to a different server and leaving the additional workers available for the other "local" connections would be very beneficial. At the same time, if the select portion of the query can be executed in parallel, then having a remote server that is not as busy as the local server and having a full set of workers available to handle the select would also be very beneficial.
So, my opinion is that having two servers with each having its own set of workers (and cache etc.) is probably the better approach here; even if both servers are running on the same machine.
Breck says: A wonderful discussion! Now... what if there are no client-server connections to the local database at all; i.e., what if the entire process is being executed by a DatabaseStart event, and client-server connections are forbidden? (this is a "batch" process that is part of an embedded database upgrade process).
answered
04 Oct '10, 14:57
Karim Khamis
5.6k●5●38●70
accept rate:
40%
... compared to what - starting both databases in their own db engine? IMHO, I don't think the latter has any advantage - but I haven't had such a scenario yet:)
As Proxy table access has been improved with v12: Any chance to use a SA 12 engine to do the copying (without reloading the SA 11 dbs, of course)?
@Volker(1): yes, as opposed to each database in its own engine.