Apparent Performance regression from version 2007-12-27 to current

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. create database with number and string fields
2. create non-unique index on those columns
3. perform a lookup with those two parameters

With the earlier version, I am getting lookups of about 1ms, whereas I am
getting 250ms lookups in the current release.

I have checked both databases, and both seem to have the correct indexes
created.

We are using Java 6, hibernate, and I am on a linux system, though the
problem seems to appear on Windows as well.  

I think that we first noticed this issue in the Jan or Feb release.  It
definitely appears in the 3/29 and 4/20 ones.  If you need me to write a
small test program to demonstrate this, please let me know

Original issue reported on code.google.com by tim.br...@gmail.com on 23 Apr 2008 at 4:12

[GoogleCodeExporter]

Sorry that I mark it as 'invalid', but I can not reproduce this problem 
unfortunately
and would need to have a test case. I made one but I can't find a problem so 
far.

drop all objects;
create table test(id int, name varchar);
create index idx_i on test(id);
create index idx_n on test(name);
@LOOP 10000 insert into test values(?, ?);
@LOOP 1000 select * from test where id = ?;
@LOOP 1000 select * from test where name = ?;

Original comment by thomas.t...@gmail.com on 23 Apr 2008 at 8:47

• Changed state: Invalid

[GoogleCodeExporter]

Just re-open this bug when posting the test case.

Original comment by thomas.t...@gmail.com on 23 Apr 2008 at 8:48

[GoogleCodeExporter]

I will work on that test case.  Your example wasn't exactly what I meant, but I 
tried
it using your methods and it doesn't show the problem either.  The index was 
supposed
to be on both id and name.  But, like I said, a similar thing to what you did 
seems
fine.  I will get this test case done when I can.  Thanks for looking.

Original comment by tim.br...@gmail.com on 23 Apr 2008 at 9:18

[GoogleCodeExporter]

Okay, after trying to build a test script and failing I have figured out what 
it is
and am not sure it should be considered a bug.  I will leave that to you:

We have a table, say test(id int, dtype int, name varchar, data varchar)
We also have index(id), index(id, name), index(id, dtype, data)

Then, we do the query:  select * from test where dtype in (...) and id=? and 
name=?

It just so happens that:
  1) h2 v 12/27/07 uses index(id,name)
  2) h2 v newer uses index(id,dtype,data)
  3) the column that *really* restricts the result set is the name column, so the
older version of h2 is doing the 'right' thing in this query's case.

Perhaps I should just do a hint or something, unless you have another 
suggestion.

Thanks,

Tim

Original comment by tim.br...@gmail.com on 25 Apr 2008 at 2:43

[GoogleCodeExporter]

Did you try ANALYZE? See
http://www.h2database.com/html/grammar.html#analyze
If this doesn't help, could you send me some test data so I can reproduce the 
problem?

Original comment by thomas.t...@gmail.com on 25 Apr 2008 at 2:52

[GoogleCodeExporter]

I dont think I have rights to reopen this, so maybe I will open another ticket
referring to this one...

Original comment by tim.br...@gmail.com on 25 Apr 2008 at 2:57

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 25 Apr 2008 at 2:59

• Changed state: Accepted

[GoogleCodeExporter]

analyzing the db does work.  how often should this be run?

thanks.

Original comment by tim.br...@gmail.com on 25 Apr 2008 at 3:02

[GoogleCodeExporter]

It doesn't need to be run a lot, I guess after the initial load if there is 
one. Or,
when you have meaningful test data, after inserting the test data. Afterwards, 
the
schema (the SQL script when you run SCRIPT NODATA) will contain the statistics 
(...
SELECTIVITY...).

So I will close this issue

Original comment by thomas.t...@gmail.com on 25 Apr 2008 at 3:22

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 26 Apr 2008 at 8:39

• Changed state: Fixed