A large number of rows are to be copied from one SQL Anywhere 18.104.22.1686 database to another database in the same folder on the same computer via simple INSERT t SELECT * FROM proxy_t statements. Millions of rows, gigabytes of data, hours of processing time using two SQL Anywhere engines.
What are the performance pros and cons of starting both databases in one SQL Anywhere engine?
asked 29 Sep '10, 12:25
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:
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).
Both DBs in the same folder will probably lead to IO as your main problem. Spreading IO accross multiple devices for this task will bring you probably more, than thinking about optimisations regarding the db engine. E.g. use for each db file its own (SAN, Raid, ...) And by the way, increase the DB file size of the target before starting the copy ;-)
answered 30 Sep '10, 09:38