Hi Thomas, There are only 5000 records in the DB. But do note that the IN () statement, contains a very large number of IDs..potentially thousands.
Here's the profiler output per Noel's request: Profiler: top 3 stack trace(s) of of 8868 ms of 1299 thread dumps: 5183/6517 (79%): at org.eclipse.jetty.io.nio.SelectorManager$SelectSet.doSelect(SelectorManager.java:569) at org.eclipse.jetty.io.nio.SelectorManager$1.run(SelectorManager.java:290) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) 525/6517 (8%): at org.h2.value.ValueStringIgnoreCase.hashCode(ValueStringIgnoreCase.java:45) at org.h2.value.Value.cache(Value.java:361) at org.h2.value.ValueStringIgnoreCase.get(ValueStringIgnoreCase.java:70) at org.h2.value.Value.convertTo(Value.java:872) at org.h2.expression.ConditionIn.getValue(ConditionIn.java:56) at org.h2.expression.Expression.getBooleanValue(Expression.java:178) at org.h2.command.dml.Select.queryFlat(Select.java:534) at org.h2.command.dml.Select.queryWithoutCache(Select.java:644) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:108) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570) at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691) at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469) at com.ca.apm.acc.configserver.core.repository.filters.JpaLazyEntityCollection.getEntities(JpaLazyEntityCollection.java:95) at com.ca.apm.acc.configserver.web.rest.AbstractResourceFilterController.filterAsJson(AbstractResourceFilterController.java:340) at com.ca.apm.acc.configserver.web.rest.AbstractResourceFilterController$$FastClassBySpringCGLIB$$85e9021b.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649) at com.ca.apm.acc.configserver.web.rest.ControllerFilterController$$EnhancerBySpringCGLIB$$8dfb84ac.filterAsJson(<generated>) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777) 241/6517 (3%): at org.h2.value.ValueStringIgnoreCase.equals(ValueStringIgnoreCase.java:37) at org.h2.value.Value.cache(Value.java:373) at org.h2.value.ValueStringIgnoreCase.get(ValueStringIgnoreCase.java:70) at org.h2.value.Value.convertTo(Value.java:872) at org.h2.expression.ConditionIn.getValue(ConditionIn.java:56) at org.h2.expression.Expression.getBooleanValue(Expression.java:178) at org.h2.command.dml.Select.queryFlat(Select.java:534) at org.h2.command.dml.Select.queryWithoutCache(Select.java:644) at org.h2.command.dml.Query.query(Query.java:322) at org.h2.command.dml.Query.query(Query.java:290) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:90) at org.h2.command.Command.executeQuery(Command.java:197) at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:108) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570) at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691) at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469) at com.ca.apm.acc.configserver.core.repository.filters.JpaLazyEntityCollection.getEntities(JpaLazyEntityCollection.java:95) at com.ca.apm.acc.configserver.web.rest.AbstractResourceFilterController.filterAsJson(AbstractResourceFilterController.java:340) at com.ca.apm.acc.configserver.web.rest.AbstractResourceFilterController$$FastClassBySpringCGLIB$$85e9021b.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649) at com.ca.apm.acc.configserver.web.rest.ControllerFilterController$$EnhancerBySpringCGLIB$$8dfb84ac.filterAsJson(<generated>) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777) summary: 79%: org.eclipse.jetty.io.nio 18%: org.h2.value . Thanks for the help. On Tuesday, July 7, 2015 at 6:42:02 AM UTC+1, Thomas Mueller wrote: > > Hi, > > If you have many rows in the database, most likely the problem is that the > values are randomly distributed. That way, caching is very inefficient. > > Regards, > Thomas > > On Friday, July 3, 2015, Noel Grandin <[email protected] <javascript:>> > wrote: > >> Hmmm, that all looks fairly normal. >> >> Is it possible you could run the performance profiler >> (see here: >> http://h2database.com/html/performance.html#built_in_profiler) >> and post the result? >> >> Either that or a self-contained test-case. >> >> -- >> 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.
