Suppose in some ORM based project (say Hibernate) I am trying to fetch only few columns rather then the whole Object with may be more than 20 attributes. I am writing HQL like "select attr1, attr2 from Entity ". I know I am not taking advantage of using Hibernate because I will get the result in Object form only and then I have to do explicit casting for it. But whether it will be a right approach to achieve optimization from database prospective or because I am performing some explicit casting the cost will still be same?

While this might be done in optimization cases, keep in mind that the more likely case is that they query can't be written in object-oriented form (or, not easily). This may be the case if you have something like 14 different tables... all used to calculate 2 output values, with some fairly complicated joins. Yes, I had to write this (and fight the in memory db used for testing, too).
–
Clockwork-MuseSep 6 '13 at 14:28

4 Answers
4

If you're not seeing performance problems when retrieving all entity attributes, don't spend time on micro-optimization.

Unless the attributes are very large, you probably won't see any performance difference in selecting only the columns you really need for a given operation as opposed to all columns.

If you're really concerned about it, believe that it will cause performance problems, and want to get ahead of those problems, run some tests (with many iterations to gain an average times) to compare the cost of retrieving all attributes versus just the ones you need. It will take less time to write the tests than you think, and it will be worth it to get the data to back up your decision.

If you get the data to prove the performance problems you're assuming, and the performance difference is big enough that it will make a difference in your specific application, then it makes sense to start researching how to improve performance.

Hibernate and other ORM tools are fantastic and can get you 80% of the way to a complete application. In your example if attr1 and attr2 are primitive values read from a column I wouldn't worry about what Hibernate is doing, you'll likely incur some overhead working without Hibernate when iterating over the ResultSet yourself. If attr1 and attr2 are the result of a join then you can make the resultant collection lazy loaded which means that the variables are populated with proxy objects until the data is requested at which point (given the transaction is still open) they go back to the database to retrieve the values.

However. Hibernate builds some pretty ugly (suboptimal) queries and quite often uses a lot of queries. If you turn logging up to DEBUG for Hibernate then you may see what you expected to be a single query being executed as multiple selects. It is at this point that your performance might well bottleneck (depending on whether you saw it coming or not) and you would be better off using JdbcTemplate with all the joins modeled as is most optimal for your datasource.

If you find performance problems because Hibernate is running too many queries or retrieving data sub-optimally, you can customize almost all of how it fetches data instead of dropping it altogether.
–
Mike PartridgeSep 6 '13 at 13:50

2

I almost completely agree, but at the point where your tool (in this case Hibernate) becomes the productivity bottleneck and you are spending more time tuning the tool than developing your software then I think it is totally acceptable to bypass it.
–
JohnMark13Sep 6 '13 at 13:58

Hibernate is a little bit more than just an automatic ORM that is supposed to map full Java objects to full database tables. It provides mapping on all operations, not only queries; inserts and deletes work straightforward, even if you do not want to insert the a full object into the database. Moreover, Hibernate comes with level 2 caching (Ehcache) which significantly improves performance.

Regarding selecting only the objects that you need; Hibernate also comes with improvements. For example, me an OO developer am more familiar with the object model rather than a database developer who is familiar with SQL and relations; Hibernate comes in my help by letting me use a more object-friendly query language (HQL, JPQL). Moreover, even if it does not know to map certain tables to another class, it still converts it to an array (or list of arrays, if multiple entries are returned in one query); you still don't have to get the ResultSet and iterate over it.

Another alternative is using lazy fetching, and the call to the database will be made only when you actually call the method. If you do not need certain fields, set them as lazy, and if they are not needed, are not fetched.

As last words I must say that automatic ORM tools like Hibernate are good and greatly improve productivity, but they are not a silver bullet. They are not suitable in all scenarios; if you want special database features or are not dealing with a complex enough domain, you are probably better with good old JDBC or the better Spring alternative, JdbcTemplate.

If you want to select only some columns you may do something likeSelect new x.y.z.Object(c.column1, c.column2) FROM Object c

Of course you should declare a constructor with only those two fields.
If you analyse the logs you will see that only the columns that you require are fetched from de DB. As far as i can say this is a perfectly valid use case along with the usage of explicit fetching (optimization such as this one are sometimes required).