Hi, did you get any other solution
prakash On Thu, Dec 3, 2009 at 2:57 PM, Cheenu <[email protected]> wrote: > 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]<h2-database%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > > -- Keep in Touch Prakash [email protected] -- 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.
