Introduction to Tuning SQL for Higher Performance | Udemy

Are your SQL queries taking too long to execute? Do you find yourself wondering how databases decide how to retrieve and filter data? If you’re comfortable writing SQL but want to learn more about how to make your queries more efficient, then this course is for you. This course explains the basics of  SQL performance principles and optimization techniques. Building on your existing SQL knowledge, we’ll look into SQL’s query planner and exploring how to evaluate query performance and choose among different approaches to writing queries.

Query Analysis and Execution Deep Dive

To tune queries, it helps to understand a databases query planner.  In this course you will learn how query plan builders work, including how different types of query plan nodes implement core operations, such as retrieving data, joining tables, and filtering results. Learn to read  execution plans, compare cost calculations, and evaluate alternative implementations of your queries. Through hands-on exercises, you’ll analyze various query patterns and see how rewriting queries can impact their performance.

Advanced Performance Optimization Techniques

The course explores several optimization strategies:

• Indexing techniques including covering, full-text, and expression indexes
• Reviewing join algorithms with real-world scenarios demonstrating when each type is optimal
• Seeing options for optimizing correlated subqueries and complex window functions
• Weighing when to use materialized views and common table expressions (CTEs) for query performance
• Learning about different pattern matching techniques including regular expressions and full-text search strategies
• Becoming familiar with the performance implications of different GiST, GIN, and SP-GiST index types

Implementing Production-Grade SQL Solutions

Tackle enterprise-level scenarios including:

• Implementing efficient table partitioning strategies for large tables
• Understanding time-series optimization techniques for IoT data
• Implementing  full-text search in large-scale applications

Performance Monitoring and Tuning

Learn how to use  tools and techniques for ongoing performance optimization:

• Advanced usage of pg_stat views for performance monitoring
• Understanding and tuning autovacuum for optimal performance
• Maintaining statistics in tables

Throughout the course, you’ll work with datasets that include a basic a sales database as well as a time-series IoT vehicle sensor system generating millions of readings per day. The hands-on exercises give you an opportunity to apply theoretical knowledge to practical application scenarios.

By the end of this course, you’ll have practiced the skills you need to analyze complex SQL queries and the knowledge to make informed decisions about database performance trade-offs in production systems.

Note: This course was formally listed as Hands-On SQL for Performance Tuning