Hi,
I've been experimenting using the latest version of H2 (1.3.159) with
the geodb and hatbox java spatial extensions.
I have a table with approx 5000 blobs. The table has three columns:
* ID INTEGER primary key
* VALUE VARCHAR
* geom BLOB
Running the following query is very slow (takes ~50 seconds):
SELECT "ID","geom" as "geom" FROM "TESTDATA"
WHERE "ID" IN (
SELECT CAST (HATBOX_JOIN_ID AS INT)
FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298,
-113.94051433313055, 44.17730198523503, 64.12298153276885)
)
However if I re-write the query to use a 'JOIN' instead of an 'IN'
statement as shown below the query returns quickly < 1 second.
SELECT a."ID",a."geom" as "geom" FROM "TESTDATA" a
INNER JOIN
(SELECT CAST (HATBOX_JOIN_ID AS INT) as id
FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298,
-113.94051433313055, 44.17730198523503, 64.12298153276885)
) b ON a."ID" = b.id
I thought maybe IN queries were slow, however this is not the case as
both of the following IN queries are quick (<1 second)
SELECT "ID","geom" as "geom" FROM "TESTDATA" WHERE "ID" IN ( SELECT
"ID" from "TESTDATA2");
SELECT "ID","geom" as "geom" FROM "TESTDATA" WHERE "ID" IN ( 1, 2, ....
5000);
Lastly, the subquery [ SELECT CAST (HATBOX_JOIN_ID AS INT) as id FROM
HATBOX_MBR_INTERSECTS_ENV('PUBLIC', 'TESTDATA', -139.17094658129298,
-113.94051433313055, 44.17730198523503, 64.12298153276885) ] runs on its
own in < 1 second.
Is there a way to make this IN statement faster?
Thanks,
Emily
--
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.
