Here is what has helped me:
If you know that your source data is correct, you might wat to disable
referential integrity during the import and enable it afterwards
again.
Give yourself plenty of heap and cache.
Use prepared statements for the insert.
If you're running update statements on the imported data, also try to
do it in smaller chunks.
For instance, to delete unwanted data, build a list of ID_NUMBER (or
whatever your unique field is) and execute the statement whenever you
have 1000 valid records.
for(int c = 1; c < cellValues.length; c++){//f skip heading
currentRow++;
value = cellValues[c];
if(value != null && !
value.equals("")){
if(validRow > 0){
if(validRow%1000 == 0){
if(sqlCon.createStatement().executeUpdate("delete from item where
item_number in(" + buff + ")") == 0){
// nothing deleted... so handle it here
}
buff.setLength(0);
}else{
buff.append(",");
}
}
buff.append("'" +
value.replaceAll("'", "") + "'");
validRow++;
}
}
// before the import starts
destCon.createStatement().executeUpdate("SET REFERENTIAL_INTEGRITY
false");
destCon.createStatement().execute("SET CACHE_SIZE 32768*28");
// at the end.
destCon.createStatement().executeUpdate("SET REFERENTIAL_INTEGRITY
true");
destCon.createStatement().execute("SET CACHE_SIZE 32768");
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
