This course is designed for data professionals who want to master the art and science of writing high-performance SQL. In the rapidly evolving landscape of big data, an engineer's ability to create efficient, scalable, and well-tuned queries is paramount to building robust data pipelines and ensuring a seamless experience for downstream users. This program will elevate your skills from a proficient SQL user to a performance-focused data engineer who can diagnose and resolve complex database bottlenecks, ensuring your data systems remain fast and reliable as data volume grows.

Duration: 10 days

Target Audience

• Data Engineers
• Database Administrators
• Data Analysts and Scientists who write complex SQL queries
• Software Developers who interact with large databases
• Anyone looking to improve their SQL performance and debugging skills

Objectives

• Understand the core components of a database's query optimizer
• Analyze and interpret query execution plans to identify performance bottlenecks
• Apply effective indexing and partitioning strategies to large datasets
• Write efficient and scalable SQL queries, avoiding common performance pitfalls
• Master advanced SQL features like window functions and CTEs for optimized logic
• Implement best practices for database design that support high-performance queries
• Utilize modern tools and techniques for monitoring and tuning database performance

Course Modules

Module 1: The Foundations of SQL Performance

• How databases process and execute queries
• The role of the query optimizer in performance
• Understanding and using basic database statistics
• The impact of data volume and schema design on query speed
• Introduction to the cost-based optimizer

Module 2: Analyzing Query Execution Plans

• Generating and reading execution plans with EXPLAIN and similar commands
• Identifying costly operations like full table scans and nested loops
• Understanding the flow and hierarchy of a query plan
• Using execution plans to compare and evaluate different query approaches
• Tools for visualizing and interpreting complex plans

Module 3: Indexing Strategies for Optimization

• The fundamental concept of database indexes and their internal structure
• Creating and managing different types of indexes (e.g., clustered, non-clustered, covering)
• Choosing the right columns to index for your specific query patterns
• Best practices for index maintenance and avoiding index bloat
• The trade-offs of using indexes (read performance vs. write overhead)

Module 4: Writing Efficient WHERE and JOIN Clauses

• Techniques for writing "Sargable" queries that utilize indexes
• Avoiding common mistakes that disable index usage
• Optimizing different types of JOIN operations (e.g., INNER JOIN vs. OUTER JOIN)
• Structuring joins to connect smaller tables to larger tables first
• The performance implications of OR conditions and NOT IN

Module 5: Optimizing Aggregation and Grouping

• Best practices for using GROUP BY with large datasets
• The performance difference between GROUP BY and DISTINCT
• Using HAVING effectively versus filtering with the WHERE clause
• Techniques for handling large aggregations and intermediate results
• The impact of ORDER BY on performance

Module 6: Advanced Filtering and Set Operations

• The performance difference between UNION and UNION ALL
• Using EXISTS and NOT EXISTS as alternatives to IN and NOT IN
• Leveraging CASE statements and conditional logic for efficient data transformation
• Optimizing queries with subqueries and their potential performance issues
• Breaking down complex queries into smaller, more manageable parts

Module 7: Working with Temporary Data

• The use cases and performance characteristics of temporary tables
• Using Common Table Expressions (CTEs) for readability and performance
• The distinction between persistent and session-based temporary data
• Best practices for managing temporary data to minimize I/O and memory usage
• Advanced use cases for recursive CTEs

Module 8: Window Functions and Analytical Queries

• The power of window functions for complex calculations without aggregation
• Using PARTITION BY and ORDER BY to define a window frame
• Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK()
• Performing running totals, moving averages, and other time-series analysis
• Optimizing window functions for large partitions

Module 9: Database Partitioning and Sharding

• The concept of data partitioning to improve query performance and management
• Different types of partitioning (e.g., range, list, hash)
• Strategies for partitioning large tables by date, ID, or other criteria
• The role of sharding in distributing data across multiple servers
• Best practices for managing partitioned data and ensuring query efficiency

Module 10: Optimizing Database Schema and Design

• The relationship between normalization, denormalization, and query performance
• Deciding when to use star schemas or snowflake schemas
• The importance of choosing the correct data types for each column
• Managing foreign keys and ensuring referential integrity without performance hits
• Archiving or purging old data to keep active tables lean

Module 11: Stored Procedures and Functions

• The performance benefits of using stored procedures
• Creating and managing user-defined functions
• The performance implications of function calls within a query
• Using prepared statements to reduce parsing overhead
• Security and efficiency considerations for stored procedures

Module 12: Concurrency and Locking

• Understanding database locks and their impact on performance
• The different levels of locking (e.g., row-level, page-level, table-level)
• Identifying and resolving deadlocks and lock contention issues
• Strategies for writing queries that minimize locking duration
• The role of transaction isolation levels

Module 13: Monitoring and Performance Dashboards

• Key metrics to monitor for database health (e.g., CPU, memory, I/O)
• Setting up and using performance monitoring tools
• Creating custom dashboards to track slow-running queries over time
• Using database-specific views (e.g., pg_stat_statements) to find bottlenecks
• Alerting and automated performance checks

Module 14: Practical Application and Case Studies

• Hands-on exercises to apply learned optimization techniques
• Analyzing and refactoring a series of inefficient queries
• Diagnosing a real-world database performance problem from a case study
• Working with a large, simulated dataset to test optimization strategies
• Evaluating and documenting performance improvements

Module 15: Advanced Tuning and Final Project

• The role of database configuration settings in performance
• Using query hints for specific tuning scenarios
• The impact of hardware (CPU, RAM, SSDs) on database performance
• A final project where students must optimize a slow query on a complex database schema
• Presenting the optimization strategy and the results of the final project

Training Approach

This course will be delivered by our skilled trainers who have vast knowledge and experience as expert professionals in the fields. The course is taught in English and through a mix of theory, practical activities, group discussion and case studies. Course manuals and additional training materials will be provided to the participants upon completion of the training.

Tailor-Made Course

This course can also be tailor-made to meet organization requirement. For further inquiries, please contact us on: Email: info@skillsforafrica.org, training@skillsforafrica.org Tel: +254 702 249 449

Training Venue

The training will be held at our Skills for Africa Training Institute Training Centre. We also offer training for a group at requested location all over the world. The course fee covers the course tuition, training materials, two break refreshments, and buffet lunch.

Visa application, travel expenses, airport transfers, dinners, accommodation, insurance, and other personal expenses are catered by the participant

Certification

Participants will be issued with Skills for Africa Training Institute certificate upon completion of this course.

Airport Pickup and Accommodation

Airport pickup and accommodation is arranged upon request. For booking contact our Training Coordinator through Email: info@skillsforafrica.org, training@skillsforafrica.org Tel: +254 702 249 449

Terms of Payment: Unless otherwise agreed between the two parties’ payment of the course fee should be done 7 working days before commencement of the training.