At a glance
View schedule & enroll Sorted by: location or date
Course number U4183S
Length 4 days
Delivery method Virtual Instructor-Led Training (VILT)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price USD $3,200
CAD $3,520
*Courses are supported in the delivery formats above, but are not necessarily scheduled in every delivery format listed. Please click the schedule links at the top of the page to see which delivery formats are currently scheduled.

Course overview

Enhance your query writing skills by learning how to design complex and efficient queries to access HP NonStop SQL/MP databases in this 4-day class. Learn the different query analysis and tuning techniques. Become familiar with tools to optimize query performance, influence the optimizer's choice of query execution plans, and test queries before they go into production. This course is applicable for both online transaction processing (OLTP) and decision support solutions (DSS) query programmers.


  • NonStop SQL/MP essentials course U4181S
  • NonStop SQL/MP experience in a production environment


Anyone who has responsibilities for query design and programming or query performance and tuning.

Ways to save

Benefits to you

  • HP NonStop SQL/MP features and techniques for designing complex queries to meet query performance criteria
  • HP NonStop SQL/MP query processing architecture and SQL optimizer functions
  • HP NonStop SQL/MP performance analysis tools and the EXPLAIN utility
  • Query performance improvement through efficient query design and environmental control

Course outline

  • Query writing process
    • Analysis and design
    • Implementation
    • Assessment
    • NonStop server tools
    • Lab Exercise
    • Apply the query writing process to a simple query using the available NonStop server tools
  • Basic query component overview
    • Functions and syntax of SQL Data Manipulation Language (DML) statements
    • Overview of query components for restricting row selections, predicates, aggregate functions, grouping functions, and handling SQL null values
    • Lab Exercise
    • Design simple queries for a given set of problem scenarios
  • Advanced query writing
    • Generating joins and subqueries to access data from multiple tables
    • Specifying the use of multivalued predicates in queries
    • Manipulating string data with NonStop SQL/MP string functions
    • The use of the CASE operator in a query
    • Manipulating SQL DATETIME and INTERVAL data types
    • Designing a query for concurrent access
    • Lab Exercise
    • Generate queries to solve complex business problems
  • Performance considerations in query design
    • The importance of integrating performance design issues in the query writing process
    • Tools that can be used for performance planning and assessment
    • Functions of these tools
    • Lab Exercise
    • Become familiar with the physical implementation of the training database
  • Query processing using NonStop SQL/MP
    • Function of the Nonstop SQL/MP query processing components
    • Query optimization process
    • NonStop SQL/MP DSS performance enhancement features
    • Lab Exercise
    • Become familiar with the query performance monitoring tools: EXPLAIN, DISPLAY STATISTICS, and Measure
  • NonStop SQL/MP optimizer functions
    • How the SQL optimizer transforms key predicates and restructures queries
    • Differentiating the three types of selectivity: predicate, index, and table; how the optimizer determines these selectivities
    • How the SQL optimizer determines and selects the appropriate join and sort strategies, computes the cost for a query, and selects the optimal query execution plan for the query
    • How to use the QEP information to predict the performance level of a query
    • Lab Exercise
    • Interpret the information for various EXPLAIN PLAN reports and evaluate the performance levels of the queries
  • Improving query performance through query design
    • Using efficient predicates in queries to improve performance
    • Designing efficient join queries
    • Improving query performance by selecting appropriate columns for faster data access, minimizing costs for sort operations, and using index-only scan
    • Lab Exercise
    • Construct queries for a given set of problem scenarios and apply performance-enhancement features to improve query performance
  • Improving query performance through environment control
    • CONTROL statements that influence the optimizer's decision to use an index
    • CONTROL statements that influence the optimizer to consider parallel processing
    • CONTROL statements that affect table processing
    • The compilation option that does not invalidate a program
    • Lab Exercise
    • Experiment with different CONTROL statements to study the effects on query performance
  • Query design for DSS and OLTP environment
    • Database design characteristics of DSS and OLTP databases
    • Performance requirements for both DSS and OLTP applications
    • Query design characteristics of DSS and OLTP environment
    • General guidelines for designing queries for DSS and OLTP environments
  • Onsite-delivery equipment requirements
    • One or two NonStop servers, CLX or above
    • One or two NonStop servers, CLX or above
    • Minimum of four disks, 200 MB of memory space for class databases
    • NonStop Kernel operating system version D20, D30, or later for NonStop K-series servers, or any Gxx version for NonStop S-series servers
    • One 6530 or equivalent character-based terminal per student
    • One 6530 or equivalent character-based terminal for the instructor
    • NonStop TM/MP and NonStop SQL/MP (release 2 or later) software
    • A 132-character line printer