Advanced SQL for Query Tuning and Performance Optimization (2019)

1h 45mAdvanced2019-04-01

Authors

Dan Sullivan

Enterprise Architect, Big Data Expert

Course details

SQL queries can be fast and highly efficient, but they can also be slow and demand excessive CPU and memory resources. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. But by gaining a better understanding of how databases translate SQL queries into execution plans, you can take steps to avoid these issues. In this course, Dan Sullivan shows developers how to analyze query execution plans and use data modeling strategies to boost query performance. Dan describes how SQL queries are executed; highlights different types of indexes and how they factor in query tuning; covers several methods for performing joins; and discusses how to use partitioning and materialized views to improve performance.

Learning objectives
How SQL executes queries
Working with PostgreSQL tools for tuning
Bitmap and hash indexes
Using different types of indexes to improve performance
Challenges with joining tables
When to use partitioning to improve performance
Collecting statistics about data in tables

Skills covered

PostgreSQLSQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development

Concepts

0. Introduction

01 - Reduce query reponse time with query tuning
02 - What you should know

1. How SQL Executes a Query

03 - From declarative SQL to a procedural execution plan
04 - Scanning tables and indexes
05 - Joining tables
06 - Partitioning data

2. PostgreSQL Tools for Tuning

07 - Installing PostgreSQL
08 - Overview of pgAdmin
09 - Explain and analyze
10 - Example plan - Selecting with a WHERE clause
11 - Indexes

3. Types of Indexes

12 - Indexing
13 - B-tree indexes
14 - B-tree index example plan
15 - Bitmap indexes
16 - Bitmap index example plan
17 - Hash indexes
18 - Hash index example plan
19 - PostgreSQL-specific indexes

4. Tuning Joins

20 - What affects joins performance
21 - Nested loops
22 - Nested loop example plan
23 - Hash joins
24 - Hash join example plan
25 - Merge joins
26 - Merge join example
27 - Subqueries vs. joins

5. Partitioning Data

28 - Horizontal vs. vertical partitioning
29 - Partition by range
30 - Partition by range example
31 - Partition by list
32 - Partition by list example
33 - Partition by hash
34 - Partition by hash example

6. Materialized Views

35 - Materialized views
36 - Creating materialized views
37 - Refreshing materialized views

7. Other Optimization Techniques

38 - Collect statistics about data in tables
39 - Hints to the query optimizer
40 - Parallel query execution
41 - Miscellaneous tips

Conclusion

42 - Next steps