Table of Contents
Informix Performance Optimization
Overview:
Steps for Optimizing
Optimization Goal: Increase Performance
Setting up a Test Environment
Optimizing the Query: Understand the Requirements
Optimizing the Query: Examine the Schema
Optimizing the Query: Examine the Data
Optimizing the Query: Run, Examine and Modify
Set Explain Output
Set Explain: Example 1
Set Explain: Example 2
Set Explain: Example 3
Set Explain: Example 4
Set Explain: Example 5
Set Explain: Example 6
Set Explain: Example 7
Set Explain: Example 8
Set Explain: Example 8 cont.
Indexing Strategies
Indexing Strategies: B+ Trees
Indexing Strategies: Types of Indexes
Indexing Strategies: Leading Portion of an Index
Indexing Strategies: Guidelines
Indexing Strategies: Benefits vs. Cost
New SQL for Informix Dynamic Server 7.3
New SQL in IDS 7.3
New SQL in IDS 7.3: First N rows
New SQL in IDS 7.3: CASE
New SQL in IDS 7.3: DECODE
New SQL in IDS 7.3: NVL
New SQL in IDS 7.3: DBINFO
Table Scans
Types of Table Scans
Index Scans: Upper and Lower Index Filters
Index Scans: Upper and Lower Index Filters
Index Scans: Upper and Lower Index Filters
Index Scans: Key-Only
Index Scans: Key-First
Table Joins
Joining Tables
Joining Tables: Join Methods
Join Methods: Nested Loop Join
Joining Tables: Table Order
Joining Tables: Table Order Who Cares?
Joining Tables: Table Order What is the best order?
Joining Tables: Table Order What affects the join order?
Optimizer Directives
Optimizer Directives
Optimizer Directives
Optimizer Directives: Syntax
Types of Directives
Types of Directives: Access Methods
Types of Directives: Join Order
Types of Directives: Optimization Goal
Types of Directives: Join Methods
Directives Examples: ORDERED
Directives Examples : INDEX
Directives Examples : INDEX (cont.)
Directives Examples : Errors
Optimization Techniques
Optimization Techniques
Optimization Techniques (Cont.)
Optimization Techniques: Use Composite Indexes
Optimization Techniques: Use Index Filters
Optimization Techniques: Use Index Filters
Optimization Techniques: Use Index Filters
Optimization Techniques: Use Index Filters
Optimization Techniques: Use Index Filters
Optimization Techniques: Key-Only Scans
Optimization Techniques: Indexed Reads for Sorting
Optimization Techniques: Indexed Reads for Sorting
Optimization Techniques: Indexed Reads for Sorting
Optimization Techniques: Indexed Reads for Sorting
Optimization Techniques: Indexed Reads for Sorting
Optimization Techniques: Temporary Tables
Optimization Techniques: Temporary Tables
Optimization Techniques: Using UNION’s
Optimization Techniques: Using UNION’s
Optimization Techniques: Avoid Sequential Scans and Auto Indexes
Optimization Techniques: Drop and Recreate Indexes
Optimization Techniques: Avoid Correlated Sub-queries
Optimization Techniques: Avoid Correlated Sub-queries
Optimization Techniques: Select needed columns vs. Select *
Optimization Techniques: Use Outer Joins
Optimization Techniques: Use Outer Joins
Optimization Techniques: Prepare and Execute
Optimization Techniques: Prepare and Execute
Xtree
PPT Slide
Enter Your Session ID
A More Complex Example
PPT Slide
Correlated Sub-Queries
Correlated Sub-Queries What are they?
Correlated Sub-Queries What are they?
Correlated Sub-Queries What’s wrong with them?
Correlated Sub-queries
Correlated Sub-queries: Normal CSQ
Correlated Sub-queries: Rewritten CSQ
Correlated Sub-queries: CSQ Flattening
Correlated Sub-queries: Predicate Promotion in CSQs
Correlated Sub-queries: Predicate Promotion in CSQs
Correlated Sub-queries: Predicate Promotion in CSQs
Correlated Sub-Queries: First Row/Semi-Join
Correlated Sub-Queries: First Row/Semi-Join
Correlated Sub-Queries: Skip Duplicate
Want to know more?
|