This course is a solid and complete introduction to improving query performance in
the NonStop SQL/MPô product. The student will learn how to evaluate SQL/MP query costs and
understand the impact of different query designs, JOIN techniques, and how to improve overall
query performance. The student will also be introduced to the fundamentals of the
NonStop database infrastructure to better understand the functions of DP2, and how
different database types are optimized.
A NonStop SQL/MP programmer, database administrator or a NonStop system manager will learn various
topics that can improve and optimize query performance.
Extensive hands-on labs will anchor
the student's classroom learning.
Course Duration: 5
Those SQL Programmers, DBAs, and System Managers who need to learn how to optimize
SQL/MP queries, understand how to measure and evaulate their queries, and to
understand the implications of selecting certain table types for their databases.
- Identify the areas of system and query perfromance
- Learn the features of the three database types on the NonStop Tandem platform, and
which features are optimal in which environments.
- Understanding the SQL runtime environment, program invalidation, and SQL compiling options.
- Learning how to evaluate EXPLAIN PLANs.
- Learning the aspects of embedding SQL/MP into programs, about host variables, INVOKE statments and
using datetime variables.
- Learning about row and table locks, and how to control them.
- Learning optimal programming techniques, and how to measure query performance.
- This course includes many comprehensive and instructional lab
- Minimum of 2 years operational experience on Tandem NonStop systems.
- Minimum 6 months hands-on experience with NonStop SQL/MP Databases.
Module 6 - Predicate Evaluation
- Module 1 - Areas of Query Performance
- Introduction to the topic of performance
- Macro to Micro: Areas of performance improvement
- Areas within scope of this course
- Module 2 - Overview of Database Infrastructure
- Standard Tandem structured files, description and characteristics
- Overview of cache use, buffering and DP2 features
- Characteristics of optimal use of given structured file types
- Characteristics of improper use of given structured file types
- Impact of database structure on query performance
- Impact of using and overusing alternate indexes
- Overview of adding columns
- Demos of decisions made, discussion
- Module 3 - The SQL Environment
- Examination of dynamic database reconfiguration
- Query Optimization
- Access paths
- Row Selectivity
- JOIN and ORDER BY impacts
- Buffering, Locking
- Parallel Execution
- Use of SQLCOMP directives, CHECK options
- Use and rules for Similarity Checks
- Program invalidation causes, and options to prevent and to handle
- Impact of automatic recompilation
- Explain Plans
- Module 4 - Locking
- Database and referential integrity
- Lock Philosophy
- COncept of Referential Integrity
- Row vs. table Locking
- locking strategies
- Exclusion rules and Exclusion modes
- Automatic lock escalations
- Use of FUP LISTLOCKS
- Deadlock concerns, resolution, and prevention
- Module 5 - Query Design
Impact of JOIN techniques
- Extended discussion of types of JOINs
- Join impacts
- Use of nested queries
- Use of temporary tables
- Use of indexes
- SQL's Join strategies, hash, sort-merge, nested, others
- Relate locking strategies to query design choices
- use of LIKE, ORDER BY, Revisit subqueries
Module 7 - Programming Considerations
- Predicate classification and transformation
- Impact of using AND and OR predicates
- Processing corelated and non-corelated subqueries
- Multidimensional predicates, MDAM usage
Module 8 - Measuring Query Performance
- Use of CONTROL directives, options and effects
- Use of WHENEVER directives
- Row positioning within queries, use of cursors vs. use of concatenated keys and other techniques to position within databases
- Referential integrity techniques
- Impact of VARCHAR and NULL columns, DISTINCT, and aggregation revisited
- Techniques to evaluate and monitor query performance
- Improving query performance in Pathway environments
- Query design issues for batch processing, adding and dropping indexes
- Handling long-running queries, locking issues, error recovery and restartability