Which is better performance in hibernate native query or HQL

Question

In the server side code generally for better performance we should not use "select * from table" rather we should query the necessary column according to the need (Select name, add from employee).This i read in data base performance guidlines article.

Now i have quetion with hibernate, i read that its better to use session.load(id) in hibernate to retreive the record based on the primary key. This will retreive all the column associated to the entity for the given 'id' (a record from table).

Now is it not contradicting the general data base performance guidline. which is better performance with hibernate native sql query or hibernate query language?

Let me know your valuable inputs as im trying to tune my code for better performance.

Answer 1

You're messing up some parts. You can select only certain columns with HQL, too, for example you can use select column from table in HQL.

Native SQL is not necessarily faster than HQL. HQL finally also is translated into SQL (you can see the generated statement when running the application with the show_sql property set to true). In some cases it can happen Hibernate does not generate the most efficient statements, so then native SQL can be faster - but with native SQL your application loses the portability from one database to another, so normally is better to tune the hibernate mapping and the HQL statement to generate more efficient SQL statements. On the other side with native SQL you're missing the Hibernate cache - as a consequence in some cases native SQL can be slower than HQL.

When you use session.load(class, id) and the row is not in the cache yet, the load also generates a select * from classTable, so the speed is same to the HQL from. (But when the object already is in the cache, then probably load is faster.)

I disagree with your performance guidelines: In most cases for the performance it is irrelevant if your load all columns or only the needed columns. In database access the time is lost when searching the row, and not when transferring the data into your application. When you read only the necessary columns, it has the following disadvantages:

• When you need columns which you didn't load yet, you have more trouble to change your application (or you have to load the row again, which means poor performance).
• It gives a bad design to your application (Hibernate prefers one table - one class)
• It does not work well with the Hibernate cache.

(Thought, if there are columns which you never need in your application, or for columns which will be added after your application is finished, then you just don't put them into your class and your mapping, and they never will be loaded, and your application design still is good. Hibernate does not generate select * from table statements, it always generates select col1, col2, ... from table.)

There is one exception: If you load huge amounts of data - thousands of rows - then loading only the necessary columns can be significantly faster.

Answer 2

Entity queries (e.g. JPQL, HQL,Criteria API) are again rendered back to SQL queries, so it’s evident that running a native SQL query is going to be faster than running an entity query.

However, if you set the query plan cache size right, then you could speed up entity queries so that they run as fast as SQL ones.

But if you want to benefit from the dirty checking mechanism and issue UPDATE automatically from the modified entities, then an entity query is much more convenient. As long as the time difference between an entity query and an SQL query is insignificant (which most often is if you use database indexing and the entity query renders a very efficient SQL query), there’s nothing to worry about.