I am trying to get a feel for if I am doing something wrong with or if
H2 is not designed to handle what I am trying to do.
I have a simple one-to-many parent/child table. For purposes of our
discussion, call the one table P for parent and the many table C for
child.
I have lots of inserts happening on these tables. For the P table, 50
inserts/sec along with 0-3 inserts for the C child for each P insert.
I have a UI that performs a left join on the tables to get a limited
set of results along with the total number of results available.
For example:
1) SELECT P.ID, P.CODE, P.COMPONENT, P.STATEMENT, P.PARENT_TIME,
C.NAME, C.VALUE FROM PARENT P LEFT JOIN CHILD C ORDER BY P.PARENT_TIME
DESC
2) SELECT COUNT(DISTINCT P.ID) FROM FROM PARENT P LEFT JOIN CHILD C
On a table with a 300,000 rows on it on my machine, with an index on
PARENT_TIME DESC, I get results for query 1) in about 100ms, but query
2) takes on the order 1500ms. I realize that 2) doesn't need the left
join, but it is there because it is a dynamically built query. An
explain on query 1) shows an index scan, but query 2) is doing a table
scan.
Another example is:
1) SELECT P.ID, P.CODE, P.COMPONENT, P.STATEMENT, P.PARENT_TIME,
C.NAME, C.VALUE FROM PARENT P LEFT JOIN CHILD C WHERE C.VALUE='ABC'
ORDER BY P.PARENT_TIME DESC
2) SELECT COUNT(DISTINCT P.ID) FROM FROM PARENT P LEFT JOIN CHILD C
WHERE C.VALUE='ABC'
Again, with an index on C.VALUE, I get results for query 1) on the
order of 120ms, but query 2) is still 1500ms. The EXPLAIN for query 1)
doesn't indicate what it is doing with the C.VALUE='ABC', but query 2)
again states that it is doing a table scan.
FInally, if I change the query to
1) SELECT P.ID, P.CODE, P.COMPONENT, P.STATEMENT, P.PARENT_TIME,
C.NAME, C.VALUE FROM PARENT P LEFT JOIN CHILD C WHERE P.COMPONENT
IN('ABC', 'DEF') ORDER BY P.PARENT_TIME DESC
2) SELECT COUNT(DISTINCT P.ID) FROM FROM PARENT P LEFT JOIN CHILD C
WHERE P.COMPONENT IN('ABC', 'DEF')
Query 1) and 2) both now go to 2200 ms each. The EXPLAIN for query 1)
states that it is using the index for the P.COMPONENT column, so I
don't understand why the increase in select time. Query 2) is still a
table scan.
I have no problems with the inserts. Inserts are on the order of
1-2ms.
>From my limited understanding of how databases are supposed to work,
this doesn't seem like it should be. I really need some help in
understanding how to improve the performance on the selects,
especially with the aggregate COUNT. I am using version 1.3.163.
Thanks for the help.
Chris
--
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.
