Overview

The Query Performance Tuning course provides an in-depth architectural review of the architecture of Vertica projections, and how they can be configured to provide optimal query performance.

Prerequisites

To be successful in this course, you should have the following prerequisites or knowledge:

  • A basic understanding of UNIX (finding and changing directories, listing directory contents, editing files, etc.)
  • Intermediate understanding of SQL (creating tables, loading data, selecting specific data from a table, common column data types, SQL join functionality, etc.)
  • Familiarity with Vertica's projection definitions (segmentation, encoding, K-safety, etc.)

Delegates will learn how to

  • Optimize your database for your most commonly-run queries
  • Analyze your queries and your environment for potential performance bottlenecks
  • Tune your environment for query execution
  • Manually build and test advanced projection types

Outline

The Vertica Training Environment
• Log in to the training environment
• Review the training environment configuration
• Start and stop a database

Introduction to Database Design
• Identify common query performance issues
• Become familiar with the database environment

Projection Design
• Review the definition of a projection
• Describe the configuration of a projection DDL
• Explain how DDL configuration impacts performance

Query Execution
• Time a query
• Generate and evaluate a query plan
• Profile a query and evaluate the output

Optimizing the Database
• Describe the function of the Database Designer
• Create and deploy a comprehensive projection design
• Create and deploy an incremental projection design

Profile Analysis
• Uniquely identify a profiled query
• Identify the events, operators, and counters for a profiled query

Denormalizing Using Flattened Tables
• Create a flattened table
• Populate the table with default and derived values

Directed Queries
• Create an Optimizer-generated directed query
• Create a custom directed query

Projections with Expressions
• Define a projection containing an expression

Aggregate Projection Types
• Describe the functionality and limitations of live aggregate projections
• Build and test a live aggregate projection
• Describe the functionality and limitations of TopK projections
• Build and test a TopK projection

Resource Management
• Review the resource pool parameters
• Create and troubleshoot a resource pool
• Monitor resource pool activity

Next Steps
• Accessing Micro Focus Software's additional training offerings
• Additional information located in the MyVertica portal