Hi, Do you have any indexes? Did you read http://h2database.com/html/performance.html ?
Regards, Thomas On Sun, Apr 26, 2015 at 6:52 PM, sim <[email protected]> wrote: > Hi, > > Let's assume I have two tables > > table clients > ============= > cl_id bigint primary key, > cl_name varchar > > table requests > ============== > req_id identity primary key, > req_type integer, > req_date timestamp, > cl_id bigint foreign key > > And I want to run a request like > > select c.*,r.* > from requests r > join clients c on r.cl_id = c.cl_id > where r.req_id between 1L and 2L > > It takes ~40s to get 2 rows > > explain analyze: > > SELECT > C.CL_ID, > C.CL_NAME, > R.REQ_ID, > R.REQ_TYPE, > R.REQ_DATE, > R.CL_ID > FROM PUBLIC.CLIENTS C > /* PUBLIC.CLIENTS.tableScan */ > /* scanCount: 11 */ > INNER JOIN PUBLIC.REQUESTS R > /* PUBLIC.REQUESTS_CLID_IDX: CL_ID = C.CL_ID */ > ON 1=1 > /* scanCount: 7528481 */ > WHERE (R.CL_ID = C.CL_ID) > AND ((R.REQ_ID >= 1) > AND (R.REQ_ID <= 2)) > /* > reads: 3439464 > */ > > > Not helps much a request like > select c.*,r.* > from (select * from requests where req_id between 1L and 2L) r > join clients c on r.cl_id = c.cl_id > > explain analyze: > > SELECT > C.CL_ID, > C.CL_NAME, > R.REQ_ID, > R.REQ_TYPE, > R.REQ_DATE, > R.CL_ID > FROM PUBLIC.CLIENTS C > /* PUBLIC.CLIENTS.tableScan */ > /* scanCount: 11 */ > INNER JOIN ( > SELECT > REQUESTS.REQ_ID, > REQUESTS.REQ_TYPE, > REQUESTS.REQ_DATE, > REQUESTS.CL_ID > FROM PUBLIC.REQUESTS > /* PUBLIC.PRIMARY_KEY_F: REQ_ID >= 1 > AND REQ_ID <= 2 > */ > WHERE (REQ_ID >= 1) > AND (REQ_ID <= 2) > ) R > /* SELECT > REQUESTS.REQ_ID, > REQUESTS.REQ_TYPE, > REQUESTS.REQ_DATE, > REQUESTS.CL_ID > FROM PUBLIC.REQUESTS > /++ PUBLIC.REQUESTS_CLID_IDX: CL_ID IS ?1 ++/ > /++ scanCount: 1 ++/ > WHERE (REQUESTS.CL_ID IS ?1) > AND ((REQ_ID >= 1) > AND (REQ_ID <= 2)): CL_ID = C.CL_ID > */ > ON 1=1 > /* scanCount: 12 */ > WHERE R.CL_ID = C.CL_ID > /* > reads: 3439464 > */ > > Is it ok such a simple request takes so much time? > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
