How can I troubleshoot and resolve the issue of “too many rows 50001 error”?

In the context of Salesforce, here are the steps given:-

Checking the query

Firstly, try to review the SQL-based query that is causing the issue to understand its structure and why it might be returning more than 50001 rows.

Pagination

You can implement pagination to break the large results sets into smaller manageable chunks. Here is the Java program given of how you can implement pagination to fetch large datasets:-

Import java.sql.Connection;
Import java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import java.sql.ResultSet;
Import java.sql.SQLException;

Public class PaginationExample {
    Private static final String DB_URL = “jdbc:oracle:thin:@your-db-url:1521:your-db”;
    Private static final String USER = “your-username”;
    Private static final String PASS = “your-password”;
    Private static final int PAGE_SIZE = 1000;
    Public static void main(String[] args) {
        Try {
            // Load the Oracle JDBC driver
            Class.forName(“oracle.jdbc.driver.OracleDriver”);
            // Establish a connection
            Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
            // Define the query with pagination
            String sql = “SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY some_column) AS rnum, sales_data.* FROM sales_data WHERE conditions) WHERE rnum BETWEEN ? AND ?”;
            // Initialize pagination parameters
            Int startRow = 1;
            Int endRow = PAGE_SIZE;
            Boolean moreRows = true;
            While (moreRows) {
                // Prepare the statement
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, startRow);
                preparedStatement.setInt(2, endRow);
                // Execute the query
                ResultSet resultSet = preparedStatement.executeQuery();
                // Process the result set
                If (!resultSet.next()) {
                    moreRows = false;
                } else {
                    Do {
                        // Process each row
                        // Example: System.out.println(resultSet.getString(“column_name”));
                    } while (resultSet.next());
                    // Move to the next page
                    startRow += PAGE_SIZE;
                    endRow += PAGE_SIZE;
                }
                resultSet.close();
                preparedStatement.close();
            }
            // Close the connection
            Connection.close();
        } catch (ClassNotFoundException e) {
            System.err.println(“Oracle JDBC driver not found.”);
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Subquery filtering

You can use the subquery for the purpose of limiting the number of rows process at a time.

Database Configuration

If the database can support you can adjust the setting related to Configuration for the purpose of allowing more rows in a single query.

Execute the query and process the results

ResultSet resultSet = preparedStatement.executeQuery();
If (!resultSet.next()) {
    moreRows = false;
} else {
    Do {
        // Process each row
    } while (resultSet.next());
    startRow += PAGE_SIZE;
    endRow += PAGE_SIZE;
}

This above approach can ensure that you do not hitted by the row limit error so that you can effectively handle the large datasets in your java application.