Which is faster :Criteria query or native query?

Question

I have a table Admin which might contain 1000 or even more records. Now, I am confused on which query to use so that the performance while fetching the entire data is not compromised.

I've tried fetching the data using native query and criteria query but only for small amount of data. I also studied the relevant questions but it didn't satisfy my question completely.

Which SQL query is faster and why?

Named Query, Native Query or Criteria API

Native query is faster than HQL/Criteria query in Hibernate

Which is better performance in hibernate native query or HQL

My entity class somehow looks like:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private String username;

private String emailAddress;

private String password;

private Character status;

Using Criteria Query:

 public List<Admin> fetchAdmin() {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Admin> criteria = builder.createQuery(Admin.class);

    List<Admin> adminList = entityManager.createQuery(criteria).getResultList();

    return adminList;
}

Using Native query:

@Query(value = "SELECT * FROM admin", nativeQuery = true)
List<Admin> fetchAllAdmins();

I expect, selection of a query that would fetch big data.

What prevents you from testing it with a large amount of data and comparing the results? — Robby Cornelissen, Jul 10 '19 at 04:46
If the end query is the same why would there be any measurable difference? The bottleneck is always going to be the query and lack of indexes. — Scary Wombat, Jul 10 '19 at 04:46
Native is faster than criteria.. Sql need to be constructed from criteria — Prasad, Jul 10 '19 at 04:51
Using the CriteriaBuilder is just a tiny overhead, irrelevant w.r.t. the execution time of a query itself. Considering type safety, autocompletion support and even SQL dialect independence there is no compelling reason not to use the CriteriaBuilder (other than the generated meta classes). — Joop Eggen, Jul 10 '19 at 05:07

score 6 · Answer 1 · answered Jul 10 '19 at 04:51

Based on what I have seen working with similar ORM tools, e.g. Hibernate, the raw SQL query would always be faster than the criteria query. But, assuming the same underlying SQL is being executed in both cases, the reason is that the criteria query is doing extra work with the result set. That is, the criteria query has to do the work of marshaling the SQL result into a collection of Java objects. This entails some serialization work, etc. Assuming you needed the result of the raw query as a collection of Java objects, you might not notice a meaningful difference in execution time between the two.

So, assuming the same query is being executed against your SQL database, the only difference in time is what happens on the Java side after the result set has been returned.

Which is faster :Criteria query or native query?

1 Answers1