NonStop SQL/MP Query Design for Performance

Enhance your query writing skills by learning how to design complex and efficient queries to access HPE 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.

 

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

• NonStop SQL/MP Essentials course
• NonStop SQL/MP experience in a production environment 

Query writing process 

• The 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