Named Query Or Native Query or Query Which one is better in performance point of view?

Question

Which one is better among following(EJB 3 JPA)

//Query

a). getEntityManager().createQuery("select o from User o");

//Named Query where findAllUser is defined at Entity level

b). getEntityManager().createNamedQuery("User.findAllUser");**

//Native Query

c). getEntityManager().createNativeQuery("SELECT * FROM TBLMUSER ");

Please explain me which approach is better in which case?.

Typeset

Answer 1

1. createQuery()

   It should be used for dynamic query creation.

   //Example dynamic query
   StringBuilder builder = new StringBuilder("select e from Employee e");
   if (empName != null) {
       builder.append(" where e.name = ?");
   }
   getEntityManager().createQuery(builder.toString());
   

2. createNamedQuery()

   It is like a constant variable which can be reused by name. You should use it in common database calls, such as "find all users", "find by id", etc.

3. createNativeQuery()

   This creates a query that depends completely on the underlying database's SQL scripting language support. It is useful when a complex query is required and the JPQL syntax does not support it.

   However, it can impact your application and require more work, if the underlying database is changed from one to another. An example case would be, if your development environment is in MySQL, and your production environment is using Oracle. Plus, the returned result binding can be complex if there is more than a single result.

Scraper

Answer 2

Named queries are the same as queries. They are named only to let them be reusable + they can be declared in various places eg. in class mappings, conf files etc. (so you can change query without changing actual code)

Native queries are just native queries. You have to do all the things that JPA Queries do for you eg. Binding and quoting values etc. + they use DBMP independent syntax (JPQL in your case) so changing database system (lets say from MySQL to Postgresql or H2) will require less work as it does not (not always) require to rewrite native queries.

Karakoram

Answer 3

For me, the better is obviously the first two one, that is JPQL Queries - the second meaning the entity manager will compile the queries (and validate them) while loading the persistence unit, while the first would only yield errors at execution time.

You can also get support in some IDE, and it support the object notation (eg: select b from EntityA a left join a.entityB b) and some other oddities introduced by the object-relational mapping (like collections, index, etc).

On the other hand, use Native queries in last resort in corner case of JPQL (like window function, such as select id, partition by (group_id) from table)

Alver

Answer 4

Native SQL is not necessarily faster than Hibernate/JPA Query. Hibernate/JPA Query finally also is translated into SQL. In some cases it can happen Hibernate/JPA 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/JPA Query 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 Hibernate/JPA Query.

I am not with performance, 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.

Laurin

You don't really lose portability by using native queries. You lose portability by using native queries and including DB specific stuff in your query. If your query is just plain standard SQL (using the same syntax that every other SQL compliant database uses) then how does that cost you in portability? – Hodman

if hibernate/jpa has to translate JPQL to native queries, isnt there a delay at some layer where this translation happens? So by extension, wont native queries be faster(even if by a small amount) compared to hibernate/jpa which might need an extra step of translation/compilation/transpilation to sql? – Oared

Answer 5

Simple Answer: 1) createQuery() - When you want your queries to be executed at runtime.

2) createNamedQuery() - When you want to send common database calls like findBy<attribute>, findAll,..

3)createNativeQuery() - Used when you want your queries to be database vendor-specific. This brings a challenge of portability.

Beene

Answer 6

Named Query:

All the required queries are written in one place related to that entity and they are differentiated by name and we can use them based on the name, no need to write entiry query each time just use the name of the query

For example:

@NamedQuery(name="User_detailsbyId",Query="from UserDetails where UserId=:UserId)

Pinsk