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

-The tips

-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

-More tips

-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

Java DB

Apache Derby

Statistics-based versus hard-wired selectivity