O'Reilly logo
live online training icon Live Online training

SQL Next Steps: Optimization

Getting the most out of your database

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 is a full stack developer and a team leader with over 15 years of experience. He started as a DBA for data warehouse systems in the pharma and logistics industries. In his role as lead DBA he was in charge of designing database schemas, ETL processes and integrate with various BI tools. Haki was then promoted to a role of a team leader where he led ten developers using different technologies such as C++ and C# as well as several DBA’s.

    Haki is currently leading the development of a large ticketing and payment systems. In his current role he is working with large PostgreSQL databases and other technologies such as Python, Django and TypeScript. He also maintains a popular blog where he shares different technical aspects of his work. The blog 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)