Sybase 12.4.2 manual Limiting a query’s memory use, Limiting queries by rows returned, 449

CHAPTER 12 Managing System Resources

Limiting a query’s memory use

The QUERY_TEMP_SPACE_LIMIT option of the SET command lets you restrict the amount of memory available to any one query. By default, a query can use 1000MB of memory.

When you issue a query, Adaptive Server IQ estimates the temporary space needed to resolve the query. If the total estimated temporary result space for sorts, hashes, and row stores exceeds the current QUERY_TEMP_SPACE_LIMIT setting, the query is rejected, and you receive a message such as:

Query rejected because it exceeds total space resource limit

If this option is set to 0 there is no limit, and no queries are rejected based on their temporary space requirements.

Limiting queries by rows returned

The QUERY_ROWS_RETURNED_LIMIT option of the SET command tells the query optimizer to reject queries that might otherwise consume too many resources. If the query optimizer estimates that the result set from a query will exceed the value of this option, it rejects the query with the message:

Query rejected because it exceed resource:

Query_Rows_Returned_Limit

If you use this option, set it so that it only rejects queries that consume vast resources.

Forcing cursors to be non-scrolling

When you use scrolling cursors with no host variable declared, Adaptive Server IQ creates a temporary store node where query results are buffered. This storage is separate from the Temporary Store buffer cache. If you are retrieving very large numbers (millions) of rows, this store node can require a lot of memory.

You can eliminate this temporary store node by forcing all cursors to be non- scrolling. To do so, set the FORCE_NO_SCROLL_CURSORS option to ON. You may want to use this option to save on temporary storage requirements if you are retrieving very large numbers (millions) of rows. The option takes effect immediately for all new queries submitted.

449