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:

COUNT(nonNullableColumn) Derby transforms COUNT(nonNullableColumn) into COUNT(). This improves performance by potentially reducing the number of referenced columns in the table (each referenced column needs to be read in for each row) and by giving the optimizer more access path choices. For example, the cheapest access path for SELECT COUNT() FROM t1 is the index on t1 with the smallest number of leaf pages, and the optimizer is free to choose that path. Parent topic:** Aggregate processing

	javadb@jdbcurl.com