You can change the retrieval buffer and retrieval sort buffer sizes in Essbase to optimize Report Writer reports and Smart View Query Designer queries.

The time required to generate a report varies depending upon factors such as the size of the cube you are reporting from, the number of queries included in the script, and the size of the retrieval buffer and retrieval sort buffer.

Configurable variables specify the size of the buffers used for storing and sorting data extracted by retrievals. The retrieval buffer and retrieval sort buffer should be large enough to prevent unnecessary read and write activities. You can set them in the Essbase web interface or in MaxL.

The retrieval buffer holds extracted row data cells before they are evaluated by the RESTRICT or TOP/BOTTOM Report Writer commands. The default size is 20 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.

When the retrieval buffer is full, the rows are processed and the retrieval buffer is reused. If this buffer is too small, frequent reuse of the area can increase retrieval times. If this buffer is too large, too much memory may be used when concurrent users perform queries, also increasing retrieval times.

The retrieval sort buffer holds data until it is sorted. Report Writer and Query Designer (in Smart View) use the retrieval sort buffer. The default size is 20 KB. The minimum size is 2 KB. Increasing the size may improve retrieval performance.

To set the retrieval buffer size and retrieval sort buffer size:

1. Navigate to Buffers.
   In the Redwood Interface:

   1. On the Applications page, open the application and then open the database (cube).
   2. Click Customization, click Settings, and click Buffers.

   In the Classic Web Interface:

   On the Applications page, go to the cube inspector, then go to the Settings tab and select Buffers.

2. Add the values you want to use, and click Save.