Stumbled upon a perforamance problem in my application where the execution was taking ages. It was fetching around 1000-2000 records from the oracle database. After investigating for couple of days and tried various randon things i wasn't able to figure out much what was going wrong, I debugged to the point where i came to know that it was looping of the result set which was taking time, and then my friend who is also programmer asked why you look frustrated and i told her the issue, she said she will think about it and then after sometime she said, have you tried fetch size. I was like, comon how the fetch size can affect the looping but Bingo! that was a bull's eye. Advantage of having brainy friends.
There is also a great analysis done by Andreas on https://stackoverflow.com/questions/17744090/iterating-a-resultset-using-the-jdbc-for-oracle-takes-a-lot-of-time-about-16s/17744738#17744738
So, lets narrow down the options if you stumbled upon such issues.
1. setFetchSize(1000).
2. Check the fetch type for your joins in the entity model. See in the hibernate logs that if it is executing too many queries for single execution then you must be facing n+1 problem in which your fetching making round trips to the database to get the data. Try fetch="join" instead of "select". be careful in using it as you may worsen the situation because you are enabling the eager fetch.
3. There was a very siple query which was taking way more than the expected time and it was random timings as well. The query was something like "Select * from XYZ where columnId=123". How long do you think it should take in fetching from 350000 records. Yes, within a second. but, it was talking from 2-8 seconds. So what i find that the column was missing the indexing. Indexing is mandatory to improve your performance in fetching the result on the basis of certain column data. So i did create the index on that column, It wan't a unique data column so can't create the unique index but only the index. And it works like magic.
There are other random things which i tried but didn't help much in my case:
1. Creating your Entity class constructor with all the member variables of the class.
2. Correcting the doctype in hbm and config files.
There is also a great analysis done by Andreas on https://stackoverflow.com/questions/17744090/iterating-a-resultset-using-the-jdbc-for-oracle-takes-a-lot-of-time-about-16s/17744738#17744738
So, lets narrow down the options if you stumbled upon such issues.
1. setFetchSize(1000).
2. Check the fetch type for your joins in the entity model. See in the hibernate logs that if it is executing too many queries for single execution then you must be facing n+1 problem in which your fetching making round trips to the database to get the data. Try fetch="join" instead of "select". be careful in using it as you may worsen the situation because you are enabling the eager fetch.
3. There was a very siple query which was taking way more than the expected time and it was random timings as well. The query was something like "Select * from XYZ where columnId=123". How long do you think it should take in fetching from 350000 records. Yes, within a second. but, it was talking from 2-8 seconds. So what i find that the column was missing the indexing. Indexing is mandatory to improve your performance in fetching the result on the basis of certain column data. So i did create the index on that column, It wan't a unique data column so can't create the unique index but only the index. And it works like magic.
There are other random things which i tried but didn't help much in my case:
1. Creating your Entity class constructor with all the member variables of the class.
2. Correcting the doctype in hbm and config files.
