Configuring Query Processing Modes

To optimize connector performance, you can modify how the connector runs queries and retrieves results into memory. For example, you can configure the connector to return entire query results into memory all at once, or one row at a time. Use a query processing mode that prevents queries from consuming too much memory, based on the expected result size of your queries and the specifications of your system.

Note:

Use Single Row Mode if you plan to query large results and you do not want to retrieve the entire result into memory. Using the other query processing modes increases performance, but can result in out-of-memory errors.

You can set the connection properties described below in a connection string or in a DSN (in the odbc.ini file). Settings in the connection string take precedence over settings in the DSN.

Enabling Single Row Mode

You can configure the connector to return query results one row at a time.

To enable Single Row Mode:

  1. Set the SingleRowMode property to 1.
  2. Make sure that the UseDeclareFetch property is set to 0 or not set.

Enabling Declare/Fetch Mode

You can configure the connector to return a specific number of rows at a time.

To enable Declare/Fetch Mode:

  1. Set the UseDeclareFetch property to 1.
  2. Set the Fetch property to the number of rows that the connector returns at a time.

Enabling Retrieve Entire Result Mode

You can configure the connector to return entire query results into memory.

To enable Retrieve Entire Result Mode:

  • Make sure that the SingleRowMode, UseDeclareFetch, and UseMultipleStatements properties are set to 0 or not set.

Enabling Multiple Statements Mode

The connector can have more than one query, separated by a semicolon ( ; ), in a single SQLExecDirect call. The connector returns all the query results into memory.

To enable Multiple Statements Mode:

  1. Set the UseMultipleStatements property to 1.

  2. Make sure that the SingleRowMode and UseDeclareFetch properties are set to 0 or not set.

Enabling Enforce Single Statement Mode

You can configure the connector to return SQL_ERROR immediately for any other queries that is executed if there is already an active query in execution under the same connection.

To enable Enforce Single Statement Mode:

  1. Set the EnforceSingleStatement property to 1.
  2. Make sure that the UseMultipleStatements is set to 0 or not set.