ps One thing i forgot to add, the delete performance is MUCH faster on a table without any array data type. I can;t remember where I read it, but Thomas said that a delete always reads data, which would explain this difference.
What about doing a trick like doing an update first to remove the array data, (set it to null) and then delete the rows? Or does an update also read data before updating it? On May 12, 5:01 pm, essence <[email protected]> wrote: > I have improved it a bot by doing it 5 at a time with > > ps5 = connection.prepareStatement("delete from > doublearray where doublearray.wellresultid IN (?,?,?,?,?)"); > > and then using code above to do the rest. > > I am generally deleting about 400 rows. > > The critical thing is that the table has a column with a array type, > which holds about 3000 values. > > So if H2 has to read a row before deleting, the read may be the > bottleneck which is harder to avoid. > > I read somewhere about how a merge can be used. > > Are there any plans to avoid the read in a delete? > > On May 12, 6:37 am, essence <[email protected]> wrote: > > > > > ps. my latest and probably fastest so far is a bit crude: > > > ps1 = connection.prepareStatement("delete from > > doublearray where doublearray.wellresultid = ?"); > > for (int i = 0; i < arrayList.size(); i++) { > > ps1.setInt(1, (Integer) arrayList.get(i)); > > ps1.addBatch(); > > } > > ps1.executeBatch(); > > ps1.clearBatch(); > > > On May 12, 6:13 am, essence <[email protected]> wrote: > > > > I am getting a bit lost, Trying to improve efficiency of my delete > > > statements, which has an IN selection, and an id int primary key. > > > > In the manual it has: > > > > PreparedStatement prep = conn.prepareStatement( > > > "SELECT * FROM TABLE(X INT=?) T INNER JOIN TEST ON T.X=TEST.ID"); > > > prep.setObject(1, new Object[] { "1", "2" }); > > > ResultSet rs = prep.executeQuery(); > > > > and on a search with Google i found a message from Tom: > > > > PreparedStatement prep = conn.prepareStatement( > > > "SELECT * FROM users WHERE login IN (?)"); > > > prep.setObject(1, new Object[] { "1", "2" }); > > > ResultSet rs = prep.executeQuery(); > > > > but in my delete query I get an exception: > > > > org.h2.jdbc.JdbcSQLException: Data conversion error converting "(1, > > > 2)"; SQL statement: > > > Caused by: java.lang.NumberFormatException: For input string: "(1, 2)" > > > at > > > java.lang.NumberFormatException.forInputString(NumberFormatException.java: > > > 48) > > > at java.lang.Long.parseLong(Long.java:410) > > > at java.lang.Long.parseLong(Long.java:468) > > > at org.h2.value.Value.convertTo(Value.java:796) > > > > I am trying to filter on an attribute which has an id, of type int. > > > > I have tried > > > > ps1.setObject(1, new Object[]{new Integer(1),new > > > Integer(2)}); > > > > and > > > ps1.setObject(1, new Integer[]{1,2}); > > > > no joy. > > > > My prepared statement is: > > > > ps1 = connection.prepareStatement("delete from > > > doublearray where doublearray.wellresultid IN (?)"); -- 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.
