Just trued it. Good news, delete is MUCH faster (x 100 or so) after I have set the array data to null.
Bad news, the update is just as slow as the original delete so nothing overall gained. I think this simply confirms that a delete reads the whole row including the array, and so does an update. I think Thomas has said there are no short term plans to change this behaviour, but there are some tricks with a merge which I might look at. On May 13, 8:55 am, essence <[email protected]> wrote: > 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.
