Thanks Atul. The MSGDATE field is part of the primary key so it will be indexed by default. However, speed is not as important as not throwing Out Of Memory Exceptions.
The problem with batching in this case is that I do not know how many records will be deleted in advance. Are you suggesting that I query for the number of records first and then add (n+1) "DELETE ... LIMIT 10000" to my batch where (n) equals the number (TOTAL/10000) . On Tuesday, July 15, 2014 3:23:42 PM UTC-4, Atul wrote: > > 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] > <javascript:>> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
