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.
