Multiple active result sets (MARS)

You can only enable MARS when you connect to Vertica using a JDBC client connection.

You can only enable MARS when you connect to Vertica using a JDBC client connection. MARS allows the execution of multiple queries on a single connection. While ResultBufferSize sends the results of a query directly to the client, MARS stores the results first on the server. Once query execution has finished and all of the results have been stored, you can make a retrieval request to the server to have rows returned to the client.

MARS is set at the session level and must be enabled for every new session. When MARS is enabled, ResultBufferSize is disabled. No error is returned, however the ResultBufferSize parameter is ignored.

Benefits of MARS

In comparison with ResultBufferSize, MARS enables you to store multiple result sets from different queries at the same time. You can also send new queries before all of the results of a previous result set have been returned to the client. This allows applications to decouple query execution from result retrieval so that, on a single connection, you can process different results at the same time.

When you enable ResultBufferSize, you must wait until all result sets have been returned to the client before a new query can be executed.

Another benefit of MARS is that it allows you to free up query resources faster than ResultBufferSize allows. While a query is running, resources are held by that query session. When ResultBufferSize is enabled, a client that is performing slowly might read a single row of a result set and then have to stop to retrieve the next row. This prevents the query from finishing quickly and, therefore, prevents the resources used from being freed up for other applications. With MARS, the speed of the client is irrelevant to the reading of rows. As soon as the results are written to the MARS storage, the resources are freed and the speed at which the client retrieves rows no longer matters.

Query execution with MARS

The following graphic demonstrates how multiple queries to the server are handled when MARS is enabled:

Query 1:

  1. Query 1 is sent to the server.

  2. Query 1's row description and the status of its result set are returned to the client. However, no results are returned to the client at this time.

  3. Query 1 completes and its results are saved on the server.

    1. You can now send commands to retrieve the rows of Query 1's result set. These rows are stored on the server. Retrieved rows are sent to the client along with the status of the result set. By keeping track of the status of the result set, Vertica is able to keep track of which rows have been retrieved from the server.

  4. Now that Query 1 has successfully completed, and its result sets are being stored on the server, Query 2 can be executed.

Query 2:

  1. Query 2 is sent to the server.

  2. Query 2's row description and the status of its result set are returned to the client. However, no results are returned to the client at this time.

  3. Query 2 completes and its results are stored on the server. Both Query 1 and Query 2 now have result sets stored on the server.

  4. You can now send retrieval requests to both Query 1 and Query 2's result sets that are stored on the server. Whenever a retrieval request is made for rows from Query 1, the request is sent and rows and the result set status are sent to the client. The same occurs for Query 2.

Once all rows have been read by the client, the MARS storage on the server closes the active results session. The MARS storage on the server is then freed to store more data. The MARS storage also closes and frees once your session is finished.

Enabling and disabling MARS

You can enable and disable MARS in two different ways:

  1. To enable MARS using the JDBC client connection properties, see JDBC connection properties.

  2. To enable MARS using the SET SESSION command, see SET SESSION MULTIPLEACTIVERESULTSETS.

See also