Use JDBC's batching capabilities: https://www.google.com/search?q=jdbc%20batch
Also review your query plan etc etc... On Tue, Jul 15, 2014 at 2:33 PM, Kenton Garner <[email protected]> wrote: > Update... > I modified my query and had success - took 12 minutes to run though. > >> while ( (nRows = stmt.executeUpdate( "DELETE CDS_HISTORY WHERE MSGDATE >> <= '2014-07-13 22:21:37.048000' LIMIT 10000" )) > 0 ) >> > > Can you think of a better way? > > > > On Tuesday, July 15, 2014 12:03:16 PM UTC-4, Kenton Garner wrote: >> >> H2 Version: 1.4.179 >> Java 7 >> URL: jdbc:h2:../dbFile;COMPRESS=TRUE;AUTOCOMMIT=ON;MVCC=TRUE; >> DB_CLOSE_ON_EXIT=FALSE;MV_STORE=FALSE;AUTO_SERVER=TRUE; >> AUTO_SERVER_PORT=8700 >> Query: DELETE CDS_HISTORY WHERE MSGDATE <= '2014-07-13 22:21:37.048000 >> Expected number of rows to delete 2.1 Million. >> >> I assume the transaction log is causing the issue, but that is just a >> guess. Note: I currently have auto commit enabled. >> These rows contain a relatively small amount of data, but I have plains >> to do a similar delete with a large amount of data. >> >> I Oracle PL/SQL I would probably get a cursor and loop committing (n) >> number of deletes at a time. >> But I have never tried this with JDBC and I would expect looping on the >> resultSet will be really slow. >> >> Do you have any suggestions? >> >> Attached JdbcSQLException... >> >> Caused by: org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement: >>> DELETE CDS_HISTORY WHERE MSGDATE <= '2014-07-13 22:21:37.048000' >>> [90108-179] >>> at org.h2.message.DbException.getJdbcSQLException( >>> DbException.java:345) >>> at org.h2.message.DbException.get(DbException.java:168) >>> at org.h2.message.DbException.convert(DbException.java:289) >>> at org.h2.table.RegularTable.removeRow(RegularTable.java:407) >>> at org.h2.command.dml.Delete.update(Delete.java:94) >>> at org.h2.command.CommandContainer.update(CommandContainer.java:78) >>> at org.h2.command.Command.executeUpdate(Command.java:253) >>> at org.h2.jdbc.JdbcStatement.executeUpdateInternal( >>> JdbcStatement.java:131) >>> at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:116) >>> at com.issinc.cds.sbrecv.history.RecvHistoryDBStore. >>> deleteOlderThanMsgDate(RecvHistoryDBStore.java:450) >>> ... 1 more >>> Caused by: java.lang.OutOfMemoryError: Java heap space >>> at java.util.HashMap.resize(HashMap.java:584) >>> at java.util.HashMap.addEntry(HashMap.java:883) >>> at java.util.HashMap.put(HashMap.java:509) >>> at java.util.HashSet.add(HashSet.java:217) >>> at org.h2.index.PageDataIndex.remove(PageDataIndex.java:359) >>> at org.h2.table.RegularTable.removeRow(RegularTable.java:389) >>> ... 7 more >>> >>> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
