The Star in Tandem© NonStop Training & Professional Services since 1986
Polaris Learning Solutions Home PagePolaris Learning Solutions Home PageOn-Site CoursesOnline ClassesProfessional ServicesPricing and Payment TermsContact UsAbout UsOur Clients
PLS204 - Improving NonStop SQL/MPô Query Performance
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 days (on-site)

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 exercises.


  • Minimum of 2 years operational experience on Tandem NonStop systems.
  • Minimum 6 months hands-on experience with NonStop SQL/MP Databases.

Web site:



+1 972-898-3206
+1 972-898-3206
+1 972-898-3206

1892 FM 2481
Bluff Dale, TX  76433

  • 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
      • Statistics
      • 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
    • Lab
  • 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
    • Lab
  • 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
    • Lab
  • Module 6 - Predicate Evaluation
    • Predicate classification and transformation
    • Impact of using AND and OR predicates
    • Processing corelated and non-corelated subqueries
    • Multidimensional predicates, MDAM usage
    • Lab
  • Module 7 - Programming Considerations
    • 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
    • Lab
  • Module 8 - Measuring Query Performance
    • 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
    • Lab
For Web site issues, contact Webmaster .   Copyright © 2005-2009 Polaris Learning Solutions   All rights reserved.