NonStop SQL/MP Query Design for Performance - U4183S

Beschreibung

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.

 

expand_more chevron_right Zielgruppe

Ideal candidate for this course:
Anyone who has responsibilities for query design and programming or query performance and tuning. 

    expand_more chevron_right Vorkenntnisse

    The following knowledge is recommended for this seminar:
    • NonStop SQL/MP Essentials course
    • NonStop SQL/MP experience in a production environment 

    expand_more chevron_right Detail-Inhalte

    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 
    online_prediction Virtual Classroom Demo oder Kontakt anfordern

    Kontaktoption wählen

    close
    Chat mit Experten Wir beantworten ihre Fragen direkt im live chat perm_phone_msg Rückrufservice Senden sie uns ihre Anfrage und unser Kundenservice ruft sie an group Beratungstermin Buchen Vereinbaren sie online direkt einen Beratungstermin.

    Terminanfrage

      Durch Angabe Ihrer E-Mail-Adresse und Anklicken des Buttons „Newsletter abonnieren“ erklären Sie sich damit einverstanden, dass ETC Ihnen regelmäßig Informationen zu IT Seminaren und weiteren Trainings- und Weiterbildungsthemen zusendet. Die Einwilligung kann jederzeit bei ETC widerrufen werden.