ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Tuning Derby

by Dejan Bosanac
01/31/2007

There is a big difference in the behavior of a database when it is populated with a small amount of test inputs and when it holds a large amount of data. Usually, you would not address these database performance issues early in the development process, but when the time comes, you should take some action to ensure that the application is working correctly with large amounts of data.

The all-Java open-source database Derby is no exception, so you'll have to make sure it will not be a bottleneck to your application. Although you can find comprehensive material on this topic among Derby's manuals, I would like to focus on certain issues in more detail and give some examples from my own experience. I will focus on application performances related to selecting data from large tables.

First of all, there are various tips on how you should tune Derby properties such as page size and the size of the cache. Playing with these parameters can help you improve performance to some degree, but usually the bigger problem lies in your application and database design, so you should focus on these issues first and leave Derby properties for the end.

In the following sections, I will cover some techniques that can help you optimize problematic parts of your application. But as with all other performance-tuning activities, measure and positively identify problems before optimizing.

A Simple Example

Let's start with a simple example: We have a "search"/"list" page in our web application that has to deal with a table of nearly 100,000 rows, and let's say that the table is not trivial (i.e., that it has at least 10 columns). I will write an example in plain JDBC so we can focus on database and JDBC issues. The principles explained in this article should be applicable to all Object-Relation mapping tools as well.

In order to give your users the ability to list a large table, you would normally start with the simple query:

select * from tbl

The resulting JDBC code snippet for this would be similar to the following:

Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection connection = DriverManager.getConnection (
        "jdbc:derby://localhost:1527/testDb;");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from tbl");
ArrayList allResults = new ArrayList();
while (rs.next()) {
        // Object-Relation mapping code to populate your
        // object from result set row
        DomainObject domainObject = populate(rs);
        allResults.add(modelObject);
}
System.out.println("Results Size: " + allResults.size());

Here, we encounter our first problem. Trying to execute code like this and populate 100,000 (or even more) domain objects will almost certainly lead to a java.lang.OutOfMemoryError as Java runs out of heap space. So for starters, we have to find a way to make this code just work.

Paging Result Sets

As the amount of data in your application grows, the first thing you will want to do is add paging support for certain pages (or views in general). As you saw in the introductory example, simple queries that try to fetch large result sets can easily produce OutOfMemoryErrors.

Many database servers support specialized SQL constructs that can be used to retrieve a specified subset of query results. For example, in MySQL you'll find the LIMIT and OFFSET keywords, which can be used in SELECT queries. So if you execute a query like this:

select * from tbl LIMIT 50 OFFSET 100

your result set will contain 50 rows starting from the 100th result, even if the original query returned 100,000 rows. Many other database vendors provide similar functionality through different constructs. Unfortunately, Derby does not provide such functionality, so you have to stay with the original select * from tbl query and implement a paging mechanism on the application level. Let's look at the following example:

Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Connection connection = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/testDb;");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM tbl");
ArrayList allResults = new ArrayList();
int i = 0;
while (rs.next()) {
        if (i > 50 && i <= 100) {
                // O-R mapping code populate your row from result set
                DomainObject domainObject = populate(rs);
                allResults.add(modelObject);
        }
        i++;
}
System.out.println("Results Size: " + allResults.size());

Pages: 1, 2, 3

Next Pagearrow