Performance of EJB Queries

[natanasov]

I have a problem with the performance execution of QL statements that retrieve a lot of data. Let?s take the example of company/employee application. There is a Use Case that retrieves all employees per company and the number of the employee is 100 000 records.
The execution time of the following statements takes 102 seconds.

Query query = manager.createQuery("SELECT employee from Employee employee WHERE employee.companyID = :objectCompany");
query.getResultList();

If I use database connection and SQL statement to retrieve the records and create POJO objects for every employee record then the whole time is 12 seconds.
I suppose that the first scenario takes more time because Hibernate puts the record in a cache and keeps them until the transaction end. But I don?t need to have them in the cache because the Use Case sent the object outside of the app server and it doesn?t use them in a transaction.
The use case just selects the records and returns them to the client. In this case the employee objects are DTO.
My question is how can I accelerate the query's execution?

[niesar]

Query query = manager.createQuery("SELECT employee from Employee employee WHERE employee.companyID = :objectCompany");
query.getResultList();

Let me guess: objectCompany is an entity bean, too.

Well, in this case the speediest solution seems to be to avoid the container managed relation. As far as I can see from my own beans and from other users' questions EJB 3.0 or Hibernate insists to a create a query involving a join of the "company-table." No way that I'm aware of to avoid that.

But did you try something like this?

int companyId = objectCompany.getId(); // get company's id
Query query = manager.createQuery("SELECT employee from Employee employee WHERE employee.companyID = :companyId");
query.getResultList();

[natanasov]

I didn?t explain well. The objectCompany is not entity bean. It?s a named parameter that takes the company Id as in your example. The execution time of the example is 102 seconds.

int companyId = objectCompany.getId(); // get company's id
Query query = manager.createQuery("SELECT employee from Employee employee WHERE employee.companyID = :companyId");
query.getResultList();

If I change the routine and use JDBC connection to get the data and a factory method to create the instances from the result set then execution time is 12 seconds.
Please see the code below.

...
@Resource(name="EmployeeDS", mappedName="java:/EmployeeDS")
 private DataSource ds;
...
ArrayList<Employee> result = new ArrayList<Employee>();
try{
 connection = ds.getConnection();
 int companyId = objectCompany.getId();
 PreparedStatement pstmt = connection.prepareStatement(
"SELECT employeeID, first_name, surname, ... FROM Employee WHERE companyID = ?");
 pstmt.setInteger(1, companyId);
 pstmt.execute();
 ResultSet allEmployee = pstmt.getResultSet();
 while(allEmployee.next())
 result.add(Employee.create(allEmployee))
...
}

[epbernard]

set show_sql=true and see what are the executed queries. My guess is that you have eager linked entities

[natanasov]

I did it as you suggested and the only SQL query ?SELECT employee0_.employeeID as ID1_52_, employee0_.first_name as first_name1_52_, employee0_.surname as surname2_52_, ... FROM Employee employee0_ WHERE employee0_.companyID = ?? was executed.
I don?t have linked entity in the scheme.

[epbernard]

on your JDBC driver did you use a prepared statement?
If yes, then there is no reason for this slowness, use a profiler to find the original issue.

[natanasov]

The problems doesn't concern using JDBC driver. It concerns using query language statement

Query query = manager.createQuery("SELECT employee from Employee employee WHERE employee.companyID = :objectCompany");
query.getResultList();

If I need to use profile, which profiler do I need to use and how I have to set up because it means to profile the JBoss query language execution.

[gavin.king]

If you are retrieving hundreds of thousands of objects, it may be that you need to follow the instructions for "batch processing" in Hibernate, and drop back to Hibernate APIs.

[natanasov]

One of the problems with this bad performance was insufficient RAM on the test machine. I upgraded the RAM and the execution time of query is 65 seconds.
I use following hints to increase the performance:

query.setHint("org.hibernate.fetchSize", new Integer(5000));
query.setHint("org.hibernate.cacheable", new Boolean(false));

After this hint the execution time is 55 seconds. The approach with JDBC connection and factory method is 12 seconds.
So the resume is:
- 12-14 seconds are needed to retrieve from the database the data and constructs the objects in JDBC
- Probably the same time is spent by JBoss because transaction isolation level is the same to retrieve data from database
- 40 additional seconds are needed by JBoss ..... even if we ask him to not use cache (org.hibernate.cacheable is set to false).

What are these 40s used for ?

Moreover we are using JBoss not hibernate and the application has to be EJB application so we cannot use the advice to use Hibernate to solve performance issues.

[natanasov]

I?ve found the missing time. The default log of Hibernate produces a huge log file. If the log level is increased to INFO then the performance is slightly the same as the approach with JDBC connection.