Advanced SQL & Performance for Database Applications

Why take this course?

After completing this course, you will be able to
  • describe the major factors contributing to performance.
  • assess the performance impact of physical database design decisions.
  • recognize how application design affects performance.
  • understand how concurrency impacts performance.
  • code complex SQL statements involving data conversion, summarization and multiple table access.
  • judge the efficiency of SQL statements and tune ineffective queries.
  • understand how the Optimizer works and gain in-depth knowledge of access paths.
  • use the Explain facility, including Optimizer Hints.
  • use utilities as part of the performance tuning process.
5 Days : Fee - $ 2,947.00 (CAD) : ? Multi-seat - $ 1,997.00 (CAD)
Mon Apr 11, 2016
Ottawa
Contact us to arrange your preferred location and date
This 5-day, hands-on course examines database performance issues from the application programmer's perspective. We'll discuss the factors affecting database performance and give you practical tips and techniques for designing and implementing efficient database applications. You'll also look at advanced SQL and get an in-depth study of efficient coding techniques. Hands-on exercises ensure an effective learning experience.
Who should attend?
Application designers, programmer analysts, programmers and database administrators who design, code, or support database applications.
Any prerequisites?
You should be familiar with the SQL language and application programming, or have equivalent knowledge.

What topics are covered?

Application Tuning
  • Performance Concepts
  • Performance Factors
Performance Tuning
  • Tools and Techniques
  • The EXPLAIN Facility
Database Design for Performance
  • Tablespace/Table Design
  • Tablespace Organisation
  • Normalization
  • Denormalization
  • Data Types
  • Index Design
  • Index Structure
  • Clustering Index
  • Composite Indexes
  • Usage of Indexes
Application Design for Performance
  • On-line Design
  • Dialog Design
  • Batch Design
  • Design Objectives
  • Effective Resource Utilisation
  • Elapsed Time
  • Processing Scenarios
Concurrency Control and Performance
  • Concurrency Control
  • Locking Strategy
  • Lock Size
  • Lock Mode
  • Lock Duration
  • Reducing Contention
Advanced Multi-table Inner and Outer Joins
  • Multi-Table Inner Joins
  • Multi-Table Outer Joins
  • Join Processing
  • Join Guidelines
Advanced Unions, Exists and Intersects
  • Advanced Unions
  • Excepts
  • Intersects
Table Expressions
  • Nested Table Expressions
  • Common Table Expressions
  • Recursive SQL
Advanced Sub-Query Usage and Optimization
  • Type of Sub-Queries
  • Scalar Full Selects
  • List Full Selects
  • Row Expressions
  • Optimization Options
Data Change Tables, Case Expressions and Pivots
  • Data Change Tables
  • Case Expressions
  • Pivot Tables
Access Paths
  • What is an Access Path?
  • Single Index Access
  • Multiple Index Access
  • Join Strategies
  • Nested Loop Join
  • Merge Scan Join
  • Hybrid Join
  • Using Multiple Indexes in Joins
  • Combining Join Methods
The EXPLAIN Facility
  • The Optimizer
  • EXPLAIN Facility
  • DSN_STATEMNT_TABLE
SQL Processing
  • The Optimizer
  • Processing SQL
  • SQL Cost
  • Filter Factor
  • The Catalog
Coding for Performance
  • General SQL Considerations
  • SQL Predicates
  • Index Usage
  • Negating
  • Literals, Variables, Arithmetic Expressions, Scalar Functions
  • LIKE, BETWEEN, IN
  • ANDing,, and Oring Predicates
  • Using Composite Indexes
  • Updating and Index Usage
  • Stage 1 and Stage 2 Predicates
  • Multi Table Access
  • Avoiding Sorts
  • Performance Tips