Hands-On High Performance SQL
Tuning Queries, Optimizing Indexes, Partitioning Tables, Enabling Text Search & Optimizing Joins, Subqueries, and CTEs
.jpg "Hands-On High Performance SQL")
Preview this Course
What you'll learn
- Generate and understand query execution plans
- Define indexes and partitioning strategies to improve query performance
- Choose optimal ways to implement business logic using joins, subqueries, and common table expressions (CTEs)
- Monitor database statistics to improve query performance
Description
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 master the art of query optimization, this hands-on course is designed for you. Building on your existing SQL knowledge, we'll dive deep into SQL's query planner, exploring how to write high-performance queries for modern data-intensive applications.
This hands-on course goes beyond basic SQL performance principles to provide comprehensive coverage of advanced optimization techniques. Using real-world sales and Internet of Things (IoT) sensor datasets, you'll learn to tackle performance challenges that commonly arise in production environments.
Query Analysis and Execution Deep Dive
Taking your EXPLAIN skills to the next level, you'll master the intricacies of PostgreSQL's query planner. Learn to decode complex execution plans, understand cost calculations, and evaluate alternative implementations of your queries. Through hands-on exercises, you'll analyze various query patterns and learn to rewrite them for optimal performance.
Advanced Performance Optimization Techniques
The course explores sophisticated optimization strategies:
Advanced indexing techniques including covering, full-text, and expression indexes
Deep dive into join algorithms with real-world scenarios demonstrating when each type is optimal
Techniques for optimizing correlated subqueries and complex window functions
Strategic use of materialized views and common table expressions (CTEs) for query performance
Advanced pattern matching optimization including regular expressions and full-text search strategies
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
Converting complex analytical queries into high-performance solutions
Implementing and optimizing full-text search in large-scale applications
Performance Monitoring and Tuning
Master the tools and techniques for ongoing performance optimization:
Advanced usage of pg_stat views for performance monitoring
Understanding and tuning autovacuum for optimal performance
Strategies for maintaining statistics in large, frequently-updated tables
Using extended statistics for complex multi-column correlations
Throughout the course, you'll work with realistic datasets that mirror common production scenarios, including a sales database and a time-series IoT vehicle sensor system generating millions of readings per day. The hands-on exercises challenge you to optimize increasingly complex queries, teaching you to balance theoretical knowledge with practical constraints.
The course concludes with advanced troubleshooting techniques and a framework for systematic query optimization, ensuring you can tackle performance challenges in any database environment. By the end, you'll have the skills to optimize complex SQL queries and the knowledge to make informed decisions about database performance trade-offs in production systems.
Who this course is for:
- Data analysts
- Report writers
- Developers working with relational databases
- Database administrators
- Data Engineers
- ETL developers
- Data modelers
- Data architects
- Report writers
- Business analysts
- SQL users
- Database users
Post a Comment for "Hands-On High Performance SQL"