Hello, I'm using h2 database in my new project and i'm facing a
problem on the join request.
I have 2 tables: table entity and table entitystate.
here is the shema of the tables :
CREATE CACHED TABLE PUBLIC.ENTITY(
ID INTEGER DEFAULT (NEXT VALUE FOR
PUBLIC.SYSTEM_SEQUENCE_30944D77_28A2_47CD_949B_0D8AD2285F41) NOT NULL
NULL_TO_DEFAULT SEQUENCE
PUBLIC.SYSTEM_SEQUENCE_30944D77_28A2_47CD_949B_0D8AD2285F41
SELECTIVITY 100,
GID BIGINT NOT NULL SELECTIVITY 100,
ELEMENTTYPE INTEGER SELECTIVITY 1,
ENTITYNAME VARCHAR(255) NOT NULL SELECTIVITY 100
);
CREATE CACHED TABLE PUBLIC.ENTITYSTATE(
ID INTEGER NOT NULL SELECTIVITY 100,
DELETED BOOLEAN NOT NULL SELECTIVITY 1,
TIMESTAMP BIGINT NOT NULL SELECTIVITY 81
)
Tables are join by the ID column from a one to one relation.
There is about 1m rows in my tables.
i need to do this request :
SELECT count(*) FROM ENTITY as e INNER JOIN ENTITYSTATE as es ON e.ID
= es.ID AND e.ELEMENTTYPE = 14
it takes about 16s whereas the following request takes only 4,5s :
SELECT count(*) FROM ENTITY WHERE ELEMENTTYPE = 14
It mean that i'm losing 11,5s in the join...
What is wrong with my request ? why is this so slow ?
Thank you for your help
Quentin Flahaut
--
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.
