Hi Thomas, Sure, I have indices, you can see that in explain analyze output above, however it takes too much time to run the query when I use "between" condition
select c.*,r.* from requests r join clients c on r.cl_id = c.cl_id where r.req_id between 1L and 2L although select c.*,r.* from requests r join clients c on r.cl_id = c.cl_id where r.req_id = 1L takes 5ms only on database with nearly 8M records. On Monday, April 27, 2015 at 4:39:12 PM UTC+3, Thomas Mueller wrote: > > 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] <javascript:>> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
