Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 6:51 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 7:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 7:25 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 7:47 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
Let's discuss on some code example. You should add your query that fetches the data.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 9:08 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 9:15 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 9:16 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Tue Feb 28, 2017 9:48 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Sat Mar 04, 2017 6:15 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Sat Mar 04, 2017 7:15 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Sat Mar 04, 2017 8:53 am 
Newbie

Joined: Tue Feb 28, 2017 6:47 am
Posts: 6
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)


Top
 Profile  
 
 Post subject: Re: Hibernate Performance issue
PostPosted: Sat Mar 04, 2017 9:40 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1267
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.