Hi, Do you reuse or recreate criteria query? should be created per "tx".
Romain Manni-Bucau @rmannibucau <https://twitter.com/rmannibucau> | Blog <https://rmannibucau.metawerx.net/> | Old Blog <http://rmannibucau.wordpress.com> | Github <https://github.com/rmannibucau> | LinkedIn <https://www.linkedin.com/in/rmannibucau> | Book <https://www.packtpub.com/application-development/java-ee-8-high-performance> Le mar. 6 févr. 2024 à 12:42, Abhi S <abhivasta...@gmail.com> a écrit : > Hello Team, > I have a requirement to import bulk entities from excel sheet, in > backend, first I am 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 > > Datasource 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="${db.sentinel.datasource.maxIdle}"/> > <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 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> > > > *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 > predicatesCriteriaBuilder 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 createQueryString 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 I have > 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 i**nitially, it is faster and eventually > becomes slower consuming more memory as well.* > > Any suggestions would be appreciated. > Thanks in advance! > > With Best Regards, > Abhishek Srivastava >
