Currently we have 45 different tables in DB with 1000 records in each table. Number of parallel requests we get is 15 messages / seconds and each record in table may not exceed 100 KB.
Our situation is that we select the list of tables one after the other with a common id that we have across all the tables ex: emp.id. The select query is based on the emp.id.
When I am firing the select query for all these tables with a specific emp.id, below are the results
- @ 10 message/second - it is normal and responds back within 500 milliseconds
- @ 12 message/second- Time is growing continuously and exceeds 60 seconds
What could be going wrong? Also no blocking is identified in DB.
Below is my code
@PersistenceContext
EntityManager entityManager;// = getEntityManager();
private EntityManagerFactory entityManagerFactoryNew;//= Persistence.createEntityManagerFactory("srk-orm");
protected EntityManager getEntityManager() {
entityManager = entityManagerFactoryNew.createEntityManager();
return entityManager;
}
protected void closeEntityManager() {
try {
if(null != entityManager) {
entityManager.close();
}
} catch(Throwable th){}
}
////
private static final Logger LOGGER = LoggerFactory.getLogger(ListActiveEmployeesDAO.class);
public void setEntityManager(EntityManager entityManager) {
this.entityManager = entityManager;
}
public List<Employee> getEmployeeDetails(java.lang.Integer EmployeeId) {
List<Employee> list=new ArrayList<Employee>();
try {
EntityGraph<Employee> EmployeeGraph = entityManager.createEntityGraph(Employee.class);
list = entityManager.createQuery("SELECT j FROM Employee j "
+ "WHERE j.EmployeeId=:EmployeeId and j.statusCode=:statusCode " , Employee.class)
.setParameter("statusCode", 'A')
.setParameter("EmployeeId", EmployeeId)
.setHint("javax.persistence.fetchgraph", EmployeeGraph)
.setLockMode(LockModeType.NONE)
.setHint(QueryHints.HINT_READONLY,true)
.getResultList();
LOGGER.debug("Employee list size for Single Employee: {}",list.size());
} catch(Throwable th) {
} finally {
closeEntityManager();
}
return list;
}
public List<EmployeeRoute> getEmployeeRoute(java.lang.Integer EmployeeId){
List<EmployeeRoute> list=new ArrayList<EmployeeRoute>();
try
{
//We will create EntityGraph dynamically
EntityGraph<EmployeeRoute> EmployeeRouteGraph = entityManager.createEntityGraph(EmployeeRoute.class);
list = entityManager.createQuery("SELECT jr FROM EmployeeRoute jr "
+ "WHERE jr.Employee.EmployeeId=:EmployeeId AND jr.statusCode=:statusCode " , EmployeeRoute.class)
.setParameter("statusCode", 'A')
.setParameter("EmployeeId", EmployeeId)
.setHint("javax.persistence.fetchgraph", EmployeeRouteGraph)
.setHint(QueryHints.HINT_READONLY,true)
.setLockMode(LockModeType.NONE)
.getResultList();
LOGGER.debug("EmployeeRoute list size for Single Employee: {}",list.size());
} catch(Throwable th) {
} finally {
closeEntityManager();
}
return list;
}
Below is my applicationContext.xml for JPA.
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:task="http://www.springframework.org/schema/task"
xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd">
<context:component-scan base-package="com.uil.employee.orm.*" />
<context:component-scan base-package="com.uil.employee.orm.dao.*" />
<context:component-scan base-package="com.uil.employee.orm.util.*" />
<context:component-scan base-package="com.uil.employee.orm.db.*" />
<!-- <context:property-placeholder location="srk-orm.properties"/> -->
<context:annotation-config/>
<cache:annotation-driven />
<task:annotation-driven executor="ormTaskExecutor" />
<task:executor id="ormTaskExecutor" pool-size="${orm.task.executor.initialsize}-${orm.task.executor.maxTotal}" queue-capacity="${orm.task.executor.queueCapacity}" rejection-policy="CALLER_RUNS"/>
<bean
class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
<!-- ************ JPA configuration *********** -->
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml" />
<property name="persistenceUnitName" value="srk-esb-orm" />
<property name="dataSource" ref="srkDataSource" />
<property name="packagesToScan" value="com.uil.employee.orm.*" />
<!-- JpaVendorAdapter implementation for Hibernate EntityManager.
Exposes Hibernate's persistence provider and EntityManager extension interface -->
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="false" />
<property name="databasePlatform" value="org.hibernate.dialect.SQLServerDialect" />
</bean>
</property>
</bean>
<bean id="entityManager" class="org.springframework.orm.jpa.support.SharedEntityManagerBean">
<property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>
</beans>When I run profiler's I see below
ormTaskExecutor-251 Blocked CPU usage on sample: 0ms
java.lang.Class.forName0(String, boolean, ClassLoader, Class) Class.java (native)
java.lang.Class.forName(String, boolean, ClassLoader) Class.java:348
org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(String) ClassLoaderServiceImpl.java:226
org.hibernate.internal.util.ReflectHelper.getConstantValue(String, ClassLoaderService) ReflectHelper.java:235
org.hibernate.hql.internal.ast.QueryTranslatorImpl$JavaConstantConverter.handleDotStructure(AST) QueryTranslatorImpl.java:615
org.hibernate.hql.internal.ast.QueryTranslatorImpl$JavaConstantConverter.visit(AST) QueryTranslatorImpl.java:610
org.hibernate.hql.internal.ast.util.NodeTraverser.visitDepthFirst(AST) NodeTraverser.java:60
org.hibernate.hql.internal.ast.util.NodeTraverser.traverseDepthFirst(AST) NodeTraverser.java:49
org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(boolean) QueryTranslatorImpl.java:284
org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(Map, boolean, String) QueryTranslatorImpl.java:187
org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(Map, boolean) QueryTranslatorImpl.java:142
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(String, String, boolean, Map, SessionFactoryImplementor, EntityGraphQueryHint) HQLQueryPlan.java:115
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(String, boolean, Map, SessionFactoryImplementor, EntityGraphQueryHint) HQLQueryPlan.java:81
org.hibernate.internal.AbstractQueryImpl.applyEntityGraphQueryHint(EntityGraphQueryHint) AbstractQueryImpl.java:1056
org.hibernate.jpa.internal.QueryImpl.list() QueryImpl.java:604
org.hibernate.jpa.internal.QueryImpl.getResultList() QueryImpl.java:483
sun.reflect.GeneratedMethodAccessor565.invoke(Object, Object[])
sun.reflect.DelegatingMethodAccessorImpl.invoke(Object, Object[]) DelegatingMethodAccessorImpl.java:43
java.lang.reflect.Method.invoke(Object, Object[]) Method.java:498
org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(Object, Method, Object[]) SharedEntityManagerCreator.java:362
com.sun.proxy.$Proxy172.getResultList()