Topics: |
Adapter optimization allows the RDBMS to perform the work for which it is best suited, reducing the volume of RDBMS-to-server communication and improving response time. It also enables the RDBMS to exploit its own internal optimization techniques.
For more information, see Optimizing Requests and Optimizing Requests to Pass Virtual Fields Defined as Constants.
How to: |
Reference: |
The Adapter for Sybase supports array retrieval from result sets produced by executing SELECT queries or stored procedures. This technique substantially reduces network traffic and CPU utilization.
Using high values increases the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.
Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.
The block size for a SELECT request applies to TABLE FILE requests, MODIFY requests, MATCH requests, and DIRECT SQL SELECT statements.
ENGINE SQLSYB SET FETCHSIZE n
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is the number of rows to be retrieved at once using array retrieval techniques. Accepted values are 1 to 5000. The default varies by adapter. If the result set contains a column that has to be processed as a CLOB or a BLOB, the FETCHSIZE value used for that result set is 1.
In combination with LOADONLY, the block size for an INSERT applies to MODIFY INCLUDE requests. INSERTSIZE is also supported for parameterized DIRECT SQL INSERT statements.
ENGINE SQLSYB SET INSERTSIZE n
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is the number of rows to be inserted using array insert techniques. Accepted values are 1 to 5000. 1 is the default value. If the result set contains a column that has to be processed as a BLOB, the INSERTSIZE value used for that result set is 1.
ENGINE SQLSYB SET FASTLOAD [ON|OFF]
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Uses the Bulk Insert API. ON is the default.
Suppresses the use of the Bulk Insert API.
You can use DataMigrator with the Bulk Insert API for Sybase.
With the Adapter for Sybase, the Bulk Insert API is used in LOADONLY mode when INSERTSIZE is greater than 1. INSERTSIZE determines how often the intermediate data flush is performed. Measurements show that intermediate flushes are necessary for optimal Sybase server performance. As a rule of thumb, the INSERTSIZE value should be from several thousand to several tens of thousands. Intermediate flushes cannot be rolled back.
When you suppress the Bulk Load API, the Adapter for Sybase resorts to array insert according to the specified INSERTSIZE.
Errors that occur during the load (such as duplication) can cause the batch of rows to be rejected as a whole.
WebFOCUS | |
Feedback |