Sybase 12.4.2 manual Limiting the number of cursors, Limiting the number of statements, 450

Options for tuning resource use

If scrolling cursors are never used in your application, you should make this a permanent PUBLIC option. It will use less memory and make a big improvement in query performance.

Limiting the number of cursors

The MAX_CURSOR_COUNT option specifies a resource governor to limit the maximum number of cursors that a connection can use at once. The default is 50. Setting this option to 0 allows an unlimited number of cursors.

Limiting the number of statements

The MAX_STATEMENT_COUNT option specifies a resource governor to limit the maximum number of prepared statements that a connection can use at once.

Lowering a connection’s priority

When you set the BACKGROUND_PRIORITY option to ON, requests on the current connection have minimal impact on the performance of other connections. This option allows tasks for which responsiveness is critical to coexist with other tasks for which performance is not as important.

Prefetching cache pages

The SET option PREFETCH_BUFFER_LIMIT defines the number of cache pages available to Adaptive Server IQ for use in prefetching (the read ahead of database pages). This option has a default value of 20, which can degrade multi-user performance. Sybase recommends that you set this option to 0 for multi-user applications. For more information, see the Adaptive Server IQ Reference Manual.

450