9 Replies Latest reply on Nov 24, 2005 4:41 AM by Nikolay Atanasov

    Performance of EJB Queries

    Nikolay Atanasov Newbie

      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?


        • 1. Re: Performance of EJB Queries
          Ulf Niesar Newbie

           

          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
            Nikolay Atanasov Newbie

            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
              Emmanuel Bernard Master

              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
                Nikolay Atanasov Newbie

                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
                  Emmanuel Bernard Master

                  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
                    Nikolay Atanasov Newbie

                    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 Master

                      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
                        Nikolay Atanasov Newbie

                        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
                          Nikolay Atanasov Newbie

                          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.