CODEWITHSUNDEEP
javaperformancesql-server-2008jdbcpagination
goutthee
goutthee

Reputation: 329

Retrieve and Insert million records into table

there's column I want to retrieve and insert into another table For example, below is first table I want to retrieve values

Table1 Records 1 ABC Singapore 2 DEF Vietnam

I retrieve above column value from Table1, then insert into another table as below

Table 2 ID Name Country 1 ABC Singapore 2 DEF Vietname

Currently, I can do with java, I first retrieve records then split the values and insert. However, I want to do it by batch or pagination for better performance when Table1 got million of records to retrieve and insert those million records into Table2.

Any pointer to show me how to use pagination in my case would be appreciated.

I"m use MSSQL 2008

Upvotes: 2

Views: 3429

Answers (1)

Jan
Jan

Reputation: 13858

If you need to do that in code (and not in SQL which should be easier even with multiple delimiters), what you probably want to use would be batched inserts with proper batch size combined with a good fetch-size on your select:

    //Prepare statements first
    try(PreparedStatement select = con.prepareStatement("SELECT * FROM SOURCE_TABLE");
            PreparedStatement insert = con.prepareStatement("INSERT INTO TARGET_TABLE(col1, col2, col3) VALUES (?,?,?)")) {

        //Define Parameters for SELECT
        select.setFetchDirection(ResultSet.FETCH_FORWARD);
        select.setFetchSize(10000);

        int rowCnt = 0;

        try(ResultSet rs = select.executeQuery()) {
            while(rs.next()) {
                String row = rs.getString(1);
                String[] split = row.split(" |\\$|\\*"); //However you want to do that
                //Todo: Error handling for array length

                //Todo: Type-Conversions, if target data is not a string type
                insert.setString(1, split[0]);
                insert.setString(2, split[1]);
                insert.setString(3, split[2]);
                insert.addBatch();

                //Submit insert in batches of a good size:
                if(++rowCnt % 10000 == 0) {
                    int[] success = insert.executeBatch();
                    //Todo: Check if that worked.
                }
            }
            //Handle remaining inserts
            int[] success = insert.executeBatch();
            //Todo: Check if that worked.               
        }

    } catch(SQLException e) {
        //Handle your Exceptions
    }

On calculating on "good" fetch and batch sizes you'll want to consider some parameters:

  • Fetchsize impacts memory consumption in your client. If you have enough of that you can make it big.

  • Committing an insert of millions of rows will take some time. Depending on your requirements you might want to commit the insert transaction every once in a while (every 250.000 inserts?)

  • Think about your transaction isolation: Make sure auto-commit is turned off as committing each insert will make most of the batching gains go away.

Upvotes: 1

Related Questions