Update works slower than expected

[GoogleCodeExporter]

1. Download and unzip attached test_project.zip.
2. Run "ant ps_true" for PAGE_STORE=TRUE variant.
3. Run "ant ps_false" for PAGE_STORE=FALSE variant.
4. Look at results.

Questions:
1. Why test2 performs faster than test1?
2. Why test with PAGE_STORE=TRUE works slower than test with PAGE_STORE=FALSE?
3. Why database size grows from 17342560 to 100958304 and from 20348928 to
68857856 bytes after tests?

I think that such results are caused by big values in "name" field
(AVG(LENGTH(name)) = 2437). Is it problem?

Original issue reported on code.google.com by victor.p...@gmail.com on 18 Nov 2009 at 12:53

Attachments:

• test_project.zip

[GoogleCodeExporter]

Test1 is as simple as

stat.executeUpdate("UPDATE test SET status = 'Y' WHERE status = 'N'");

Original comment by victor.p...@gmail.com on 18 Nov 2009 at 12:56

[GoogleCodeExporter]

Simplified test2:

   public static void test2(Connection conn) throws Exception {
      List<Integer> ids = new ArrayList<Integer>();

      Statement stat = conn.createStatement();
      ResultSet rs = stat.executeQuery("SELECT id FROM test WHERE status = 'N'");
      while (rs.next()) {
         ids.add(rs.getInt(1));
      }
      rs.close();
      stat.close();

      PreparedStatement prep = conn.prepareStatement("UPDATE test SET status = 'Y'
WHERE id = ?");
      for (Integer id : ids) {
         prep.setInt(1, id);
         prep.executeUpdate();
      }
      prep.close();
   }

Original comment by victor.p...@gmail.com on 18 Nov 2009 at 12:59

[GoogleCodeExporter]

My results for PAGE_STORE=TRUE

CREATE: 16 ms
FILL  : 2437 ms
TEST1 : 13734 ms
TEST2 : 3657 ms
TEST1 : 11156 ms
TEST2 : 3578 ms
TEST1 : 11203 ms
TEST2 : 3844 ms
ALL   : 49625 ms
SIZE  : 68857856

My results for PAGE_STORE=FALSE

CREATE: 0 ms
FILL  : 765 ms
TEST1 : 9828 ms
TEST2 : 2375 ms
TEST1 : 6938 ms
TEST2 : 2328 ms
TEST1 : 8109 ms
TEST2 : 3438 ms
ALL   : 33781 ms
SIZE  : 100958304

Original comment by victor.p...@gmail.com on 18 Nov 2009 at 1:01

[GoogleCodeExporter]

I have expanded the test for SQLite and MySQL (as "behavioral test" only).

In my opinion SQLite behaviour is more predictable:
1. Test1 is faster
2. Database size is not increased during the test

CREATE: 47 ms
FILL  : 2094 ms
TEST1 : 4719 ms
TEST2 : 7687 ms
TEST1 : 4688 ms
TEST2 : 7594 ms
TEST1 : 4359 ms
TEST2 : 7750 ms
ALL   : 38938 ms
SIZE  : 14484480

More remarks:
* Time is comparable to H2
* SQLite likes transactions

No comments about MySQL:

CREATE: 78 ms
FILL  : 125125 ms
TEST1 : 187 ms
TEST2 : 253204 ms
TEST1 : 187 ms
TEST2 : 242563 ms
TEST1 : 171 ms
TEST2 : 234969 ms
ALL   : 856484 ms

If you need I can attach modified project.

Original comment by victor.p...@gmail.com on 20 Nov 2009 at 7:23

[GoogleCodeExporter]

Hi,

Yes, H2 is slow in this case. There are multiple reasons:

- You have used VARCHAR_IGNORECASE. This is slow. Try VARCHAR.
  (how did you test VARCHAR_IGNORECASE in MySQL and SQLite?)
- Because you are updating all rows, each operation is quite big.
  The operations are written to a temporary file. 
  There is a feature request to fix that:
  "Support large updates (use the transaction log for rollback)."
- H2 doesn't do in-place updates. There is a feature request for this:
  "Performance: update in-place"

So, I will not try to improve the performance for this use case at the moment.
However, performance will increase when the features are implemented. 

I close the bug because the features required to improve performance are in the
roadmap already.

Original comment by thomas.t...@gmail.com on 22 Nov 2009 at 6:08

• Changed state: InRoadmap

[GoogleCodeExporter]

I try to use VARCHAR and CLOB. The result is the same.

Original comment by victor.p...@gmail.com on 23 Nov 2009 at 5:02