Performance for Database Applications

Why take this course?

After completing this course, you will be able to
  • understand the major factors contributing to database application performance.
  • learn how tablespace, table, and index design effect performance.
  • have a comprehensive understanding of how application design affects performance.
  • learn about the impact of concurrency on performance.
  • understand how database optimizers works.
  • gain an in-depth knowledge of database access Paths.
  • become fully conversant in the use of tools such the Explain facility, including the use of Optimizer hints.
  • be able to judge the efficiency of SQL statements and tune ineffective queries.
  • learn to use utilities as part of the performance tuning process.
3 Days : Fee - $ 1,997.00 (CAD) : ? Multi-seat - $ 1,347.00 (CAD)
Wed Apr 13, 2016
Ottawa
Mon Jun 27, 2016
Ottawa
Tue Aug 2, 2016
Ottawa
Contact us to arrange your preferred location and date
This hands-on course examines database performance issues from the application programmer's perspective. The discussion of database performance factors is accompanied by practical tips and techniques for designing and implementing efficient database applications. The SQL language is revisited to study efficient coding techniques. Hands-on exercises ensure an effective learning experience.
Who should attend?
The course is primarily intended for designers, programmer analysts, programmers and administrators who design, code or support applications for database servers such DB2, Oracle, MySQL and SQL Server.
Any prerequisites?
You should be familiar with the SQL language and application programming.

What topics are covered?

Application Tuning
  • Performance Factors
  • Performance Tuning
  • Tools and Techniques
  • The EXPLAIN Facility
Database Design for Performance
  • Tablespace/Table Design
  • Tablespace Organization
  • Normalization
  • Denormalization
  • Data Types
  • Index Design
  • Index Structure
  • Clustering Index
  • Composite Index
  • Usage of Indexes
Application Design for Performance
  • Online 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
Database 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_STATEMENT_TABLE
SQL Processing
  • The Optimizer
  • Processing SQL
  • SQL Cost
  • Filter Factor
  • The Catalog
Coding for Performance Part I
  • General SQL Considerations
  • SQL Predicates
Coding for Performance Part II
  • Multi Table Access
  • Avoiding Sorts
  • Performance Tips