Java DB

Apache Derby

Derby Performance Tuning

Derby Getting Started
Derby Reference Manual
Derby Developer's Guide
Derby Performance Tuning
Derby Server and Admin Guide
Derby Tools and Utilities
Derby Performance Tuning
-Performance tips and tricks

-Use prepared statements with substitution parameters
-Create indexes, and make sure they are being used
-Ensure table statistics are accurate
-Increase the size of the data page cache
-Tune the size of database pages

-Performance trade-offs of large pages

-Avoid expensive queries
-Use the appropriate getXXX and setXXX methods for the type
-Tune database booting/class loading
-Avoid inserts in autocommit mode if possible
-Improve the performance of table functions
-Configure Derby to use an in-memory database

-Shut down the system properly
-Put Derby first in your classpath

-Tuning databases and applications

-Application and database design issues

-Avoiding table scans of large tables

-Always create indexes
-Prevent the user from issuing expensive queries

-Avoiding compiling SQL statements

-Using the statement cache

-Shielding users from Derby class-loading events

-Analyzing statement execution
-Working with RunTimeStatistics

-How you use the RUNTIMESTATISTICS attribute
-How you use the XPLAIN style
-Analyzing the information

-Statistics timing
-Statement execution plan
-Optimizer estimates
-Optimizer overrides
-Understanding XPLAIN style database tables

-DML statements and performance

-Performance and optimization

-Index use and access paths

-What is an index?
-What's optimizable?
-Covering indexes
-Useful indexes can use qualifiers
-When a table scan Is better
-Indexes have a cost for inserts, updates, and deletes

-Joins and performance

-Join order overview
-Join strategies

-Derby's cost-based optimization

-About the optimizer's choice of access path
-About the optimizer's choice of join order
-About the optimizer's choice of join strategy
-About the optimizer's choice of sort avoidance
-About the system's selection of lock granularity
-About the optimizer's selection of bulk fetch

-Locking and performance

-Transaction-based lock escalation
-Locking a table for the duration of a transaction

-Non-cost-based optimizations

-Non-cost-based sort avoidance (tuple filtering)

-DISTINCT
-GROUP BY

-The MIN() and MAX() optimizations

-Overriding the default optimizer behavior

-Selectivity and cardinality statistics

-Determinations of rows scanned from disk for a table scan

-How the optimizer determines the number of rows in a table

-Estimations of rows scanned from disk for an index scan

-Queries with a known search condition
-Queries with an unknown search condition

-Statistics-based versus hard-wired selectivity

-Selectivity from cardinality statistics
-Selectivity from hard-wired assumptions

-What are cardinality statistics?
-Working with cardinality statistics

-When cardinality statistics are automatically updated
-When cardinality statistics go stale

-Internal language transformations

-Predicate transformations

-BETWEEN transformations
-LIKE transformations

-Character string beginning with constant
-Character string without wildcards
-Unknown parameter

-Simple IN predicate transformations
-NOT IN predicate transformations
-OR transformations

-Transitive closure

-Transitive closure on join clauses
-Transitive Closure on Search Clauses

-View transformations

-View flattening
-Predicates pushed into views or derived tables

-Subquery processing and transformations

-Materialization
-Flattening a subquery into a normal join
-Flattening a subquery into an EXISTS join
-Flattening VALUES subqueries
-DISTINCT elimination in IN, ANY, and EXISTS subqueries
-IN/ANY subquery transformation

-Outer join transformations
-Sort avoidance

-DISTINCT elimination based on a uniqueness condition
-Combining ORDER BY and DISTINCT
-Combining ORDER BY and UNION

-Aggregate processing

-

		Search documentation:

Locking and performance Row-level locking improves concurrency in a multi-user system. However, a large number of row locks can degrade performance. About the system's selection of lock granularity discussed the way the optimizer makes some compile-time decisions about escalating row locks to table locks for performance reasons. This section discusses ways in which the Derby system and the user can make similar lock escalations. Transaction-based lock escalation Locking a table for the duration of a transaction Parent topic: DML statements and performance Related concepts Performance and optimization Non-cost-based optimizations Overriding the default optimizer behavior

	javadb@jdbcurl.com