The performance of queries involving proxy tables that reference two or more remote servers is critical to the success of the CIS features incorporated into Adaptive Server Enterprise. Several optimization strategies are provided to make distributed query processing as optimal as possible within the constraints of the current Adaptive Server Enterprise query processor.
In previous releases, the Adaptive Server Enterprise optimizer has been modified to incorporate the cost of network access to remote servers. The network cost was pretty much hard-coded into Adaptive Server Enterprise’s optimizer as an algorithm that assumes network exchanges will be required to
open a cursor
fetch 50 rows
close a cursor.
An exchange is required for each 50 rows. The cost of an exchange in prior releases was hard-coded at 100 milliseconds. With version 12.5, 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 the sp_serveroption stored procedure:
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 string "server cost" represents a new server option introduced with version 12.5.
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 the stored procedure sp_addserver, the default cost, 1000ms, is stored in sysattributes for that server. The use of sp_serveroption may be used to specify a greater or lesser cost for a given server. The stored procedure sp_helpserver has also been modified to show the current network cost associated with the server.
In the Adaptive Server Enterprise 12.0, sort/merge joins were enabled as a possible join strategy for joining local tables. However, this strategy is disabled if any table in a query is a proxy table. Joins between proxy tables will not be managed by the sort/merge algorithm.
Reformatting allows the contents of the inner table of a nested loop join to be transferred to a work table. A clustered index is created on the join column of the work table, and subsequent join operations use this work table rather than the original.
When a proxy table is chosen to be the inner table of a nested loop join, the reformatting strategy can result in significant performance improvements, as the network is only accessed once, rather than for each row obtained by the outer table(s).
