Tuning Derby
Version 10.3
Derby Document build:
August 1, 2007, 6:50:00 AM (PDT)
Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
• | Working with Derby properties An overview of how you set properties. | |
• | Performance tips and tricks Quick tips on how to improve the performance of Derby applications. | |
• | Tuning databases and applications A more in-depth discussion of how to improve the performance of Derby applications. | |
• | DML statements and performance An in-depth study of how Derby executes queries, how the optimizer works,
and how to tune query execution. | |
• | ||
• | Internal language transformations Reference on how Derby internally transforms some SQL statements
for performance reasons. Not of interest to the general user. |
• | Whether to authorize users | |
• | Page size of tables and indexes | |
• | Where and whether to create an error log | |
• | Which databases in the system to boot |
• | system-wide Most properties can be set on
a system-wide basis; that is, you set a property for
the entire system and all its databases and conglomerates, if this is applicable.
Some properties, such as error handling and automatic booting, can be configured
only in this way, since they apply to the entire system. (For information
about the Derby system, see the Derby Developer's Guide.) When
you change these properties, they affect any tables or indexes created after this change. | |
• | database-wide Some properties can also be
set on a database-wide basis. That is, the property
is true for the selected database only and not for the other databases in
the system unless it is set individually within each of them. When you
change these properties, they affect any tables or indexes created after this change. | |
• | conglomerate-specific Beginning with Derby
properties relating to conglomerates cannot be specified as part of the create-statement
for the object. These properties (for example, derby.storage.pageSize) must be set at the database level prior to executing the create-statement.
These storage-related properties take effect when you create a table or index,
and cannot be changed during the lifetime of the conglomerate |
• | That value is changed | |
• | The file is removed from the system | |
• | The database is booted outside of that system |
1.
| System-wide properties set programmatically (as a command-line option
to the JVM when starting the application or within application code) | |
2.
| Database-wide properties | |
3.
| System-wide properties set in the derby.properties file |
• | IBM Application Developer Kits With the IBM®
SDK, you set JVM system properties by using a -D flag
on the Java command line. For example:
|
Properties p = System.getProperties(); p.put("derby.system.home", "C:\databases\sample");
• | Provide this file | |
• | Automatically create this file for you | |
• | Automatically write any properties or values to this file |
derby.infolog.append=true derby.storage.pageSize=8192 derby.storage.pageReservedSpace=60
Properties sprops = System.getProperties(); System.out.println("derby.storage.pageSize value: " + sprops.getProperty("derby.storage.pageSize"));
Type of property | How you set it | ||||||
System-wide |
| ||||||
Database-wide | Using system procedures and functions in an SQL statement |
• | As database-wide properties | |
• | As system-wide properties via a Properties object
in the application in which the Derby engine is embedded |
• | derby.storage.pageSize = 8192 |
java -Dderby.system.home=c:\system_directory MyApp
CREATE TABLE table1 (a INT, b VARCHAR(10))
The page size for the anothertable table will be 4096 bytes.java -Dderby.system.home=c:\system_directory -Dderby.storage.pageSize=4096 MyApp CREATE TABLE anothertable (a INT, b VARCHAR(10))
CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); cs.setString(1, "derby.storage.pageSize"); cs.setString(2, "32768"); cs.execute(); cs.close();
The page size for the table2 table is 32768 bytes.CREATE TABLE table2 (a INT, b VARCHAR(10))
java -Dderby.system.home=c:\system_directory MyApp
CREATE TABLE table4 (a INT, b VARCHAR(10))
• | Use prepared statements with substitution parameters to save
on costly compilation time. Prepared statements using substitution parameters
significantly improves performance in applications using standard statements. | |
• | Create indexes, and make sure they are being used. Indexes
speed up queries dramatically if the table is much larger than the number
of rows retrieved. | |
• | Increase the size of the data page cache and prime
all the caches. | |
• | 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 Derby. | |
• | ||
• | ||
• | Tune database booting/class loading. System
startup time can be improved by reducing the number of databases in the system
directory. | |
• | Avoid inserts in autocommit mode if possible. Speed
up insert performance. |
• | 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 database-specific Statements (including PreparedStatements). You
can prime this cache by preparing common queries ahead of time in a separate
thread. |
• | 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. | |
• | For read-only applications, use a large page size (for example, 32K) with
a pageReservedSpace of 0. |
derby.storage.pageSize=8192
• | Selective Queries If your application's
queries are very selective and use an index, large page size does not provide
much benefit and potentially degrades performance because a larger page takes
longer to read. |
• | Limited memory Large database pages reduce
I/O time because Derby can access more data with fewer I/Os. However,
large pages require more memory. Derby allocates a bulk number of database
pages in its page cache by default. If the page size is large, the system
might 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:
| |
• | Limited disk space If you cannot afford the
overhead of the minimum two pages per table, keep your page sizes small. |
SELECT DISTINCT nonIndexedCol FROM HugeTable SELECT * FROM HugeTable ORDER BY nonIndexedColumn
Recommended getXXX Method | java.sql.Types | SQL types |
getLong | BIGINT | BIGINT |
getBytes | BINARY | CHAR FOR BIT DATA |
getBlob | BLOB | BLOB |
getString | CHAR | CHAR |
getClob | CLOB | CLOB |
getDate | DATE | DATE |
getBigDecimal | DECIMAL | DECIMAL |
getDouble | DOUBLE | DOUBLE PRECISION |
getDouble | FLOAT | DOUBLE PRECISION |
getInt | INTEGER | INTEGER |
getBinaryStream | LONGVARBINARY | LONG VARCHAR FOR BIT DATA |
getAsciiStream, getUnicodeStream | LONGVARCHAR | LONG VARCHAR |
getBigDecimal | NUMERIC | DECIMAL |
getFloat | REAL | REAL |
getShort | SMALLINT | SMALLINT |
getTime | TIME | TIME |
getTimestamp | TIMESTAMP | TIMESTAMP |
getBytes | VARBINARY | VARCHAR FOR BIT DATA |
getString | VARCHAR | VARCHAR |
None supported. You must use XMLSERIALIZE and then the
corresponding getXXX method. | SQLXML | XML |
• | Run in autocommit false mode, execute a number of inserts in one transaction,
and then explicitly issue a commit. | |
• | If your application allows an initial load into the table, you can use
the import procedures to insert data into a table. Derby will
not log the individual inserts when loading into an empty table using these
interfaces. See the Derby Tools and Utilities Guide Guide for
more information on the import procedures. |
• | When you define a primary key, unique, or foreign key constraint on a
table. See "CONSTRAINT clause" in the Derby Reference Manual for
more information. | |
• | When you explicitly create an index on a table with a CREATE INDEX statement. |
• | If all the data requested are in the index, Derby does
not have to go to the table at all. (See Covering indexes.) | |
• | For operations that require a sort (ORDER BY), if Derby uses
the index to retrieve the data, it does not have to perform a separate sorting
step for some of these operations in some situations. (See About the optimizer's choice of sort avoidance.) |
select ht.hotel_id, ha.stay_date, ht.depart_time from hotels ht, Hotelavailability ha where ht.hotel_id = ha.hotel_id and ht.room_number = ha.room_number and ht.bed_type = 'KING' and ht.smoking_room = 'NO' order by ha.stay_date
-- would benefit from an index like this: -- CREATE INDEX c_id_desc ON Citites(city_id DESC) SELECT * FROM Cities ORDER BY city_id DESC -- would benefit from an index like this: -- CREATE INDEX f_miles_desc on Flights(miles DESC) SELECT MAX(miles) FROM Flight -- would benefit from an index like this: -- CREATE INDEX arrival_time_desc ON Flights(dest_airport, arrive_time DESC) SELECT * FROM Flights WHERE dest_airport = 'LAX' ORDER BY ARRIVAL DESC