Optimizing Hibernate session.createQuery().list();

Question

We have a Users table (MySQL) with 120,000 rows

List<User> users = session.createQuery("from User").list();

This hibernate query takes about 6 to 9 seconds to execute. How can we optimize this? Is MySQL the bottleneck? Or is .list() usually this slow?

@Masud, the results will not be displayed. we need the result set for some processing. — Saqib Ali
– Saqib Ali, Commented Oct 5, 2013 at 17:38

Roman C · Accepted Answer · 2013-10-05 17:27:23Z

1

Ofcourse it's slow because the query perform the full table scan. You should join other objects associated with it, including where clause of the query, the query could be changed to return the limited number of records or use criteria API and projection.

answered Oct 5, 2013 at 17:27

Roman C

1

Sign up to request clarification or add additional context in comments.

4 Comments

Saqib Ali Over a year ago

unfortunately we need to the entire table. so we can't use the criteria API or projection or WHERE clause

Roman C Over a year ago

then you have no choice

Saqib Ali Over a year ago

Is mySQL the bottleneck? Or the CPU which is running tomcat? or the memory?

Saqib Ali Over a year ago

then what is causing it to be so slow?

Masudul · Accepted Answer · 2013-10-05 17:31:40Z

1

Use pagination on your query. You should not call all rows at a time. You can set first position of result and maximum result. For example, if you want to read first 100 result than change your query like:

Query q=session.createQuery("from User");
q.setFirstResult(fistRes);// this will variable
q.setMaxResults(maxRes);// this will also be variable as parameter.

edited Oct 5, 2013 at 17:31

answered Oct 5, 2013 at 17:25

Masudul

22k5 gold badges45 silver badges60 bronze badges

1 Comment

Masudul Over a year ago

@SaqibAli This will partition your result into different page.

Collectives™ on Stack Overflow

Optimizing Hibernate session.createQuery().list();

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related