O'Reilly logo
live online training icon Live Online training

SQL Next Steps: Optimization

Getting the most out of your database

Topic: Data
Haki Benita

Key business decisions are no longer made on a hunch or a whim; they’re firmly based on data. But to become a successful data-driven decision maker, you must constantly extract, filter, manipulate, and aggregate data from various data sources. SQL is the perfect tool to do just that. Mastering SQL gives you the advantage you need to make accurate and fast decisions.

Expert Haki Benita takes you through real-life SQL mistakes from code reviews, SQL reports, and real system code and shares solutions for resolving these errors—or avoiding them altogether. Along the way, you’ll learn how to optimize common day-to-day tasks, including how to correctly manipulate and filter dates, aggregate with missing data, correctly calculate percentages, concatenate the results of two queries, best utilize existing indexes, and make slow queries execute faster. You’ll also pick up some handy productivity tricks to help you write SQL more efficiently.

What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

  • How to avoid common mistakes in SQL
  • How to evaluate and improve the performance of SQL queries

And you’ll be able to:

  • Write correct SQL to produce accurate information
  • Make SQL queries execute faster
  • Be more productive when writing SQL

This training course is for you because...

  • You’re a professional who relies on data on a daily basis.
  • You work with PostgreSQL databases.
  • You don’t have confidence in the data you query from the database.
  • You feel you waste a lot of time fiddling with SQL instead of gaining insights from your data.
  • You want to write faster, more accurate SQL queries.
  • You want to become more productive.
  • You want to become a data-driven decision maker.

Prerequisites

  • Basic knowledge of SQL
  • Familiarity with PostgreSQL (Useful but not required—most of the course material is relevant to all popular SQL databases, but some tips are specific to PostgreSQL.)

Recommended preparation:

Recommended follow-up:

  • Finish Practical SQL, focusing on chapters 6, 7, 12, and 15 (book)

About your instructor

  • Haki Benita leads the development of a large ticketing and payment system, working with large PostgreSQL databases and other technologies such as Python, Django, and TypeScript. A full stack developer and a team leader with over 15 years of experience, he’s been a DBA for data warehouse systems in the pharma and logistics industries; served as a lead DBA in charge of designing database schemas and ETL processes and integrating with various BI tools; and led a team of 10 developers using technologies such as C++ and C# and several DBAs. Haki maintains a popular blog, where he shares the technical aspects of his work. It focuses on optimizing large databases and the challenges of scaling a web app to millions of users.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Introduction (15 minutes)

  • Presentation: The structure of an SQL query

Writing correct SQL: Part 1 (40 minutes)

  • Presentation: The naive sales report; guarding against "division by zero" errors; being careful when dividing integers; being careful when counting a nullable column (engineering a special category called “coupons” when the price is null)
  • Hands-on exercise: Write two summary queries
  • Q&A
  • Break (5 minutes)

Writing correct SQL: Part 2 (30 minutes)

  • Presentation: Using BETWEEN only for inclusive ranges; being aware of time zones
  • Hands-on exercise: Write a query to find products sold by hour of day
  • Q&A

How the database decides how to execute a query (20 minutes)

  • Presentation: The role of the query optimizer; the execution plan
  • Q&A
  • Break (5 minutes)

Writing faster SQL (35 minutes)

  • Presentation: Avoiding transformations on indexed fields; the difference between UNION and UNION ALL; adding "faux" predicates; fetching only what you need
  • Q&A

Writing SQL faster (20 minutes)

  • Presentation: SQL productivity tips
  • Q&A

Wrap-up and Q&A (10 minutes)