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.