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

Over the years, the business world has become more data driven. Key decisions are no longer made on a hunch or a whim, they are firmly based on data.

To become a data driven decision maker, you must constantly extract, filter, manipulate and aggregate data from various data sources. SQL is used by analysts, data scientists, product managers, designers and many others to do just that. Mastering SQL gives you the advantage you need to make accurate and fast decisions.

This course presents real life SQL mistakes from code reviews, SQL reports, and real system code, and offers solutions on how to resolve these errors or avoid them altogether. The course will also cover how to optimize common day to day tasks, including how to correctly manipulate and filter dates, how to aggregate with missing data, how to correctly calculate percents, how to concatenate the results of two queries, how to best utilize existing indexes, and how to make slow queries execute faster. In addition to learning how to spot common mistakes and fix them, the course will review some handy productivity tricks that will enable you to write SQL more efficiently.

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

By the end of this live, hands-on, 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 are a professional who relies on data on a daily basis.
  • You work with PostgreSQL databases. (Most of the course material is relevant to all popular SQL databases, but some tips are specific to PostgreSQL.)
  • 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 the data.
  • You want to write accurate and faster SQL queries.
  • You want to become more productive.
  • You want to become a data driven decision maker.

Prerequisites

  • Basic knowledge of SQL.

Recommended preparation:

Recommended follow-up:

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

Course Overview (15 min)

  • Introductions and Welcome
  • Structure of an SQL query

Writing correct SQL, Part 1 (40 minutes)

  • The naive sales report
  • Guard Against "division by zero" Errors
  • Be Careful When Dividing Integers
  • Be Careful When Counting Nullable Column (engineer a special category called coupons when the price is null)
  • Exercise: Write two summary queries
  • Q&A
  • Break (5 minutes)

Writing correct SQL, Part 2 (30 minutes)

  • Use BETWEEN Only For Inclusive Ranges
  • Be Aware of Timezones
  • Exercise: Write query to find products sold by hour of day
  • Q&A

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

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

Writing faster SQL (35 minutes)

  • Avoid Transformations on Indexed Fields
  • Know the Difference Between UNION and UNION ALL
  • Add "Faux" Predicates
  • Fetch Only What You Need!
  • Q&A

Writing SQL faster (20 minutes)

  • SQL productivity tips
  • Q&A

Conclusion (10 minutes)