Sybase Optimization Settings

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.

Specifying Block Size for Retrieval Processing

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.

Syntax: How to Specify Block Size for Array Retrieval

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:

SQLSYB

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

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.

Syntax: How to Specify Block Size for Insert Processing

xIn 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:

SQLSYB

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

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.

Syntax: How to Suppress the Bulk Insert API

ENGINE SQLSYB SET FASTLOAD [ON|OFF]

where:

SQLSYB

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

Uses the Bulk Insert API. ON is the default.

OFF

Suppresses the use of the Bulk Insert API.

Reference: Bulk Insert API Behavior

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