Comparison of Query Performance when Working with ORM

Using Object-Relational Mapping (ORM) approach is very popular in developing modern database applications. ORM allows you to handle relational data in a pure object-oriented approach, which is more natural to developers and also the modern programming language they are using (i.e. Java, .NET).

But some developers may complain about the poor performance of using ORM, especially when working on a large database. In fact, ORM provides different utilities which helps users overcome such problem, and most of the performance issues can be solved by some tricks in their implementation. In the following, we will use a simple customer, order and orderline example to demonstrate how we can work efficiently using ORM.

Assume we have the following simple class model.

We create one single customer, who got 3000 orders, and each order attached to 10 orderlines. So there are 30000 records in total. (The attached Create.java helps you to create those sample data into database).

Now let’s try to query the total quantity of items the customer ordered. There are 2 approaches we can use to query such information. We first start with the traditional approach, which find out the customer from database, and walk through the order and orderline records to sum up the quantity.

// query customer from database Customer[] customers = CustomerFactory.listCustomerByQuery(null, null); int qty = 0;// walk through the customer records for (int i = 0; i < customers.length; i++) { Customer customer = customers[i]; if (“C1232122”.equals(customer.getAccountNo())) {// query order records for the customer Order[] orders = customer.orders.toArray();// walk through the order records for (int j = 0; j < orders.length; j++) { Order order = orders[j];// query the orderline records OrderLine[] orderlines = order.orderLines.toArray(); for (int k = 0; k < orderlines.length; k++) {// walk through the orderline records and calcualte the total quantity OrderLine orderline = orderlines[k]; qty += orderline.getQuantity(); } } } break; }

Another approach is using the criteria class. ORM provide criteria classes which allow you to query database in a more efficient approach. By using criteria class, you can simply specify criteria to query the customer and his associated order and orderline, and then sum up the total number of the items he ordered.

// create criteria class for orderline OrderLineCriteria criteria = new OrderLineCriteria();// create criteria class for order from criteria class of orderline OrderCriteria orderCriteria = criteria.createOrderCriteria();// create criteria class for customer from criteria class of order CustomerCriteria customerCriteria = orderCriteria.createCustomerCriteria();// specify criteria for looking up the customer customerCriteria.accountNo.eq(“C1232122”);// calculate total number of quantity criteria.setProjection(Projections.sum(OrderLine.PROP_QUANTITY));// execute query and obtain the result System.out.println(criteria.uniqueResult());

Our testing environment is as follow:

Client side	Hardware Intel Core 2 Duo 3.16GHz, 4GB RAM Operating system Window 7 Professional 64 bit OS Hibernate version 3.3.2 JDK version 1.6.0_17-b04 Eclipse version 3.5.1 SDE version SDE 5.2
Server side	Hardware Intel Core 2 Duo 3.16GHz, 4GB RAM Operating system Linux Fedora Core 5 Database server Oracle 10g

Now here is the comparison of the above 2 approaches:

Total quantity for all orders.

	Query via association	Query via criteria class	Remarks
Execution Time (ms, average of 5 executions)	4733.8	141	Using criteria class is 33 times faster.
Number of query executed	3000	1

Total quantity for all orders in open state.

	Query via association	Query via criteria class	Remarks
Execution Time (ms, average of 5 executions)	2705.2	139.8	Using criteria class is 19 times faster.
Number of query executed	1500	1

In the testing result you can see the using criteria class are much faster then query via association (over 19 times faster). The number of query being executed is much higher in the query via association approach. That is because the system will perform query to locate the customer, and then query about his orders. Once the orders being located, it will then perform another query to get the orderlines. Compare to criteria class approach, the criteria class helps you to query database based on your provide criteria, so you can retrieve related data object in a single query.

How to try the sample

1. Install Eclipse and SDE (Professional Edition or above) for Eclipse. You can download SDE for Eclipse beforehand.
2. Download sample project files and unpack to your local file system.
3. Start Eclipse.
4. Create new Java project in Eclipse.
5. Start SDE for Eclipse by import the project file downloaded at step2.
6. Go to Tools > Database> Database Configuration to specify your database connection settings.
7. Generate code and database.
8. Download the attached Java files to the src\ormsamples folder in your eclipse project.

The sample files are for perform the following query:

File Name	Purpose
Create.java	Create sample data
TestNormal1.java	Query the total number of quantity for customer “C1232122” using association approach.
TestNormal2.java	Query the total number of quantity for customer “C1232122” with order in open status using association approach.
TestCriteria1.java	Query the total number of quantity for customer “C1232122” using criteria class approach.
TestCriteria2.java	Query the total number of quantity for customer “C1232122” with order in open status using criteria class approach.

Download sample project and source files for trying criteria class.