The performance of queries involving proxy tables that reference two or more remote servers is critical to the success of the Component Integration Services features incorporated into Adaptive Server. Several optimization strategies are provided to make distributed query processing as optimal as possible within the constraints of the current Adaptive Server query processor.
In versions earlier than 12.5, the Adaptive Server optimizer was modified to incorporate the cost of network access to remote servers. The network cost was hard-coded into the Adaptive Server optimizer as an algorithm that assumes network exchanges are 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 versions 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 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. “server cost” represents a server option introduced in Adaptive Server 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 sp_addserver, the default cost, 1000ms, 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.
Sort/merge joins are enabled as a possible join strategy for joining local tables. However, they are disabled if any table in a query is a proxy table. Joins between proxy tables are not 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 tables.
