Adaptive Server generates a query plan containing the optimal join order for a multi-table query without regard to the storage location of each table. If remote tables are represented in the query, Component Integration Services performs additional optimization taking location into account and possibly rearranging the plan for a join order that allows part of the join to be executed remotely.††
To make intelligent plan choices, statistics are required for all tables involved in the query, including proxy tables. These are obtained by executing update statistics for a specific table.
If update statistics has not been run, the Adaptive Server defaults apply. For more information on Adaptive Server optimization, see Chapter 7, “The Adaptive Server Query Optimizer,î in the Performance and Tuning Guide.†
The Adaptive Server optimizer incorporates the cost of network access to remote servers based on a “network exchange” unit which specifies the time required to execute the sequence:
Open a cursor
Fetch 50 rows
Close a cursor
The cost of a single exchange is under the user’s control, and is specified on a per-server basis, defaulting to 1000 milliseconds, by sp_serveroption:
sp_serveroption <servername>, "server cost", "nnnn"
where nnnn is a string of numeric digits representing the number of milliseconds to be used per exchange during the optimizerís calculation of network cost.
The server cost limit is 32767. If you exceed that limit,
an arithmetic overflow error occurs.
When a new server is added to sysservers using sp_addserver, the default cost, 1000 milliseconds, is stored in sysattributes for that server. sp_serveroption can be used to specify a greater or lesser cost for a given server. sp_helpserver shows the current network cost associated with the server.†
