Performance nativeQuery vs JDBC

Hibernate ORM
1

Hi,

I was testing Hibernate NativeQuery vs JDBC and found JDBC is much faster as shown in my code below:

This is my hibernate/JPA code

	long timeTx = 0;
	long startTx = System.currentTimeMillis();

	List batch = null;

	String query = "SELECT " 
			+ "e.id AS ereturn_id" 
			+ ", e.globalId" 
			+ ", e.rma AS rma" 
			+ ", e.trackingNumber"
			+ ", e.pieces AS pieces" 
			+ ", e.weight AS weight"  
			// + ", e.groupName"
			+ ", pi.id AS product_item_id"
			+ ", pi.returnAction"
			+ ", pi.price"
			// + ", pi.groupName"
			//				+ ", r.creationtime AS receptacle_creationtime"
			//				+ ", r.type AS receptacle_type"
			//				+ ", r.id AS receptacle_id"
			+ ", pd.sku AS sku" 
			+ ", shipper.id AS shipper_id" 
			+ ", shipper.firstName AS shipper_first_name"
			+ ", shipper.lastName AS shipper_last_name" 
			+ ", shipper.address AS shipper_address"
			+ ", shipper.state AS shipper_state" 
			+ ", shipper.city AS shipper_city"
			+ ", shipper.country AS shipper_country" 
			+ ", shipper.postalcode AS shipper_postalcode"
			+ ", shipper.contactNumber AS shipper_contactNumber" 
			+ ", consignee.id AS consignee_id"
			+ ", consignee.firstName AS consignee_first_name" 
			+ ", consignee.lastName AS consignee_last_name"
			+ ", carrier.id AS carrier_id" 
			+ ", carrier.firstName AS carrier_first_name"
			+ ", carrier.city AS carrier_address" 
			+ ", carrier.city AS carrier_city"
			+ ", carrier.state AS carrier_state" 
			+ ", carrier.country AS carrier_country"
			+ ", carrier.postalcode AS carrier_postalcode" 
			+ " FROM" 
			+ " product_item pi"
			+ " JOIN ereturn e ON pi.ereturn = e.id" 
			+ " JOIN user shipper ON e.shipper = shipper.id"
			+ " JOIN user consignee ON e.consignee = consignee.id" 
			+ " JOIN user carrier ON e.carrier = carrier.id"
			+ " JOIN product_definition pd ON pi.product = pd.id" 
			//				+ " LEFT JOIN receptacle r ON pi.receptacle = r.id" 
			+ " WHERE"
			+ " pi.receptacle IS NULL"
			//				+ " r.masterCrossDock IS NULL"
			+ " AND carrier.email = :carrierEmail" 
			+ " AND e.scanDateTime IS NOT NULL"
			+ " AND e.status = 'RECEIVED'";

	if (shipperEmail != null && !shipperEmail.isEmpty()) {
		query += " AND shipper.id = :shipper";
	}

	if (returnAction != null && !returnAction.isEmpty()) {
		query += " AND pi.returnAction = :returnAction";
	}

	Query q = em.createNativeQuery(query, "ProductItemBatchMapping");

	q.setParameter("carrierEmail", issuer);

	if (shipperEmail != null && !shipperEmail.isEmpty()) {

		User shipper = UserDAO.fetch(shipperEmail, UserType.SHIPPER); // Another SHIT!!! change shipper email for id
		// so we can save this query
		q.setParameter("shipper", shipper.getId());
	}
	
	if (returnAction != null && !returnAction.isEmpty()) {
		q.setParameter("returnAction", returnAction);
	}

	batch = q.getResultList();
	
	long endTx = System.currentTimeMillis();
	timeTx = endTx - startTx;
	
	logger.info("How long does it takes to retrieve List<ProductItem> objects from JPA? " + timeTx);

This is my JDBC code:

public List<ProductItem> getEreturn(String carrierEmail, String shipperEmail, String returnAction) throws SQLException {
	
	List<ProductItem> productItems = new ArrayList();
	
	String sql = "SELECT " 
			+ "e.id AS ereturn_id" 
			+ ", e.globalId" 
			+ ", e.rma AS rma" 
			+ ", e.trackingNumber"
			+ ", e.pieces AS pieces" 
			+ ", e.weight AS weight"  
			// + ", e.groupName"
			+ ", pi.id AS product_item_id"
			+ ", pi.returnAction"
			+ ", SUM(pi.price)"
			// + ", pi.groupName"
			//				+ ", r.creationtime AS receptacle_creationtime"
			//				+ ", r.type AS receptacle_type"
			//				+ ", r.id AS receptacle_id"
			+ ", pd.sku AS sku" 
			+ ", shipper.id AS shipper_id" 
			+ ", shipper.firstName AS shipper_first_name"
			+ ", shipper.lastName AS shipper_last_name" 
			+ ", shipper.address AS shipper_address"
			+ ", shipper.state AS shipper_state" 
			+ ", shipper.city AS shipper_city"
			+ ", shipper.country AS shipper_country" 
			+ ", shipper.postalcode AS shipper_postalcode"
			+ ", shipper.contactNumber AS shipper_contactNumber" 
			+ ", consignee.id AS consignee_id"
			+ ", consignee.firstName AS consignee_first_name" 
			+ ", consignee.lastName AS consignee_last_name"
			+ ", carrier.id AS carrier_id" 
			+ ", carrier.firstName AS carrier_first_name"
			+ ", carrier.city AS carrier_address" 
			+ ", carrier.city AS carrier_city"
			+ ", carrier.state AS carrier_state" 
			+ ", carrier.country AS carrier_country"
			+ ", carrier.postalcode AS carrier_postalcode" 
			+ " FROM" 
			+ " product_item pi"
			+ " JOIN ereturn e ON pi.ereturn = e.id" 
			+ " JOIN user shipper ON e.shipper = shipper.id"
			+ " JOIN user consignee ON e.consignee = consignee.id" 
			+ " JOIN user carrier ON e.carrier = carrier.id"
			+ " JOIN product_definition pd ON pi.product = pd.id" 
			//				+ " LEFT JOIN receptacle r ON pi.receptacle = r.id" 
			+ " WHERE"
			+ " pi.receptacle IS NULL"
			//				+ " r.masterCrossDock IS NULL"
			+ " AND carrier.email = ?" 
			+ " AND e.scanDateTime IS NOT NULL"
			+ " AND e.status = 'RECEIVED'";
	
	if (shipperEmail != null && !shipperEmail.isEmpty()) {
		sql += " AND shipper.id = ?";
	}

	if (returnAction != null && !returnAction.isEmpty()) {
		sql += " AND pi.returnAction = ?";
	}
	
	PreparedStatement stmt = conn.prepareStatement(sql);

    stmt.setString(1, carrierEmail);
    
    if (shipperEmail != null && !shipperEmail.isEmpty()) {

		User shipper = UserDAO.fetch(shipperEmail, UserType.SHIPPER); // TODO: change shipper email for id
		// so we can save this query
		stmt.setLong(2, shipper.getId());
	}
	
	if (returnAction != null && !returnAction.isEmpty()) {
		stmt.setString(3, returnAction);
	}
	
    ResultSet rs = stmt.executeQuery();

    while (rs.next()) {

        productItems.add(new ProductItem(rs));
    }

    return productItems;
}

Time to fetch data using hibernate/JPA nativequery => ~3.6 seconds
Time to fetch data using JDBC => 46 ms

QuestionL Why is hibernate so much lower compared to JDBC? is this normal?

2

That’s not how you write benchmark tests. You need thousands of iterations for warming up and then 5 times more for the actual testing. And use a Metrics library that supports percentiles as well.

Maybe the Hibernate Query executed on a cold DB while for JDBC the pages were already cached. Or maybe the plan was cached as well.

3

Hi Vlad,

Thank you for your response. The database has less than 40MB of data and the hardware it is running has plenty of resources plus system was idle when I was running my code. My intention was not to run a performance test but to give an overview of the difference between hibernate and JDBC. It doesn’t matter if I run JDBC or Hibernate on first on a “cold” database as the results I am getting are consistent to my original post so I am guessing this is not a database issue.

I am using same Class constructor for JDBC and Hibernate nativequery so it has something hibernate is doing under the hood.

My point is that ~3.6 seconds to retrieve 1100 rows is too long and I don’t understand what hibernate is doing under the hood. That is why I decided to create a thread in this forum, to see if someone could help me understand or point me to the source of the issue I am hitting.

Forgot to mention I am using hibernate-core 5.2.8.Final

thank you very much

4

The only way to see why it takes so much in your case is to profile it.

However, there’s no reason why you’d ever want to fetch 1000 records as you can’t fit all that data in the UI and pagination is the way to go even for batch processing. For more details, check out this article.

So, if you limit the result to just as much data as you really need, it might be that you won’t even notice any overhead.

Hibernate overhead is rather low, it’s just Java code which executes in microseconds. On the other hand, the code executing on the database side takes orders of magnitude more to execute. That’s why you need indexing and making sure your working set resides in the database cache, not load data from the disk on every query.

1 Like