Advanced SQL for Query Tuning and Performance Optimization

Go to class
Write Review

Free Online Course: Advanced SQL for Query Tuning and Performance Optimization provided by LinkedIn Learning is a comprehensive online course, which lasts for 1-2 hours worth of material. The course is taught in English and is free of charge. Upon completion of the course, you can receive an e-certificate from LinkedIn Learning. Advanced SQL for Query Tuning and Performance Optimization is taught by Dan Sullivan.

Overview
  • Learn how to analyze query execution plans and use data modeling strategies to boost query performance.

Syllabus
  • Introduction

    • Reduce query reponse time with query tuning
    • What you should know
    1. How SQL Executes a Query
    • From declarative SQL to a procedural execution plan
    • Scanning tables and indexes
    • Joining tables
    • Partitioning data
    2. PostgreSQL Tools for Tuning
    • Installing PostgreSQL
    • Overview of pgAdmin
    • Explain and analyze
    • Example plan: Selecting with a WHERE clause
    • Indexes
    3. Types of Indexes
    • Indexing
    • B-tree indexes
    • B-tree index example plan
    • Bitmap indexes
    • Bitmap index example plan
    • Hash indexes
    • Hash index example plan
    • PostgreSQL-specific indexes
    4. Tuning Joins
    • What affects joins performance?
    • Nested loops
    • Nested loop example plan
    • Hash joins
    • Hash join example plan
    • Merge joins
    • Merge join example
    • Subqueries vs. joins
    5. Partitioning Data
    • Horizontal vs. vertical partitioning
    • Partition by range
    • Partition by range example
    • Partition by list
    • Partition by list example
    • Partition by hash
    • Partition by hash example
    6. Materialized Views
    • Materialized views
    • Creating materialized views
    • Refreshing materialized views
    7. Other Optimization Techniques
    • Collect statistics about data in tables
    • Hints to the query optimizer
    • Parallel query execution
    • Miscellaneous tips
    Conclusion
    • Next steps