Do you have indexes on these tables? On Wed, Nov 9, 2011 at 10:58, qflahaut <[email protected]> wrote: > 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. > >
-- 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.
