Oracle Batch Insert w/No Primary Key Missing Inserts

Question

I'm trying to insert over 100,000 records into an Oracle 9i table with no primary key using the ojdbc14.jar driver and Spring's SimpleJdbcTemplate batchUpdate method. Here's my code snippet:

private static final String TABLE_INSERT = "insert into TABLE_FINAL (ID, START_TIME, VALUE) VALUES (ID_SEQ.NEXTVAL, :startTime, :value)";

log.info("inputData list size={}",inputData.size());
Object[] dataArray = inputData.toArray();
log.info("dataArray length={}",dataArray.length);

final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(inputData.toArray());
log.info("SqlParamterSource length={}", batch.length);

final int[] inserted = getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);

for(int i=0; i < inserted.length; i++){
if(inserted[i] != -2){
    System.out.println("i="+i +" insert[i]="+inserted[i]);
    System.out.println(batch[i]);
}

}

The size of the inputData List, dataArray, and batch length are all the same expected value. The batchUpdate completes without throwing any exceptions and the subsequent for loop prints nothing as every item in the inserted array returns -2 (success). However, only 42,000 records are persisted to the destination table instead of the 100,000+ records expected.

If I replace the batchUpdate with looping over the input collection and performing an update per item, the 100,000+ records are persisted. I would like to use the batchUpdate, however, to take advantage of the improved performance.

Does anyone have any ideas as to why the batchUpdate doesn't work? I can't help but think it has something to do with the missing primary key.

Here's data from the source table that's used to populate the inputData List:

0.1933,-0.0253,0,0,4/16/2011 5:00:00 AM,4/16/2011 6:00:00 AM,12,9,1,1
0.1917,-0.0253,0,0,4/16/2011 6:00:00 AM,4/16/2011 7:00:00 AM,12,9,1,1
0.1936,-0.0253,0,0,4/16/2011 7:00:00 AM,4/16/2011 8:00:00 AM,12,9,1,1
0.2017,-0.0253,0,0,4/16/2011 8:00:00 AM,4/16/2011 9:00:00 AM,12,9,1,1
0.2083,-0.0253,0,0,4/16/2011 9:00:00 AM,4/16/2011 10:00:00 AM,12,9,1,1
0.2133,-0.0253,0,0,4/16/2011 10:00:00 AM,4/16/2011 11:00:00 AM,12,9,1,1
0.2238,-0.0253,0,0,4/16/2011 11:00:00 AM,4/16/2011 12:00:00 PM,12,9,1,1
0.2309,-0.0253,0,0,4/16/2011 12:00:00 PM,4/16/2011 1:00:00 PM,12,9,1,1
0.2319,-0.0253,0,0,4/16/2011 1:00:00 PM,4/16/2011 2:00:00 PM,12,9,1,1
0.231,-0.0253,0,0,4/16/2011 2:00:00 PM,4/16/2011 3:00:00 PM,12,9,1,1
0.2283,-0.0253,0,0,4/16/2011 3:00:00 PM,4/16/2011 4:00:00 PM,12,9,1,1
0.2216,-0.0253,0,0,4/16/2011 4:00:00 PM,4/16/2011 5:00:00 PM,12,9,1,1
0.2164,-0.0253,0,0,4/16/2011 5:00:00 PM,4/16/2011 6:00:00 PM,12,9,1,1
0.2155,-0.0253,0,0,4/16/2011 6:00:00 PM,4/16/2011 7:00:00 PM,12,9,1,1
0.2162,-0.0253,0,0,4/16/2011 7:00:00 PM,4/16/2011 8:00:00 PM,12,9,1,1
0.2187,-0.0253,0,0,4/16/2011 8:00:00 PM,4/16/2011 9:00:00 PM,12,9,1,1
0.2203,-0.0253,0,0,4/16/2011 9:00:00 PM,4/16/2011 10:00:00 PM,12,9,1,1
0.2296,-0.0253,0,0,4/16/2011 10:00:00 PM,4/16/2011 11:00:00 PM,12,9,1,1
0.2323,-0.0253,0,0,4/16/2011 11:00:00 PM,4/17/2011,12,9,1,1
0.2293,-0.0253,0,0,4/17/2011,4/17/2011 1:00:00 AM,12,9,1,1
0.2154,-0.0253,0,0,4/17/2011 1:00:00 AM,4/17/2011 2:00:00 AM,12,9,1,1
0.2088,-0.0253,0,0,4/17/2011 2:00:00 AM,4/17/2011 3:00:00 AM,12,9,1,1
0.202,-0.0253,0,0,4/17/2011 3:00:00 AM,4/17/2011 4:00:00 AM,12,9,1,1
0.1916,-0.0253,0,0,4/17/2011 4:00:00 AM,4/17/2011 5:00:00 AM,12,9,1,1

and here's what gets persisted after the batchUpdate:

47987296,4/19/2011 4:37:15 PM,0.1933,-0.0253,4/16/2011 5:00:00 AM,4/16/2011 6:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47961249,4/19/2011 4:37:15 PM,0.2238,-0.0253,4/16/2011 11:00:00 AM,4/16/2011 12:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47966094,4/19/2011 4:37:15 PM,0.2309,-0.0253,4/16/2011 12:00:00 PM,4/16/2011 1:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47968596,4/19/2011 4:37:15 PM,0.2319,-0.0253,4/16/2011 1:00:00 PM,4/16/2011 2:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47972962,4/19/2011 4:37:15 PM,0.231,-0.0253,4/16/2011 2:00:00 PM,4/16/2011 3:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47978129,4/19/2011 4:37:15 PM,0.2283,-0.0253,4/16/2011 3:00:00 PM,4/16/2011 4:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47982943,4/19/2011 4:37:15 PM,0.2216,-0.0253,4/16/2011 4:00:00 PM,4/16/2011 5:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
48005719,4/19/2011 4:37:15 PM,0.2164,-0.0253,4/16/2011 5:00:00 PM,4/16/2011 6:00:00 PM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47990490,4/19/2011 4:37:15 PM,0.2088,-0.0253,4/17/2011 2:00:00 AM,4/17/2011 3:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
47993531,4/19/2011 4:37:15 PM,0.202,-0.0253,4/17/2011 3:00:00 AM,4/17/2011 4:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011 
48000722,4/19/2011 4:37:15 PM,0.1916,-0.0253,4/17/2011 4:00:00 AM,4/17/2011 5:00:00 AM,4/19/2011 4:37:28 PM,9,12,1,1,04-15-2011

The 24 rows in the source table should also have 24 rows in the destination table, but only 11 rows get populated.

I modified the table to use the ID as the primary key, then retried the persistence with batchUpdate. Unfortunately, batchUpdate still only persisted 47,000 records instead of 100,000. — jlentz, Apr 18 '11 at 20:22
What happens with the value of the sequence? Does this reflect the 100000 or the 47000? When do you do the commit? — , Apr 19 '11 at 05:44
The sequence reflects the 47000 count. The batchUpdate is the last method call in a message driven bean's onMessage method so the commit happens after the batchUpdate's successful return. — jlentz, Apr 19 '11 at 14:22
do you happen to have NULL in your data that could end the insert? — , Apr 19 '11 at 14:28
I added a check for the inputData List to see if any entries were null. There were none. I'm also now examining each item in the inputData List to make sure all required fields are non-null and they are. — jlentz, Apr 19 '11 at 16:42

score 1 · Accepted Answer · answered May 05 '11 at 17:51

When using SimpleJdbcTemplate.batchUpdate(String sql, SqlParameterSource[] source) with ojdbc14.jar and large amounts of data (over 60K), data was missing from the destination table as I described in my original posting. I've discovered that if I break the input data into 10K chunks, the data is persisted successfully. I also tried using the JdbcTemplate.batchUpdate(String [] sql) method which persisted correctly, but was slower than looping and calling SimpleJdbcTemplate.update. On the plus side, JdbcTemplate.batchUpdate(String [] sql) returns an int[] where each item in the array contains the number of rows impacted.

I changed my Oracle driver to ojdbc6.jar and retested using SimpleJdbcTemplate.batchUpdate(String sql, SqlParamterSource[] source) passing in all 100,000+ source records and it worked!! Unfortunately, we have other dependencies that require the ojdbc14.jar so we can't upgrade yet.

For the final solution, the data will be broken into 10K chunks as shown below and a sql query that validates the data was persisted will be added after the batchUpdate.

if(inputData.size() > 10000){

            int beginIndex =0;
            int endIndex = 10000;
            List<InputData> partialList = null;
            while(beginIndex < inputData.size()){
                partialList = inputData.subList(beginIndex, endIndex);

                final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(partialList.toArray());

                getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);

                beginIndex = endIndex;
                endIndex = endIndex + 10000 < inputData.size() ? endIndex + 10000 : inputData.size();
            }
} else{

            final SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(inputData.toArray());
            getJdbcTemplateJoa().batchUpdate(TABLE_INSERT, batch);

        }

score 0 · Answer 2 · answered Apr 20 '11 at 14:40

0

Perhaps there have been exceptions that were catched and not passed through. Try installing a servererror trigger to find out if Oracle passed any exceptions to the client.

Here you'll find an example.

BTW, I'd be interested in the performance improvement you achieve once it's working. I wouldn't be surprised if it doesn't make a difference....

answered Apr 20 '11 at 14:40

HAL 9000

3,877
1
23
29

Our DBA set up the trigger with logging to an error_log table for us as described in the link you provided. Unfortunately, only SQL errors were recorded in the table (table/view doesn't exist, for instance). Thank you for the suggestion, though. – jlentz May 05 '11 at 15:20

Oracle Batch Insert w/No Primary Key Missing Inserts

2 Answers2