Performance Tips and Tricks
Page 2 of 3

The Top Ten Tips

  1. Tip One. Use a Fast JVM with Lots of Memory, and Tune the JIT. Using a fast JVM can make a 2X performance improvement.
  2. Tip Two. Use Stored Prepared Statements and Prepared Statements to save on costly compilation time. Stored prepared statements let you avoid most compilation altogether, and some Cloudscape applications have seen 2-10X performance increases as a result of using PreparedStatements instead of Statements.
  3. Tip Three. Create Indexes, and Make Sure They're Being Used. Indexes speed up queries dramatically if the table is much larger than the number of rows retrieved.
  4. Tip Four. Tune How You Load Data. Create indexes before, check constraints after.
  5. Tip Five. Increase the Size of the Data Page Cache and prime all the caches.
  6. Tip Six. Tune the Size of Database Pages. Using large database pages has provided a performance improvement of up to 50%. There are also other storage parameters worth tweaking. If you use large database pages, increase the amount of memory available to Cloudscape.
  7. Tip Seven. Avoid Expensive Queries.
  8. Tip Eight. Use the Appropriate getXXX and setXXX Methods for the Type.
  9. Tip Nine. Tune Database Booting/Class Loading. System startup time can be improved by reducing the number of databases in the system directory.
  10. Tip Ten. Recompile Stale Stored Prepared Statements if data distribution changed. If you created a stored prepared statement when the table was empty, and now it has a lot of rows, you may need to recompile to get a better query plan.

These tips may or may not solve your particular performance problem. Be sure to visit the Support section of Cloudscape's Web site for up-to-date performance tips and tricks.

Tip One. Use a Fast JVM with Lots of Memory, and Tune the JIT

Not all Java Virtual Machines (JVMs) are created equal. Using a JVM with a Just-in-Time Compiler (JIT) can bring a 2X performance improvement to Cloudscape applications. Many JVMs are available for different hardware and operating system combinations. Here are some:

  1. Javasoft JDK/JRE Performance Packs (available for Windows only) (http://java.sun.com/j2se/)

    The "vanilla" JDK or Java Runtime Environment (JRE) always includes a JIT. See the Javasoft JDK page for details.

  2. Visual Cafe (available for Windows only)

    If you're using Symantec Cafe, the JVM included with it has a JIT. See the Symantec Cafe home page.

  3. The Microsoft Virtual Machine (available for Windows only) (http://www.microsoft.com/java/sdk/)

    The Microsoft Virtual Machine, also known as jview, includes a JIT compiler.

  4. IBM Developer Kit and Runtime Environment for Windows (http://www.ibm.com/java/jdk/download/index.html)

    IBM's JVM and JRE for Windows.

However, also try your application with and without a JIT. Sometimes GUI applications run faster without the JIT. Another reason to use -nojit is if startup time is more important than long-term performance.

For example: (JDK 1.1)

java -nojit COM.cloudscape.tools.cview

(JDK 1.2)

java -Djava.compiler=NONE COM.cloudscape.tools.cview

In addition, allocate as much memory as you can to the JVM to use. Use the minimum size flag upon start up; it can reduce the boot time by at least a couple of seconds. For example:

java -ms16m -mx16m COM.cloudscape.tools.ij 

In a multi-user environment, use a machine with a lot of memory and allocate as much memory as you can for the JVM to use. Cloudscape can run in a small amount of memory, but the more memory you give it, the faster it runs.

NOTE: If you do not allocate memory using -mx, the JVM typically limits itself to 32 MB.

Tip Two. Use Stored Prepared Statements and Prepared Statements

You can name and store prepared statements in a database. Storing prepared statements helps you avoid costly compilation time, because the statement is stored precompiled. In your application, you do have to "prepare" a statement that references the stored prepared statement, but this statement has a minimal compilation cost.

You will find a more complete description of stored prepared statements in Storing Prepared Statements to Improve Performance.

Where stored prepared statements are not possible, use prepared statements. In Cloudscape, as with most relational database management systems, performing an SQL request has two steps: compiling the request and executing it. By using prepared statements (java.sql.PreparedStatement) instead of statements (java.sql.Statement) you can help Cloudscape avoid unnecessary compilation, which saves time. In general, any query that you will use more than once should be a stored prepared statement or prepared statement.

For more information, see Avoiding Compiling SQL Statements.

Making this change has resulted in 2-10X performance improvement, depending on the complexity of the query. More complex queries show more benefit from being prepared.

Tip Three. Create Indexes, and Make Sure They're Being Used

By creating indexes on columns by which you often search a table, you can reduce the number of rows that Cloudscape has to scan, thus improving performance. Depending on the size of the table and the number of rows returned, the improvement can be dramatic. Indexes work best when the number of rows returned from the query is a fraction of the number of rows in the table.

There are some trade-offs in using indexes: indexes speed up searches but slow down inserts and updates. As a general rule, every table should have at least a primary key constraint.

See Index, Index, Index for more information.

Tip Four. Tune How You Load Data

When loading large amounts of data into tables, create indexes (including those created to back primary, unique, and foreign key constraints) before you load and check constraints after. Having indexes already defined before you bulk load is faster than creating the indexes after the data is loaded. Disable check constraints before loading, and re-enable after loading.

Tip Five. Increase the Size of the Data Page Cache

You can increase the size of a database's data page cache, which consists of the data pages kept in memory. When Cloudscape can access a database page from the cache instead of reading it from disk, it can return data much more quickly.

The default size of the data page cache is 40 pages. In a multi-user environment, or in an environment where the user accesses a lot of data, increase the size of the cache. You configure its size with the cloudscape.storage.pageCacheSize property. For more information about how to set this property and how to estimate memory use, see cloudscape.storage.pageCacheSize.

NOTE: Cloudscape can run even with a small amount of memory and even with a small data page cache, although it may perform poorly. Increasing the amount of memory available to Cloudscape and increasing the size of the data page cache improve performance.

In addition, you may want to prime all the caches in the background to make queries run faster when the user gets around to running them.

These caches include:

  • the page (user data) cache (described above)

    Prime this cache by selecting from much-used tables that are expected to fit into the data page cache.

  • the data dictionary cache

    The cache that holds information stored in the system tables. You can prime this cache with a query that selects from commonly used user tables.

  • the statement cache

    The cache that holds connection-specific Statements (including -PreparedStatements). You can prime this cache by preparing common queries ahead of time in a separate thread.

  • the stored prepared statement cache

    The cache that holds system-wide stored prepared statements.

Tip Six. Tune the Size of Database Pages

Stick with 4K as the page size (the default, and the size operating systems use) unless:

  • You are storing large objects.
  • You have very large tables (over 10,000 rows).

    For very large tables, large pages reduces the number of I/Os required.

  • You have very small tables and have footprint issues.

    For very small tables that will never grow, use 2K.

  • Your application is read-only.

    For read-only applications, use a large page size (for example, 16K) with a pageReservedSpace of 0.

You may need to experiment with page size to find out what works best for your application and database.

Performance Trade-Offs of Large Pages

Using large database pages benefits database performance, notably decreasing I/O time. By default, the database page size is 4096 bytes. You can change the default database page size with the cloudscape.storage.pageSize property. For example:

cloudscape.storage.pageSize=8192

NOTE: Large database pages require more memory.

If row size is large, generally page size should be correspondingly large. If row size is small, page size should be small. Another rough guideline is to try to have at least 10 average-sized rows per page (up to 128K). Page size should not be less than 4K unless the table is very small and the whole base table can fit into one page.

Use a larger page size for tables with large columns or rows. After page size reaches 32K, each row inflicts a sightly higher overhead, so if the rows are small (say, < 100 bytes), it probably doesn't make sense to set the page size larger than 32K.

However, some applications involve rows whose size will vary considerably from user to user. In that situation, it is hard to predict what effect page size will have on performance.

If a table contains one large column along with several small columns, put the large column at the end of the row, so that commonly used columns won't be moved to overflow pages. Don't index large columns.

Large page size for indexes improves performance considerably.

When Large Page Size Does Not Improve Performance

  • Selective Queries

    If your application's queries are very selective and use an index, large page size doesn't buy you much and potentially degrades performance because a larger page takes longer to read.

When Large Page Size Is Not Desirable

  • Limited memory

    Large database pages reduce I/O time because Cloudscape can access more data with fewer I/Os. However, large pages require more memory. Cloudscape allocates a bulk number of database pages in its page cache by default. If the page size is large, the system may run out of memory.

    Here's a rough guideline: If the system is running Windows 95 and has more than 32 MB (or Windows NT and has more than 64 MB), it is probably beneficial to use 8K rather than 4K as the default page size.

    Use the -mx flag as an optional parameter to the JVM to give the JVM more memory upon startup.

    For example:

    java -mx64 myApp

  • Limited disk space

    If you can't afford the overhead of the minimum two pages per table, keep your page sizes small.

  • Large number of users

    Very large page size reduces concurrency slightly when the system uses row-level locking.

Tip Seven. Avoid Expensive Queries

Some queries can, and should, be avoided. Two examples:

SELECT DISTINCT nonIndexedCol FROM HugeTable

SELECT * FROM HugeTable ORDER BY nonIndexedColumn

See Prevent the User from Issuing Expensive Queries.

Tip Eight. Use the Appropriate getXXX and setXXX Methods for the Type

JDBC is permissive. It lets you use java.sql.ResultSet.getFloat to retrieve an int, java.sql.ResultSet.getObject to retrieve any type, and so on. (java.sql.ResultSet and java.sql.CallableStatement provide getXXX methods and java.sql.PreparedStatement and java.sql.CallableStatement provide setXXX methods.) This permissiveness is convenient but expensive in terms of performance.

For performance reasons, use the recommended getXXX method when retrieving values, and use the recommended setXXX method when setting values for parameters.

Table 2-1 shows the recommended getXXX methods for given java.sql (JDBC) types, and their corresponding SQL-J types.

Table 2-1 Mapping of java.sql.Types to SQL-J Types 

Recommended getXXX Method

java.sql.Types

SQL-J Types

getLong

BIGINT

LONGINT

getBytes

BINARY

BIT

getBoolean

BIT

BOOLEAN

getString

CHAR

CHAR

getDate

DATE

DATE

getBigDecimal

DECIMAL

DECIMAL

getDouble

DOUBLE

DOUBLE PRECISION

getDouble

FLOAT

DOUBLE PRECISION

getInt

INTEGER

INTEGER

getBinaryStream

LONGVARBINARY

LONG VARBINARY

getAsciiStream,
getUnicodeStream

LONGVARCHAR

LONG VARCHAR

getBigDecimal

NUMERIC

DECIMAL

getObject

OTHER

Java classes

getFloat

REAL

REAL

getShort

SMALLINT

SMALLINT

getTime

TIME

TIME

getTimestamp

TIMESTAMP

TIMESTAMP

getByte

TINYINT

TINYINT

getBytes

VARBINARY

BIT VARYING

getString

VARCHAR

VARCHAR

Tip Nine. Tune Database Booting/Class Loading

By default, Cloudscape does not boot databases (and some core Cloudscape classes) in the system at Cloudscape startup but only at connection time. For multi-user systems, you may want to reduce connection time by booting one or all databases at startup instead. See cloudscape.system.bootAll and cloudscape.database.noAutoBoot.

For embedded systems, you may want to boot the database in a separate thread (either as part of the startup, or in a connection request).

For more information, see Shielding Users from Cloudscape Class-Loading Events.

Tip Ten. Recompile Stale Stored Prepared Statements

Changes in the layout of data might require a new statement execution plan for old stored prepared statements. Some changes automatically force recompilation--adding or deleting indexes, for example. Other changes do not force recompilation--changes in the amount of data, for example. When a statement plan is no longer appropriate, we call it stale.

An egregious example of when changes in the amount of data make a statement plan go stale: You create a stored prepared statement for a SELECT statement against an empty table that has an index and try to use the same statement when the table has 20,000 rows. When the statement was created, Cloudscape would not have chosen an index as the access path in the statement execution plan. Recompiling the statement allows Cloudscape to choose a better statement execution plan.

Cloudscape automatically checks to see if a statement has gone stale after a given number of executions within a single Cloudscape session (100 by default). Systems in which data changes happen rapidly, or that shut down and restart often, may not benefit from this feature unless you configure the automatic checking to happen more often. For more details, see Stale Plans and Stored Prepared Statements.

Use RunTimeStatistics to see if Cloudscape is using a good statement execution plan. (See Working with RunTimeStatistics for more information.)

Alternately, you can recompile stored prepared statements with the ALTER STATEMENT statement. UPDATES and DELETES may have similar issues.