Page mode

When you execute a statement in the SQL Whiteboard, MXDM caches the results in virtual memory. The SQL Whiteboard displays these results, but the virtual memory allotted for the SQL Whiteboard can exhaust in the following scenarios:

  • A single query has a large result set consisting of a large number of columns or a large number of rows, or both.

  • Several queries with large result sets are executed in a single SQL Whiteboard session.

To prevent an out-of-memory condition and accommodate large result sets, the SQL Whiteboard can run in the page mode. In the page mode, the SQL Whiteboard breaks large result sets into pages and retrieves one page at a time instead of retrieving all the data and displaying all rows in the grid. The page mode reduces the memory usage because only a partial result is held in memory and displayed in the Statement Details pane.

Default page mode operation

By default, the SQL Whiteboard displays all the results of a statement. However, it automatically runs in page mode in the following scenarios:

  • Insufficient memory is available to display the result set in one page.

  • You specify a value from 1 to 100,000 in the Rows/Page field of the Statement pane. In this case, if sufficient memory is available, the SQL Whiteboard automatically uses page mode and allocates the number of rows per page that you specified.

Page mode considerations

The following considerations apply for page mode:
  • During the course of statement execution, MXDM frequently computes the available virtual memory of the application. If the available virtual memory is less than an internal threshold, statement execution happens in the page mode. You cannot modify this behavior. In some cases, you must use the page mode to prevent MXDM from running out of memory and generating an exception.

  • When a statement is executed and the number of rows is less than the Rows/Page threshold, all rows in the results are retrieved and displayed in the grid.

  • When a statement is executed and the number of rows in the result set exceeds the Rows/Page threshold, statement execution happens in the page mode. Partial results are retrieved and displayed.

  • When statement execution happens in the page mode, the Statement Details pane displays Next Page. The following considerations apply:

    • Until you click Next Page, the execution is paused, and the SQL cursor remains open. However, the Time Elapsed timer continues to run because it tracks the wait time.

    • When you click Next Page, the next page of results is retrieved from the server. The first page is deleted from the grid when the new page is loaded. The process repeats until you either view all the pages or cancel the query.

    • You cannot return to the previous page because the ODBC cursor operates in the FORWARD mode only. To display earlier results, you must re-execute the statement.

  • Regardless of whether the number of rows in the result set exceeds the Rows/Page threshold, if the available virtual memory falls below the internal threshold, the statement execution goes into the page mode and displays the results that have been retrieved so far. In this case, the grid might display fewer rows than the Rows/Page threshold. This situation continues until garbage collection begins, and the memory occupied by the pages that have been disposed becomes available again. When the system resources are low, the Statement Details pane displays a message that execution is in the entering page mode.

  • Grid sorting works only on the currently displayed page.

  • In the page mode, the following options copy data for the current page only:

    • Data to Clipboard
    • Data to Browser
    • Data to Spreadsheet
    • Data to File
  • To free workstation memory, you can discard the results of the selected statement(s) cached in the Statement List pane. For more information, see Statement List pane.