After more investigation I strongly suspect that IO is my bottleneck, but the disk read rate is low because there are very many seeks being generated by h2.
I expect PAGE_SIZE is the best way to address this. Is that correct? Does row insert order affect this much? For example, would alternate inserts into two tables result in the data being more spatially distributed on the disk than writing first one table, then the other? On Tuesday, October 8, 2013 5:41:57 PM UTC-7, Brian Craft wrote: > > I'm wondering if these numbers look typical of h2, or if I still have some > bottleneck I haven't identified. > > I have a probes table (mentioned in other threads) with one index over two > columns: eid (int) and name (varchar). (eid, name) is unique. For each eid > there might be 10k-500k rows. > > The table has about 38M rows. A query that retrieves 500 rows matching a > single eid with 240k rows, takes about 5 seconds. > > This is using the TABLE() inner join, instead of a big IN query. The > explain analyze output shows it hitting the index. > > If I close the db and re-open it, the same query takes much less than a > second, which I suspect reflects the OS block cache. Though, as I mentioned > earlier, the disk doesn't seem very strained during the first query. I'm > not sure what that means. > > The query: > SELECT `probes`.`name` as `gene`, `probes`.`id` FROM `probes` > INNER JOIN TABLE(name varchar=?) T ON T.`name`=`probes`.`name` WHERE > (`probes`.`eid` = ?) > > The explain analyze: > > SELECT > PROBES.NAME AS GENE, > PROBES.ID > FROM TABLE(NAME VARCHAR=?1) T > /* function */ > /* scanCount: 501 */ > INNER JOIN PUBLIC.PROBES > /* PUBLIC.EID_NAME_INDEX_8: EID = ?2 > AND NAME = T.NAME > */ > ON 1=1 > /* scanCount: 1000 */ > WHERE (PROBES.EID = ?2) > AND (T.NAME = PROBES.NAME) > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
