I have a database with 1 table of 500K rows.
This is a read-only lookup data table.
I open database, and load entire table in memory by running a select
to fetch each row
"jdbc:h2:db/h2/
test;ACCESS_MODE_LOG=r;ACCESS_MODE_DATA=r;CACHE_SIZE=1048576;CACHE_TYPE=SOFT_LRU"
I have indexed 2 columns on that table
username char(20)
city char(12)
But there is a huge difference in performance depending on which
column I use to filter
1. This query executes in 0.01 msec
SELECT * FROM test WHERE username = ?
2. This query takes 100 msec
SELECT * FROM test WHERE city = ?
The difference between the two is that query 1 always returns one row,
whereas query 2 returns a recordset
JProfiler shows the culprit to be somewhere near
org.h2.command.dml.Select.queryFlat
1031 invocations of org.h2.command.dml.Select.queryFlat resulted in
8,184,699 invocations of org.h2.result.LocalResult.addRow
90.5% - 1,960 s - 1,031 inv. org.h2.command.dml.Query.query
90.5% - 1,960 s - 1,031 inv.
org.h2.command.dml.Select.queryWithoutCache
86.2% - 1,867 s - 1,031 inv. org.h2.command.dml.Select.queryFlat
46.1% - 998 s - 8,184,699 inv. org.h2.result.LocalResult.addRow
21.8% - 473 s - 114,585,786 inv.
org.h2.expression.ExpressionColumn.getValue
13.2% - 284 s - 114,585,786 inv. org.h2.table.TableFilter.getValue
5.3% - 114 s - 8,184,699 inv. org.h2.index.IndexCursor.get
5.1% - 109 s - 8,184,699 inv. org.h2.index.BtreeCursor.get
4.6% - 99,488 ms - 8,184,699 inv. org.h2.index.BtreeIndex.getRow
Seems like some vast table scan is being done in case 2
Any idea what the problem is, and how to avoid it
Thanks
--
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.
