Table of Contents
Page 1 of 1

Table of Contents

About This Document

Purpose of This Document

Audience

How This Document Is Organized

1 Working with Cloudscape Properties

Properties Overview

Scope of Properties

Persistence of Properties

Precedence of Properties

Protection of Database-Wide Properties

Dynamic vs. Static Properties

Ways of Setting Cloudscape Properties

System-Wide Properties

Programmatically

As a Parameter to the JVM Command Line

Using a Properties Object Within an Application or Statement

In the cloudscape.properties File

Verifying System Properties

Database-Wide Properties

Shortcut for Setting Database Properties

Conglomerate-Specific Properties

Verifying Conglomerate-Specific Properties

In a Client/Server Environment

Dynamic or Static Changes to Properties

Properties Case Study

2 Performance Tips and Tricks

The Top Ten Tips

Tip One. Use a Fast JVM with Lots of Memory, and Tune the JIT

Tip Two. Use Stored Prepared Statements and Prepared Statements

Tip Three. Create Indexes, and Make Sure They're Being Used

Tip Four. Tune How You Load Data

Tip Five. Increase the Size of the Data Page Cache

Tip Six. Tune the Size of Database Pages

Performance Trade-Offs of Large Pages

When Large Page Size Does Not Improve Performance

When Large Page Size Is Not Desirable

Tip Seven. Avoid Expensive Queries

Tip Eight. Use the Appropriate getXXX and setXXX Methods for the Type

Tip Nine. Tune Database Booting/Class Loading

Tip Ten. Recompile Stale Stored Prepared Statements

No Longer in the Top Ten, but Worth Listing

Shut Down the System Properly

Put Cloudscape First in Your Class Path

Increase the Statement Cache Size

When Working with Development Tools, Pre-Load DatabaseMetaData Stored Prepared Statements

3 Tuning Databases and Applications

Application and Database Design Issues

Avoiding Table Scans of Large Tables

Index, Index, Index

Create Useful Indexes

Make Sure They Are Being Used, and Rebuild Them

Think About Join Order

Prevent the User from Issuing Expensive Queries

Understand When Statements Go Stale

Overview

Automatic Stale Plan Invalidation

Stale Plans and Stored Prepared Statements

When a Change in Table Makes a Plan Stale

Avoiding Compiling SQL Statements

Across Connections and Sessions

Within a Single Connection

Shielding Users from Cloudscape Class-Loading Events

Tuning Tips for Multi-User Systems

Tuning Tips for Single-User Systems

Analyzing Statement Execution

Storing Prepared Statements to Improve Performance

Creating a Stored Prepared Statement

Executing a Stored Prepared Statement

Additional Benefits for Multiple Connections: The Stored Prepared Statement Cache

Invalid Stored Prepared Statements and Recompilation

Stale Stored Prepared Statements

Working with RunTimeStatistics

Overview

How You Use It

Analyzing the Information

Statistics Timing

Statement Execution Plan

Subset of Statement Execution Plan

Optimizer Estimates

Viewing Runtime Statistics in Cloudview

4 DML Statements and Performance

Performance and Optimization

Index Use and Access Paths

What Is an Index?

What's Optimizable?

Directly Optimizable Predicates

Indirectly Optimizable Predicates

Joins

Covering Indexes

Single-Column Index Examples

Multiple-Column Index Example

Useful Indexes Can Use Qualifiers

When a Table Scan Is Better

Indexes Have a Cost for Inserts, Updates, and Deletes

How Indexes Affect Cursors

Joins and Performance

Join Order Overview

Join Strategies

Cloudscape's Cost-Based Optimization

About the Optimizer's Choice of Access Path

Estimating Row Counts for Unknown Search Values

About the Optimizer's Choice of Join Order

Join Order Case Study

About the Optimizer's Choice of Join Strategy

About the Optimizer's Choice of Sort Avoidance

Cost-Based ORDER BY Sort Avoidance

About the Optimizer's Selection of Lock Granularity

How the Optimizer Makes Its Decision at Compile Time

Lock Escalation Threshold

Runtime Overrides

About the Optimizer's Selection of Bulk Fetch

Overriding the Optimizer

Overriding the Optimizer's Choice of Access Path

Overriding the Optimizer's Choice of Join Order

Overriding the Optimizer's Choice of Join Strategy

Overriding the Optimizer's Choice of Fetch Size

Tuning Lock Granularity

Optimizer Accuracy

Providing Costing Information for VTIs

Locking and Performance

Transaction-Based Lock Escalation

LOCK TABLE Statement

Non-Cost-Based Optimizations

Non-Cost-Based Sort Avoidance (Tuple Filtering)

DISTINCT

Quick DISTINCT Scans

GROUP BY

The MIN() and MAX() Optimizations

5 Cloudscape Properties

Scope of Cloudscape Properties

Cloudscape Properties

cloudscape.authentication.ldap.searchAuthDN

cloudscape.authentication.ldap.searchAuthPW

cloudscape.authentication.ldap.searchBase

cloudscape.authentication.ldap.searchFilter

cloudscape.authentication.provider

cloudscape.authentication.server

cloudscape.connection.requireAuthentication

cloudscape.database.classpath

cloudscape.database.defaultConnectionMode

cloudscape.database.forceDatabaseLock

cloudscape.database.fullAccessUsers

cloudscape.database.noAutoBoot

cloudscape.database.propertiesOnly

cloudscape.database.readOnlyAccessUsers

cloudscape.infolog.append

cloudscape.jdbc.metadataStoredPreparedStatements

cloudscape.language.bulkFetchDefault

cloudscape.language.defaultIsolationLevel

cloudscape.language.logStatementText

cloudscape.language.preloadClasses

cloudscape.language.spsCacheSize

cloudscape.language.stalePlanCheckInterval

cloudscape.language.statementCacheSize

cloudscape.language.triggerMaximumRecursionLevel

cloudscape.locks.deadlockTimeout

cloudscape.locks.deadlockTrace

cloudscape.locks.escalationThreshold

cloudscape.locks.monitor

cloudscape.locks.waitTimeout

cloudscape.service

cloudscape.storage.fileCacheSize

cloudscape.storage.initialPages

cloudscape.storage.minimumRecordSize

cloudscape.storage.pageCacheSize

cloudscape.storage.pageReservedSpace

cloudscape.storage.pageSize

cloudscape.storage.rowLocking

cloudscape.storage.sortBufferMax

cloudscape.storage.tempDirectory

cloudscape.stream.error.field

cloudscape.stream.error.file

cloudscape.stream.error.logSeverityLevel

cloudscape.stream.error.method

cloudscape.system.bootAll

cloudscape.system.home

cloudscape.user.UserName

6 Optimizer Overrides

bulkFetch

constraint

index

joinOrder

joinStrategy

Appendix A Internal Language Transformations

Predicate Transformations

BETWEEN Transformations

LIKE Transformations

Character String Beginning with Constant

Character String Without Wildcards

Unknown Parameter

Static 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

Flatting 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

COUNT(nonNullableColumn)

Index