MrBool
You must be logged in to give feedback. Click here to login
[Close]

You must be logged to download.

Click here to login

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Active Record: Improving Performance

In this article we will see how to improve performance of Active records with Joins

[close]

You didn't like the quality of this content?

Would you like to comment what you didn't like?

Introduction

My previous article, Framework-free enterprise patterns: Active Record , introduced the Active Record pattern and showed a simple implementation. This implementation has the advantage of being simple, easy to understand, quick to write, and has a high degree of isolation between the logic required to deal with different types: everything about one type is encapsulated within its class.

It does, however, have one serious problem: lack of performance. Every request we make for an Account record triggers an extra database query for its corresponding Customer record. If we have a million accounts and request the list of all of them, we’ll get a million database queries. Obviously, in a large system this is totally unacceptable.

Active Record with Joins

The obvious solution is to use a join in the implementation of the query methods to obtain the Customer’s data at the same time as we get the Account. While this is relatively simple if we ensure that each table has column names that are unique, we would like to not have such a constraint on column naming Column names should, in my opinion, be natural and correspond to the data they describe, not artificially forced into a naming scheme that has only structural benefits. But unfortunately, unlike other database APIs like Microsoft’s ADO.NET, JDBC doesn’t allow us to retrieve data from a result set using a “table.column” syntax. We’re therefore going to have to rename any conflicting columns in the query.

The first change is to change Customer’s ResultSet constructor to default protection rather than private, so it can be created from Account. Now we have to change the queries to rename the columns, and at the same time change the constructor to extract its data using the new names; I’ve decided to rename all of the columns in the queries as we don’t know what tables or columns will be added to the database in future, and I don’t want to have to check every table each time for columns that might conflict in a join. I’ve decided on a simple scheme of renaming each column to “entity_column”, where “entity” is the singular name for an entity (i.e. the class name in lower case, or the name of the table in singular rather than plural). Changing over the “get” method my results look like this:

Listing 1: First set of changes to the Customer class

 
	Customer (ResultSetrs) throwsSQLException
	{
		id = rs.getInt ("customer_id");
		name = rs.getString ("customer_name");
	}

	....

	publicstatic Customer get (Connection connection, int id) throwsSQLException
	{
		try (PreparedStatement s = connection.prepareStatement (
				"select customers.id customer_id, customers.name customer_name"+
				" from customers where customers.id=?"))
		{
			s.setInt (1, id);
			try (ResultSet r = s.executeQuery ())
			{
				if (r.next ()) 
					returnnew Customer (r);
				returnnull;
			}
		}
	}

A similar change can be made to getAll(). No changes need to be made to the writer methods update() and create().

Now we can look at the changes to Account. Its constructor changes similarly to Customer’s, only we extend it so that if its ResultSet includes columns for customer (we check by searching for the customer_id column) we create a Customer directly rather than querying for a new one:

Listing 2: Account’s updated constructor

 
	private Account (Connection connection, ResultSetresultSet) throwsSQLException
	{
		id = resultSet.getInt ("account_id");
		balance = resultSet.getDouble ("account_balance");
		if (resultSet.findColumn ("customer_id") > 0)
			customer = new Customer (resultSet);
		else
			customer = Customer.get (connection, resultSet.getInt ("account_customer"));
	}

We can now change the get*() methods in Account to use the necessary field renaming and add our first join. We’ll start with the method to list all accounts of a specific customer:

Listing 3: getting accounts with a join

 
	publicstatic List<Account>getByCustomer (Connection connection, int id) throwsSQLException
{
		try (PreparedStatement s = connection.prepareStatement (
			"select accounts.id account_id, accounts.balanceaccount_balance, "+
			"accounts.customeraccount_customer, customers.id customer_id, "+
"customers.name customer_name" +
			" from accounts left join customers on accounts.customer=customers.id"+
			"  whereaccounts.customer=?"))
		{
			s.setInt (1, id);
			try (ResultSet r = s.executeQuery ())
			{
				ArrayList<Account> result = newArrayList<> ();
				while (r.next ()) 
					result.add (new Account (connection, r));
				return result;
			}
		}
}

Testing this method while keeping an eye on the query log shows that no query for a customer is made when it’s called. We’re getting there.

Unfortunately, looking at that method, it is a bit of a mess. It’s full of column names, and these column names will be duplicated into every query method. Worse, it also has knowledge of the columns of an entirely different class - we’ve violated Customer’s encapsulation by placing logic relating to its internal structure in another class. We fix both of these problems in the same way: by moving the list of column renames for each table into a static method of that table (we could use a constant, but a method seems to encapsulate it better for me: the value of a constant is part of the class’s public interface, but the return value of the method can change without being apparent to its consumers). So, for example Customer.getAll() now looks like this:

Listing 4: Updating Customer to encapsulate its column projection

	static String projection ()
	{
	return"customers.id customer_id, customers.name customer_name";
}
	
	publicstatic List<Customer>getAll (Connection connection) throwsSQLException
	{
		try (Statement s = connection.createStatement ();
			ResultSet r = s.executeQuery ("select "+projection ()+" from customers"))
		{
			ArrayList<Customer> result = newArrayList<> ();
			while (r.next ()) 
				result.add (new Customer (r));
			return result;
		}
	}

And Account changes to have its own projection() method and to reference Customer’s:

Listing 5: Using encapsulated projection from the Account class

	static String projection ()
{
	return"accounts.id account_id, accounts.balanceaccount_balance, “+
“accounts.customeraccount_customer";
}
	
	publicstatic List<Account>getAll (Connection connection) throwsSQLException
	{
		try (Statement s = connection.createStatement ();
			ResultSet r = s.executeQuery (
			"select "+projection ()+","+Customer.projection()+
			" from accounts left join customers on accounts.customer=customers.id"))
		{
			ArrayList<Account> result = newArrayList<> ();
			while (r.next ()) 
				result.add (new Account (connection, r));
			return result;
		}
	}

One further optimisation is possible. We do not necessarily always need to know about the Customer when we get an Account: we may have an operation that only works on an Account without touching it’s corresponding Customer. In such a case, we’d like to be able to load only the Account without having to fetch the Customer’s information. The Account object’s getCustomer() method, however, should not change behaviour - the Customer data should still be available if we decide we need it later. This can be achieved quite simply, but has a slight cost: we’ll need to store the database’s Connection object. This is only really viable if the Account object is short-lived and only used in a single thread. We need to make sure that this really is the case if we want to use it like this.

The modifications are quite simple:

Listing 6: Getting an Account without its Customer

publicclass Account
{
	privatefinalintid;
	privatedoublebalance;
	private Customer customer;
	private Connection connection;
	privateintcustomerId;
	
	private Account (Connection connection, ResultSetresultSet) throwsSQLException
	{
		id = resultSet.getInt ("account_id");
		balance = resultSet.getDouble ("account_balance");
		customerId = resultSet.getInt ("account_customer");
		if (resultSet.findColumn ("customer_id") > 0)
			customer = new Customer (resultSet);
		else
		{
			this.connection = connection;
		}
	}
	public Customer getCustomer ()
{
		if (customer == null)
		{
			try
			{
				customer = Customer.get(connection, customerId);
				connection = null;
			}
			catch (SQLException e)
			{
				thrownewRuntimeException (e);
			}
		}
	returncustomer;
	}
	
	publicvoidsetCustomer (Customer customer)
	{
	this.customer = customer;
	this.customerId = customer.getId();
	}
	
	publicboolean create (Connection connection) throwsSQLException
	{
		try (PreparedStatement s = connection.prepareStatement (
"insert into accounts (balance,customer) values (?,?)"))
		{
			s.setDouble (1, balance);
			s.setInt (2, customerId);
			returns.execute ();
		}
	}
	publicboolean update (Connection connection) throwsSQLException
	{
		try (PreparedStatement s = connection.prepareStatement (
"update accounts set balance=?, customer=? where id=?"))
		{
			s.setDouble (1, balance);
			s.setInt (2, customerId);
			s.setInt (3, id);
			returns.execute ();
		}
	}
}

With this change in place, we don’t need to update our get() method to execute a join - it seems like places where we get a single account directly by id are those that are likely to not need to operate on the customer, and at the very least the extra cost of running an extra query per account touched in such places is likely to be minimal.

This is all for this article. See you next time.



My main area of specialization is Java and J2EE. I have worked on many international projects like Recorders,Websites,Crawlers etc.Also i am an Oracle Certified java professional as well as DB2 certified

What did you think of this post?
Services
Know how to keep MrBool Online
SUPPORT US
SUPPORT US
With your help, we can keep providing free content and helping you to be a better professional
support us
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually � in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits � in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login