The suggestion of multi-column index turned out to work quite well. I
seem to have a ~ 10% speed gain for the system as a whole, which is great.
There is still one other query that is quite slow, but I've moved it to
run only occasionally, and reworked my task-selection algorithm to do a
better job, so that the query doesn't run as often.
-- clint
Noel Grandin wrote:
Please do an explain plan on the queries and post the results.
On 2012-07-13 02:43, Clint Hyde wrote:
here's the situation:
h2 v.166
I have a DB with about 20 million total rows across all tables. 16
million are in one table.
the query:
select taskID, taskname, filename from tasks where state=1 and
priority=3;
this is a distributed multi-processing task controller (grid engine)
program. I need to be aware of the overall state of things, so I need
to do this query once in a while, for all values of 'priority'
this query is quite expensive, takes 20-24 seconds right now, with
16M rows. it's going to get worse.
all the fields in the query are indexed. Why is it so slow? (fwiw,
this is on a 3GHz 32GB dual-hex-core Mac Pro--blazing fast hardware)
I sure would like this query to run in 1/10 the time (2 sec, and not
dependent on table size).
------------------
related: similar query:
select taskname, count(*) from tasks where state=1 group by taskname
also about 24 seconds
--------------------
other than this set of queries, H2 is doing good for me, mostly it
runs at about 5% of cpu or less.
-- clint
--
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.
