Skip to content
O'Reilly home
SQL

SQL Next Steps: Optimization

Published by O'Reilly Media, Inc.

Getting the most out of your database

October 20, 2021

12:00 p.m. - 4:00 p.m. Coordinated Universal Time

This event has ended.

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 live event 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)

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)

Your Instructor

  • Haki Benita

    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.

Start your free 10-day trial

Get started

Want to learn more at events like these?

Get full access to O'Reilly online learning for 10 days—free.

  • checkmark50k+ videos, live online training, learning paths, books, and more.
  • checkmarkBuild playlists of content to share with friends and colleagues.
  • checkmarkLearn anywhere with our iOS and Android apps.
Start Free TrialNo credit card required.