When you use scrolling cursors with no host variable declared, Sybase 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.
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.
