![]() |
Performance Tips and Tricks
|
Reference Manual |
The Top Ten Tips
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 JITNot 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:
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. java -nojit COM.cloudscape.tools.cview 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 StatementsYou 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 UsedBy 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 DataWhen 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 CacheYou 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.
Tip Six. Tune the Size of Database PagesStick with 4K as the page size (the default, and the size operating systems use) unless:
You may need to experiment with page size to find out what works best for your application and database. Performance Trade-Offs of Large PagesUsing 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
When Large Page Size Is Not Desirable
Tip Seven. Avoid Expensive QueriesSome 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 TypeJDBC 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. Tip Nine. Tune Database Booting/Class LoadingBy 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 StatementsChanges 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |