Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate: Restrict fields in SELECT statement

 
Justin Rundle
Ranch Hand
Posts: 123
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi newbie to hibernate and just wanted to get your thoughts on the following:

I have a POJO called Client that contains all information about a client ie.: firstname, surname, dob... etc.

I implemented a search functionality that searches for clients by Surname but when I view the sql generated by hibernate i noticed that hibernate fetches all fields defined in the Client POJO. ie.: SELECT FirstName, Surname, DOB, Occupation, Income FROM Client

However for performance i would prefer to restrict the output to SELECT FirstName, Surname FROM Client.

What would be the best way to achieve this in hibernate, i mean theres no point returning/fetching all attributes from the Client table when im only using the firstname and surname to display the found clients.

Thanks
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
you can use an HQL query to explicitly get the columns instead of all columns.
However, i would think there is no(or very less) performance gain when you select a few columns instead of all columns because that DB row will still need to be accessed.

However, you may try both the approaches and see whether selecting only a few columns by HQL realy does help in performance instead of selecting all columns.




 
Justin Rundle
Ranch Hand
Posts: 123
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rahul,

Thanks for the prompt response, however with regards to fewer columns my Client POJO has over 15 attribute of which come are joins to other tables, additionally why do you feel have fewer columns will not make a difference, I mean fetching 100s and 1000s of clients from a database and retrieving all columns vs. only retrieving 2 or 3 colums?!!
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have a choice between selecting 2-3 columns versus 100 columns then i would think selecting 2-3 columns would make sense.
Also if you have relationships with other tables(with lazy loading turned off), then it makes sense to select only the few columns.
My point is that there would not be much difference if you were to select 2-3 columns instead of 10-15 columns...because the main cost in terms of a database comes in the number of times you access a database, no of rows you select, and not generally on the number of columns.
Further, I think i read somewhere that Hibernate will have some sort of optimized strategy for getting all the columns in the table..not sure about that..

Anyways, normally whether there is a performance gain or not depends on you scenario(normally there isn't much if you select few columns), but its always good to find out how much time your queries take in both the scenarios.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic