Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

NuoDB & Stored Procedures: How to Maximize Hibernate Performance

DZone's Guide to

NuoDB & Stored Procedures: How to Maximize Hibernate Performance

· Java Zone
Free Resource

Build vs Buy a Data Quality Solution: Which is Best for You? Gain insights on a hybrid approach. Download white paper now!

This article walks you through a few Java Hibernate use cases that do multi-step query updates. I will point out the performance drawbacks of running the sample Hibernate code on the client-side, as opposed to running it wrapped around a server-side NuoDB Stored Procedure.

But before we dive deeper into the code, it is a good idea to understand some of the basics of NuoDB.

What makes NuoDB different from other DBMS offerings?

Let's take a hypothetical scenario: John has made a wonderful app that users are really excited about, and there are hundreds more users jumping in each hour. Now John has to scale his app as soon as he can!

He is left with two options:

  • He can use expensive machines and/or scary workarounds to scale his database.
  • He can re-write the entire app and use a lighter-weight, scale-out NoSQL DBMS.

But in doing so, he must make sure not to make compromises between performance and reliability, which is impossible in both scenarios above.

What if he had a third option?

Say that there was an RDBMS that had no complex workarounds. Say it's an RDBMS that is designed from the ground up to offer elastic scale-out performance without giving up SQL/ACID guarantees, which are essential for many operational database applications. Well, that's where NuoDB comes to John's rescue!

NuoDB is a web scale distributed database that is designed to scale out elastically on the cloud, on-premise and hybrid datacenter environments. 

Getting started with NuoDB

NuoDB supports MacOS, Windows, and various Linux OS platforms - see NuoDB downloads, but this article makes the assumption that the underlying OS is GNU/Linux. (Debian)

In a Linux shell, follow the setup steps below to get up and running with NuoDB:

# In case you haven’t installed maven yet.
$ sudo apt-get install maven2

# Download the latest NuoDB Release.

$ wget http://download.nuohub.org/nuodb-2.0.4.linux.x64.deb

# Install the NuoDB software.

$ sudo dpkg -i nuodb-2.0.4.linux.x64.deb
(Reading database ... 67407 files and directories currently installed.)
Preparing to unpack nuodb-2.0.4.linux.x64.deb ...
 * NuoDB web console already stopped
 * NuoDB auto console already stopped
 * Stopping NuoDB agent
Unpacking nuodb (2.0.4.5) over (2.0.4.5) ...
Setting up nuodb (2.0.4.5) ...
 * Starting NuoDB agent
Processing triggers for libc-bin (2.19-0ubuntu6) ...


# Switch dir to the location where NuoDB is installed
$ cd /opt/nuodb

# Set up a sample database.
$ ./run-quickstart
Setting PATH to /opt/nuodb/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/home/ubuntu/AWS-ElasticBeanstalk-CLI-2.6.1/eb/linux/python2.7
This script will set up a sample NuoDB database.

Starting a database named: test

 Starting a NuoDB Storage Manager
....
....

# After all the DB setup is done, you will end up in a SQL prompt, quit from that.
SQL> exit
-----
NuoSQL has finished.  We've left the 'test@localhost' database running for you.

Learn more about the system:
   Read the documentation online: http://doc.nuodb.com

Experiment with the system:
   Agent CLI: nuodbmgr --user 'quickstart' --password 'quickstart' --broker localhost
   Start Web Console (as root): service nuowebconsole start
   Access Web Console: http://localhost:8080

Run nuosql:
   export PATH=/opt/nuodb/bin:$PATH
   nuosql 'test@localhost' --user 'dba' --password 'goalie' --schema Hockey
-----

Cool! Now that you have installed NuoDB and have a sample DB, it's time to look into some sample code:

# change dir to the location where nuodb is installed

$ cd /opt/nuodb

# Install the NuoDB jdbc and hibernate jar files in the maven repository.

$ mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-jdbc \

-Dversion=2.0 \

-Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodbjdbc.jar

$ mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-hibernate \

-Dversion=2.0 \

-Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodb-hibernate.jar

# change to hibernate sample dir.

$ cd samples/hibernate

# compile the code

$ sudo mvn compile

# execute the code

$ sudo mvn exec:java

Eureka! You have run your first Hibernate app with NuoDB.

Performance Analysis

Let's do a performance analysis of MySQL + Hibernate vs. NuoDB + Hibernate. Before we proceed further, though, we need to understand NuoDB Java Stored Procedures.

NuoDB 2.0 introduced support for Java-language Stored Procedures. Java Stored Procedures support a richer programming language than SQL stored procedures, and also provide an easy migration path from client-side code to server-side extension.

It involves the following steps:

  • Create a Java Class that basically creates a statement from a connection, executes a query, and gets the result.
Statement stmt = conn.createStatement();

  stmt.execute("select * from system.tables");

  ResultSet rs = stmt.getResultSet();
  • Compile your Java class and create a JAR file. It is important to note that the jar file must contain all third party classes and/or resources that will be used by the stored procedure.

    In our example, that means the Hibernate classes and its dependencies and, because we use configuration files instead of annotations, the Hibernate resource files (*.cf.xml, *.hbm.xml etc.).  The easiest way to do this is to create an executable JAR file (e.g. with eclipse via export, with IntelliJ via build artifact, with Maven via shade).
  • Create javaclass in NuoSQL:
    `CREATE JAVACLASS classid FROM 'path/to/myfirstjavaproc.jar';`
  • Create a NuoSQL Stored Procedure:
    `CREATE PROCEDURE xyz(INOUT s string, INOUT n integer, INOUT d double, INOUT dec decimal(10,2)) LANGUAGE JAVA EXTERNAL 'classid:com.mycompany.MyFirstJavaProc.stored_proc_inout_args_db';`
  • Invoke the Java Stored Procedure: `EXECUTE xyz(?, ?, ?, ?);`
  • Unload the Java Class: `DROP JAVACLASS classid IF EXISTS;`

First up without NuoDB:

package com.nuodb.sample;

import java.util.List;

import org.apache.log4j.Logger;

import org.hibernate.Query;

import org.hibernate.SessionFactory;

import org.hibernate.Transaction;

import org.hibernate.cfg.Configuration;

import org.hibernate.Session;

import com.nuodb.sample.model.Address;

import com.nuodb.sample.model.AddressEntity;

import com.nuodb.sample.model.User;

/**

* A sample application that performs a few operations on a hibernate data

* model. The data model is a subset of the "Caveat Emptor" example used in _Java

* Persistence with Hibernate_ (http://caveatemptor.hibernate.org/). This subset

* only contains the User and AddressEntity entity objects and two styles of

* component objects.

*

* This application performs these steps:

*

* 1. Drop and create the necessary tables. To change this behavior, change the

* hbm2ddl.auto value in hibernate.cfg.xml. Allowed values are: "validate",

* "update", "create", or "create-drop".

*

* 2. Create two User records with associated ShippingAddresses.

*

* 3. Print the user records. Note that each record is printed to the console and

* the output shows the identity of the record along with its version number.

* For example, "User (3/0)" is identity 3, version 0. The identity is a

* surrogate key that is assigned when the record is created and does not

* change. The version changes each time the record is modified.

*

* 4. Modify the User's name and billing address by converting fields to

* upper-case. The home address is not changed.

*

* 5. Print the user records a second time to show the modifications. In

* addition to the field changes, note that the identity has not changed but the

* version is incremented (eg: "User (3/0)" becomes "User (3/1)".

*

* Each one of these steps is performed in a separate transaction.

*

* By default, the logging level prints warnings. To override, modify

* log4j.properties or add "-DCONSOLE_LEVEL=DEBUG" to the command line.

*/

public class SampleMain

{

  private static Logger LOG = Logger.getLogger(SampleMain.class);

  public static void main(String[] args)

  {

    runit(); 

  }

    public static int runit()

  {

  Configuration configuration = new Configuration();

  configuration.configure();

  SessionFactory factory = configuration.buildSessionFactory();

  Session session = factory.openSession();

  int users = 0;

  try {

    createUsers(session);

    printUsers(session);

    modifyUsers(session);

    users = printUsers(session);

  } finally {

    session.close();

    factory.close();

  }

    return users;

  }

  /**

  * Create new user records using the given hibernate session.

  */

  private static void createUsers(Session session)

  {

    Transaction txn = session.beginTransaction();

    try {

      // @formatter:off

      createUser(session,

      "Fred", "Flintstone",

      "fredf@example.com", "fred", true,

      "301 Cobblestone Way", "Bedrock", "00001",

      "1 Slate Drive", "Granitetown", "00002");

      createUser(session,

      "Barney", "Rubble",

      "barney@example.com", "barney", false,

      "303 Cobblestone Way", "Bedrock", "00001",

      "1 Slate Drive", "Granitetown", "00002");

      // @formatter:on

      txn.commit();

    } finally {

    if (txn.isActive())

    txn.rollback();

    }

  }

  /**

  * Instantiate a new User instance and save it to the given session.

  */

  // @formatter:off

  private static User createUser(Session session,

  String firstName, String lastName,

  String email, String userName, boolean admin,

  String homeStreet, String homeCity, String homeZip,

  String shipStreet, String shipCity, String shipZip)

  // @formatter:on

  {

    User user = new User(firstName, lastName, userName, "", email);

    user.setHomeAddress(new Address(homeStreet, homeZip, homeCity));

    user.setAdmin(admin);

    // always use home address for billing

    user.setBillingAddress(new Address(homeStreet, homeZip, homeCity));

    user.setShippingAddress(new AddressEntity(shipStreet, shipZip, shipCity));

    user.getShippingAddress().setUser(user);
  
    session.save(user);
  
    LOG.info("Created user " + user);

    return user;

  }

  /**

  * Modify all Users by converting the name and billing address fields to upper

  * case. Home and shipping address are not changed.

  */

  private static void modifyUsers(Session session)

  {

    Transaction txn = session.beginTransaction();

    try {

      Query query = session.createQuery("from " + User.class.getName());

      @SuppressWarnings("unchecked")

      List<User> users = query.list();

      System.out.println("Found " + users.size() + " user records:");

      for (User user : users) {

      LOG.info("Updating " + user);

      user.setFirstname(user.getFirstname().toUpperCase());

      user.setLastname(user.getLastname().toUpperCase());

      user.setAdmin(false);

      Address bill = user.getBillingAddress();

      bill.setStreet(bill.getStreet().toUpperCase());

      bill.setCity(bill.getCity().toUpperCase());

      bill.setZipcode(bill.getZipcode().toUpperCase());

    }

    txn.commit();

    } finally {

      if (txn.isActive())

      txn.rollback();

    }

  }

  /**

  * Iterate over all User records and print them.

  */

  private static int printUsers(Session session)

  {

    int count = 0;

    Transaction txn = session.beginTransaction();

    try {

      Query query = session.createQuery("from " + User.class.getName());

      @SuppressWarnings("unchecked")

      List<User> users = query.list();

      System.out.println("Found " + users.size() + " user records:");

      for (User user : users) {

      System.out.println(user);

      System.out.println("  home: " + user.getHomeAddress());

      System.out.println("  bill: " + user.getBillingAddress());

      System.out.println("  ship: " + user.getShippingAddress());

      count++;

    }

    txn.commit();

    } finally {

      if (txn.isActive())

      txn.rollback();

    }

  return count;

  }

}

Same code with __nuoDB__ exposed as Stored procedure:

package com.nuodb.sample;

import com.nuodb.sample.model.Address;

import com.nuodb.sample.model.AddressEntity;

import com.nuodb.sample.model.User;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

import java.sql.Connection;

import java.util.List;

public class StoredProcMain {

  public static void stored_proc_hibernate(Connection conn, String firstname,String lastname,String username)

  throws java.sql.SQLException {

    System.out.println(String.format("stored_proc_hibernate = " +username));

    Configuration configuration = new Configuration();

    configuration.configure();

    String chorus = System.getProperty("nuodb.url");

    if (chorus != null)

    {

      System.out.println("Using JDBC URL: " + chorus);

      configuration.setProperty("hibernate.connection.url", chorus);

    }

    long start=System.currentTimeMillis();

    SessionFactory factory = configuration.buildSessionFactory();

    Session session = factory.openSession(conn);

    for(int i=0;i<1000;i++)

    {

      User user = new User(firstname+i,lastname+i ,username+i, "", username+i+"@gmail.com");

      user.setHomeAddress(new Address("street", "address", "city"));

      user.setAdmin(false);

      user.setBillingAddress(new Address("street", "address", "city1"));

      user.setShippingAddress(new AddressEntity("street1", "street2", "city3"));

      user.getShippingAddress().setUser(user);

      session.save(user);

    }

    printUsers(session);

    modifyUsers(session);

    printUsers(session);

    System.out.println("total time taken"+(System.currentTimeMillis()-start));

  }

  private static void modifyUsers(Session session)

  {

    Query query = session.createQuery("from " + User.class.getName());

    @SuppressWarnings("unchecked")

    List<User> users = query.list();

    System.out.println("Found " + users.size() + " user records:");

    for (User user : users) 

 { user.setFirstname(user.getFirstname().toUpperCase()); user.setLastname(user.getLastname().toUpperCase()); user.setAdmin(false); Address bill = user.getBillingAddress(); bill.setStreet(bill.getStreet().toUpperCase()); bill.setCity(bill.getCity().toUpperCase()); bill.setZipcode(bill.getZipcode().toUpperCase()); } } /** * Iterate over all User records and print them. */ private static int printUsers(Session session) { int count = 0; Query query = session.createQuery("from " + User.class.getName()); @SuppressWarnings("unchecked") List<User> users = query.list(); System.out.println("Found " + users.size() + " user records:"); for (User user : users) { System.out.println(user); System.out.println(" home: " + user.getHomeAddress()); System.out.println(" bill: " + user.getBillingAddress()); System.out.println(" ship: " + user.getShippingAddress()); count++; } return count; } }

Install the stored procedure and execute it:

nuosql test@localhost --user 'dba' --password 'goalie' --schema test
SQL> use sample;

SQL> CREATE JAVACLASS hsp1 FROM 'nuodb-hibernate-sample.jar'; 

SQL> CREATE PROCEDURE newsp1(IN firstname String,IN lastname String,IN username String) LANGUAGE JAVA EXTERNAL 'hsp1:com.nuodb.sample.StoredProcMain.stored_proc_hibernate' ;

SQL> execute newsp1(?,?,?);

Enter value: "Hemanth"

Enter value: "HM"

Enter value: "GNUMANTH"

Basically, we are doing CRUD operations and printing the users. Here are some performance results:

Created 2000 users in 1000 transactions.  Two users are created in each transaction:

  • With MySQL: __25.032 seconds__
  • With NuoDB (equivalent to MySQL code): __14.038secs__
  • With NuoDB (and Java Stored Procedures): __10seconds__

In applications where every millisecond matters, __10seconds__ instead of __25.032__ is undoubtedly a major performance improvement!

By the way, you can view the results of these queries in `/var/log/nuodb/agent.log`

Now that you have NuoDB running a Hibernate stored procedure, you can simply scale things out by starting one or more additional Transaction Engines. NuoDB takes care of automatically ensuring that the Java code is deployed in any new Transaction Engine that is part of a database.  The simple management command for starting a Transaction engine is the following:

$ nuodbmgr --broker localhost --user domain --password bird

nuodb [domain] > start process te host scaleouthostname  database test

In this example, scaleouthostname is a new machine that is already provisioned for running NuoDB.

When running the client program, any new connection to the database will take advantage of the scaleouthostname.

So what are you waiting for? Go ahead and use the power of NuoDB!

Build vs Buy a Data Quality Solution: Which is Best for You? Maintaining high quality data is essential for operational efficiency, meaningful analytics and good long-term customer relationships. But, when dealing with multiple sources of data, data quality becomes complex, so you need to know when you should build a custom data quality tools effort over canned solutions. Download our whitepaper for more insights into a hybrid approach.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}