Difference between fetching with server cursor vs streaming

Question

What's the difference, behaviour-wise, between fetching results using server cursors and fetching using streaming?

The former (server cursors) can be activated:

• For every statement by setting useCursorFetch=true and defaultFetchSize=N in the connection properties. (Where N is a number greater than zero.)

• Or individual statements by initially setting useCursorFetch=true or com.mysql.jdbc.Connection.setUseCursorFetch(true) on the connection, followed by java.sql.Statement.setFetchSize(N) on the statement.

The latter (streaming) can be activated:

• On individual statements by setting java.sql.Statement.setFetchSize(Integer.MIN_VALUE) or by calling com.mysql.jdbc.Statement.enableStreamingResults() on the statment.

• And probably on every statement by setting defaultFetchSize=X in the connection properties, where X is a number equal to Integer.MIN_VALUE.

What does one need to consider when developing with these alternative methods? A good answer may touch on topics such as performance, lock holding, and resource allocation (/ deallocation).

Answer 1

Examining Mysql's Connector/J source code (v5.1.39):

When using server cursor (setUseCursorFetch(true)) and if the result set type is TYPE_FORWARD_ONLY, it seems like the "streaming" mode is just a special case where the fetched block is 1-row only:

////// RowDataCursor.java /////////

private void fetchMoreRows() throws SQLException {
    ...

    synchronized (this.owner.connection.getConnectionMutex()) {
        ...

        int numRowsToFetch = this.owner.getFetchSize();
        ...

        if (numRowsToFetch == Integer.MIN_VALUE) {
            // Handle the case where the user used 'old' streaming result sets

            numRowsToFetch = 1;
        }

See also MysqlIO.java for the conditions where RowDataCursor is instantiated:

    //
    // Handle cursor-based fetch first
    //

    if (this.connection.versionMeetsMinimum(5, 0, 2) && this.connection.getUseCursorFetch() && isBinaryEncoded && callingStatement != null
            && callingStatement.getFetchSize() != 0 && callingStatement.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY) {
        ServerPreparedStatement prepStmt = (com.mysql.jdbc.ServerPreparedStatement) callingStatement;

        boolean usingCursor = true;

MySQL 5.7 documentation indicates that using server cursor generates a temporary table at the server side (memory table if size allows) so this may affect server performance.