I quite often use a huge IN clause as a quick way to combine lots of
small queries. I'm fairly sure it uses the index (although I must admit
I haven't checked). But it usually speeds up the queries considerably.
Much faster than lots of small queries. Even in embedded mode for me.
On 8/10/2013 5:20 AM, Brian Craft wrote:
Sorry, the 120 sec is including some application code that is
apparently slower than I expected. The h2 trace log shows 9 sec for
the query. There is an index on the column, but it doesn't appear to
be used for the IN clause with 500 values. "explain analyze" shows it
scanning 50k rows or so.
I'll try the TABLE() thing instead of the big IN clause.
On Monday, October 7, 2013 12:33:36 PM UTC-7, Thomas Mueller wrote:
Hi,
> a "where ... in" clause with 500 values (like "where x in [v0,
v1, v2, .... v499]"), which ran with average time 120 seconds.
120 seconds for 500 entries sounds like a problem with the data or
the query. Do you have an index on that column? See also
http://h2database.com/html/performance.html#explain_plan
<http://h2database.com/html/performance.html#explain_plan>
Regards,
Thomas
On Sun, Oct 6, 2013 at 6:29 AM, Brian Craft <craft...@gmail.com
<javascript:>> wrote:
Reading over the archive on the subject of threads has left me
mostly still confused about how h2 handles concurrency, so
I've been doing some tests, instead.
I started with largish queries that all have a "where ... in"
clause with 500 values (like "where x in [v0, v1, v2, ....
v499]"), which ran with average time 120 seconds.
I then re-ran the same queries as sets of 500 queries (each
with a single value, like "where x in [v0]"), splitting the
queries across different threads. These sets of 500 queries
ran much faster, average 30 seconds. It also made much more
use of the disk: io stats where much higher, which is
consistent with the queries running concurrently.
The docs seem to indicate that a query in one thread blocks
queries in other threads, so I'm not sure how to interpret
these numbers. Maybe I misunderstood the docs, and queries do
run concurrently? I'm not using MULTI_THREADED.
--
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 h2-database...@googlegroups.com
<javascript:>.
To post to this group, send email to h2-da...@googlegroups.com
<javascript:>.
Visit this group at http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit
https://groups.google.com/groups/opt_out
<https://groups.google.com/groups/opt_out>.
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
