-
1. Re: Performance of EJB Queries
niesar Nov 3, 2005 11:58 AM (in response to natanasov)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();
-
2. Re: Performance of EJB Queries
natanasov Nov 4, 2005 3:53 AM (in response to 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)) ... }
-
3. Re: Performance of EJB Queries
epbernard Nov 4, 2005 6:29 AM (in response to natanasov)set show_sql=true and see what are the executed queries. My guess is that you have eager linked entities
-
4. Re: Performance of EJB Queries
natanasov Nov 7, 2005 3:26 AM (in response to 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. -
5. Re: Performance of EJB Queries
epbernard Nov 17, 2005 6:33 AM (in response to natanasov)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. -
6. Re: Performance of EJB Queries
natanasov Nov 21, 2005 12:19 PM (in response to 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. -
7. Re: Performance of EJB Queries
gavin.king Nov 21, 2005 1:11 PM (in response to natanasov)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.
-
8. Re: Performance of EJB Queries
natanasov Nov 23, 2005 12:49 PM (in response to 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. -
9. Re: Performance of EJB Queries
natanasov Nov 24, 2005 4:41 AM (in response to 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.