Remotely assisted instructional learning (RAIL)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price
USD $2,800
CAD $3,080
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.
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
Next Steps
HP NonStop SQL/MX database for SQL/MP users U4185S
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
Printable version
