Hello,

I am facing performance issue when i am trying to get data from database using Hibernate. When i am running query on database tool it takes hardly 2-3 seconds. But When i am trying it from my java code the line query.getResultList() taking more than 1 minute to execute. Can you please suggest solution on this.

Thanks,
Roshan

Most likely, that in the database SQL browser you are only seeing a subset of the whole ResultSet while, in Hibernate, the whole ResultSet is being traversed and fetched into your application.

Just as we explained in this Performance Tuning Guide, there is absolutely no reason to ever fetch tons of data in your data access layer. In fact, that's an Anti-Pattern.

Therefore, you have multiple options:

1. Either you're doing the processing in the DB so that you don't have to pay the price of extracting large amounts of data and serialize them over the network
2. You can still do the processing in the application, but you need to operate on small batches at a time.
3. If you're sending the ResultSet to the UI, then pagination is mandatory since you can't even display tons of data to the user. Not to mention mobile clients.

I understand.

And I am not fetching all the records from the database table.

As I have more than 50K records in table but I am fetching on 100 out of that and using pagination on client side. To fetch 100 records only from table it is taking long time. Please suggest solution for this.

Thanks

Let's discuss on some code example. You should add your query that fetches the data.

Because of some reasons I cannot show it code wise. But I can say like I am simply creating query on some of DB View as below

TypedQuery<Object> query = entityManager.createQuery(c);
List<Object> queryResultList = query.getResultList();

and the query has pagination, some where (In Condition with other) conditions and sorting.

It will be helpful if i can get some help.

Thanks

Unfortunately, without seeing the actual code, it's hard to point out the issue.

You might want to take a look on Keyset pagination in case the scanned DB set is fairly large. Check out Blaze Persistence for more details.

One more thing I am adding here that. This is not consistent, sometimes I am getting response in 2~3 seconds but sometime it is taking more than 40~50 seconds.

That can have multiple causes:

1. Sometimes, you might get more traffic, or maybe you have a batch processor that does a lot of processing being triggered when you experience a performance degradation
2. The query plan might change based on the bind parameters, yet the DB (e.g. Oracle, SQL Server) has cached a bad plan

So, you should do profiling and gather statistics all the time to make sure what happens. High-Performance MySQL has a very good chapter on Profiling and collecting metrics. All in all, you'll have to work it through with your DBA as well.

Somehow after debugging i got to know that when i am passing date in filter for the View it is taking time to return the records. But When removed date from filter I am getting quick response.

Can you please suggest me on this.

The answer lies in the execution plan. If you compare the execution plan for the query with and without that parameter, you are going to find why it works slower in the first case.

Again findings:

The delay of time I am getting is not for query execution. Some the the connection pool is getting disconnected.

Please find logs:

2017-03-04 04:21:25,063 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (default task-30) EXGLDS: getConnection(null, WrappedConnectionRequestInfo@18267b2a[userName=eclipse]) [0/250]
2017-03-04 04:21:28,935 DEBUG [org.jboss.jca.core.connectionmanager.pool.idle.IdleRemover] (IdleRemover) Notifying pools, interval: 900000
2017-03-04 04:21:28,935 DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (IdleRemover) EXGLDS: removeIdleConnections(1488628288935) [0/250]
2017-03-04 04:21:52,732 DEBUG [org.jboss.activemq.artemis.wildfly.integration.recovery] (Periodic Recovery)
=======================================================================================
2017-03-04 04:21:52,732 DEBUG [org.jboss.activemq.artemis.wildfly.integration.recovery] (Periodic Recovery) Returning the following list on getXAREsources:
2017-03-04 04:21:52,732 DEBUG [org.jboss.activemq.artemis.wildfly.integration.recovery] (Periodic Recovery) server-id=93a469c3-ea35-11e5-b38f-0f3e9b721027, value=org.jboss.activemq.artemis.wildfly.integration.WildFlyActiveMQXAResourceWrapper@6443a5d6
2017-03-04 04:21:52,732 DEBUG [org.jboss.activemq.artemis.wildfly.integration.recovery] (Periodic Recovery)

You do realize that you are looking at different threads, right?

- default task-30 - this one is from your app
- IdleRemover - this one is run by the CP to remove idle connections
- Periodic Recovery - this one is for managing the XA resource of the underlying MQ

If you were listening to what I told you, you would fix this issue in no time.