Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 469 / 536

CHAPTER 12 Managing System Resources
449
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.