Caching result set data sample

Download JDBC driver

This Microsoft JDBC Driver for SQL Server sample application demonstrates how to retrieve a large set of data from a database. It then controls the number of rows of data that are cached on the client by using the setFetchSize method of the SQLServerResultSet object.

To set a limit on the number of rows cached on the client, specify a cursor type that uses a server-side cursor when creating Statement objects. For example, the JDBC driver provides the TYPE_SS_SERVER_CURSOR_FORWARD_ONLY cursor type, which is a fast forward-only, read-only server-side cursor for use with SQL Server databases.

After you have run the query in the Statement object and the data is returned to the client as a result set, call setFetchSize to control how much data is retrieved from the database at one time. For example, if you have a table with 100 rows of data, and the fetch size is 10, only 10 rows of data are cached on the client at a time. Although this setting can slow down the speed at which the data is processed, it uses less memory on the client. This scenario is useful when you need to process large amounts of data without using too much memory.

The code file for this sample is named CacheResultSet.java, and it can be found in the following location:

\<installation directory>\sqljdbc_<version>\<language>\samples\resultsets

Requirements

To run this sample application, set the classpath to include the mssql-jdbc jar file. You also need access to the AdventureWorks2022 sample database. For more information about how to set the classpath, see Using the JDBC Driver.

Example

In the following example, the sample code makes a connection to the AdventureWorks2022 sample database. Then it uses an SQL statement with the SQLServerStatement object, specifies the server-side cursor type, and runs the SQL statement. The data is returned in a SQLServerResultSet object.

Next, the sample code calls the custom timerTest method, passing as arguments the fetch size to use and the result set. The timerTest method then sets the fetch size of the result set by using the setFetchSize method, sets the start time of the test, and then iterates through the result set with a While loop. As soon as the While loop is exited, the code sets the stop time of the test, and then displays the result of the test including the fetch size, the number of rows processed, and the time it took to execute the test.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.microsoft.sqlserver.jdbc.SQLServerResultSet;

public class CacheResultSet {

    @SuppressWarnings("serial")
    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";

        try (Connection con = DriverManager.getConnection(connectionUrl);
                Statement stmt = con.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY);) {

            String SQL = "SELECT * FROM Sales.SalesOrderDetail;";

            for (int n : new ArrayList<Integer>() {
                {
                    add(1);
                    add(10);
                    add(100);
                    add(1000);
                    add(0);
                }
            }) {
                // Perform a fetch for every nth row in the result set.
                try (ResultSet rs = stmt.executeQuery(SQL)) {
                    timerTest(n, rs);
                }
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void timerTest(int fetchSize,
            ResultSet rs) throws SQLException {

        // Declare the variables for tracking the row count and elapsed time.
        int rowCount = 0;
        long startTime = 0;
        long stopTime = 0;
        long runTime = 0;

        // Set the fetch size then iterate through the result set to
        // cache the data locally.
        rs.setFetchSize(fetchSize);
        startTime = System.currentTimeMillis();
        while (rs.next()) {
            rowCount++;
        }
        stopTime = System.currentTimeMillis();
        runTime = stopTime - startTime;

        // Display the results of the timer test.
        System.out.println("FETCH SIZE: " + rs.getFetchSize());
        System.out.println("ROWS PROCESSED: " + rowCount);
        System.out.println("TIME TO EXECUTE: " + runTime);
        System.out.println();
    }
}

See also

Working with result sets