Hi We are facing performance issue with backend code, while importing bulk data from excel sheet, first while checking if the record exists by using snippet1 and then inserting the record using snippet2.
*Below code snippet are composed using apache openjpa library version 3.2.2.* *In code snippet1 we are using two approaches to check for record in database.* *In code snippet2 is used for persisting data in Postgres usinh openjpa library.* Code Snippet1 to fetch data from Postgres database : we have used mainly two types of fetch code, as mentioned in points 1 and 2 below marked in red. 1. Criteria Builder is optimized by using implementing the predicates *CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();* *CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(this.persistentClass);* *Root<T> root = criteriaQuery.from(this.persistentClass);* *criteriaQuery.select(root);* *Predicate namePredicate = criteriaBuilder.equal(criteriaBuilder.lower(root.get(propertyName)), propertyValue.toString().toLowerCase());* *criteriaQuery.where(namePredicate);* *List<T> resultList = entityManager.createQuery(criteriaQuery).getResultList(); * 2. FindByProperty method is optimized using TypedQuery and executed it directly using createQuery *String jpql = "SELECT a FROM " persistentClass.getName() " a WHERE LOWER(a.name <http://a.name>) = LOWER(:name)";* *TypedQuery<T> query = entityManager.createQuery(jpql, this.persistentClass);* *query.setParameter(propertyName, propertyValue.toString().toLowerCase());* *List<T> resultList = query.getResultList();*/* Code snippet 2 is generic saveandupdate call we are using to persist entities : *Snippet2 :* * public T saveOrUpdate(T entity) {* * return (T) jpaRepository.save(entity);* * }* Issue with above calls is with large records update, Since there are thousands of records in excel and processing them sequentially, the fetch and update calls takes a longer time than expected and keeps on growing exponentially in time taken, so initially, it is faster and eventually becomes slower in time and consuming more memory as well. Below are the Datasource Configuration used for db connection and connection pooling setting in spring xml configuration : * <bean id="dataSource1" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">* * <property name="maxActive" value="-1"/>* * <property name="maxIdle" value="2"/>* * <property name="maxWait" value="-1"/>* * <property name="connectionPoolDataSource" ref="connectionPoolDataSource1"/>* * <property name="testOnBorrow" value="true"/>* * <property name="testOnReturn" value="true"/>* * </bean>* * <bean id="connectionPoolDataSource1" class="org.postgresql.ds.PGConnectionPoolDataSource">* * <property name="serverName" value="${db.sentinel.server}"/>* * <property name="portNumber" value="${db.sentinel.port}"/>* * <property name="databaseName" value="${db.sentinel.dbName}"/>* * <property name="user" value="${db.sentinel.user}"/>* * <property name="password" value="${db.sentinel.password}"/>* * <property name="socketTimeout" value="${db.sentinel.datasource.socketTimeout}"/>* * </bean>* Added below jpa ConnectionFactoryProperties property in persistence.xml file : * <persistence-unit name="sentinel" transaction-type="RESOURCE_LOCAL">* * <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>* * <properties>* * <property name="openjpa.DynamicEnhancementAgent" value="true"/>* * <property name="openjpa.RuntimeUnenhancedClasses" value="supported"/>* * <property name="openjpa.Log" value="SQL=INFO"/>* * <property name="openjpa.ConnectionFactoryProperties" value="PrettyPrint=true, PrettyPrintLineLength=72, PrintParameters=true, MinSize=5, MaxSize=20, MaxActive=10, MaxIdle=5, MinIdle=2, MaxWait=60000"/>* * <property name="openjpa.jdbc.QuerySQLCache" value="true(EnableStatistics=false)"/>* * <property name="openjpa.jdbc.DBDictionary" value="postgres(StoreCharsAsNumbers=false, BatchLimit=25)"/>* * </properties>* * </persistence-unit>* Any suggestions would be appreciated. Thanks in advance! With Best Regards, Abhishek Srivastava
