The query processing steps taken when Component Integration Services is enabled are similar to the steps taken by SAP ASE.
If a client connection is made in passthrough mode, the SAP ASE query processing is bypassed and the SQL text is forwarded to the remote server for execution.
When select, insert, delete, or update statements are submitted to the server for execution, additional steps may be taken by Component Integration Services to improve the query’s performance, if local proxy tables are referenced.
The steps are outlined below.
The SQL parser checks the syntax of incoming SQL statements, and raises an error if the SQL being submitted for execution is not recognized by the Transact-SQL parser.
During query normalization, each object referenced in the SQL statement is validated. Query normalization verifies the objects referenced in the statement exist, and the datatypes are compatible with values in the statement.
select * from t1 where c1 = 10
The query normalization stage verifies that table t1 with a column named c1 exists in the system catalogs. It also verifies that the datatype of column c1 is compatible with the value 10. If the column’s datatype is datetime, for example, this statement is rejected.
Query preprocessing prepares the query for optimization.
It may change the representation of a statement such that the SQL statement Component Integration Services generates is syntactically different from the original statement.
Preprocessing performs view expansion, so that a query can operate on tables referenced by the view. It also takes steps such as reordering expressions and transforming subqueries to improve processing efficiency. For example, subquery transformation may convert some subqueries into joins.
After preprocessing, a decision is made as to whether Component Integration Services or the standard SAP ASE query optimizer handles optimization.
Every table represented in the SQL statement resides within a single remote server.
The remote server is capable of processing all the syntax represented by the statement.
Component Integration Services determines the query processing capabilities of the remote server by its server class. For example, Component Integration Services assumes that any server configured as server class sql_server is capable of processing all Transact-SQL syntax.
For remote servers with server class direct_connect, Component Integration Services issues an RPC to ask the remote server for its capabilities the first time a connection is made to the server. Based on the server’s response to the RPC, Component Integration Services determines the syntax of the SQL it forwards to the remote server.
It is a select, insert, delete, or update statement.
If it is an insert, update, or delete statement, there are no identity or timestamp columns, or referential constraints.
It contains no text or image columns.
It contains no compute by clauses.
It contains no for browse clauses.
It is not a select...into statement.
It is not a cursor-related statement (for example, fetch, declare, open, close, deallocate, update, or delete statements that include where current of cursor).
The remote connection does not include a cursor opened on the remote server.
If the above conditions are not met, quickpass mode cannot be used, and the standard SAP ASE query optimizer handles optimization.
SAP ASE optimization and plan generation evaluates the optimal path for executing a query and produces a query plan that tells the SAP ASE how to execute the query.
If the update statistics command has been run for the tables in the query, the optimizer has sufficient data on which to base decisions regarding join order. If update statistics has not been run, the SAP ASE defaults apply.
For more information on SAP ASE optimization, see Performance and Tuning Series: Query Processing and Abstract Plans > Using Statistics to Improve Performance.
If quickpass mode can be used, Component Integration Services produces a simplified query plan in which the entire statement is pushed to a remote server.
If quickpass mode cannot be used, the SAP ASE optimizer generates a plan for executing the entire statement. This plan is then examined and portions of the plan are chosen to be pushed off to remote servers. As much of the original plan is pushed off as is possible based on the locations of the tables and the capabilities of the remote servers. The remote statement may come very close to the original statement for a fully capable remote server. A more minimal statement may be produced for other servers with the local SAP ASE executing the portion of the plan that could not be sent.
For example, if a client entered the statement:
select a,b from table1 where cos(a) > 0 and sin(b) > 0
If the remote server that owned table1 supported cos() but not sin(), the statement sent to the remote server would be:
select a,b from table1 where cos(a) > 0
The local server would then have a plan that would apply the check for sin(b) > 0 to the result set returned by the remote server.
SAP ASE 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 SAP ASE defaults apply.
The SAP ASE 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.
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.
Any command that can affect a table is checked by the server to determine whether the object has a local or remote storage location
If the storage location is remote, then the appropriate access method is invoked when the query plan is executed in order to apply the requested operation to the remote objects. The following commands are affected if they operate on objects that are mapped to a remote storage location:
|
|
|