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?
|
