SQL Next Steps: Optimization
Published by O'Reilly Media, Inc.
Getting the most out of your database
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.
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
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:
- Read “Understanding Data Types,” “Extracting Information by Grouping and Summarizing,” and “Working with Dates and Times” (chapters 3, 8, and 11 in Practical SQL)
Recommended follow-up:
- Finish Practical SQL, focusing on chapters 6, 7, 12, and 15 (book)
Schedule
The time frames 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)
- A 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 summary queries
- Q&A
- Break (5 minutes)
Writing correct SQL: Part 2 (55 minutes)
- Working with timestamps in SQL
- Getting familiar with time zones, daylight savings and timestamp calculation methods
- Hands-on exercise: Write a query to find products by time
- Q&A
- Break (5 minutes)
Writing faster SQL (50 minutes)
- The path of a query: how a query is being processed by the database
- Understanding how a cost based query optimizer works
- Produce and read execution plans
- Understanding cardinality estimates and how they are used by the database
- Ways to influence execution plan
- 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 at Pcentra, 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 DBAs. Haki maintains a popular blog, where he shares the technical aspects of his work, with a focus on optimizing large databases and the challenges of scaling a web app to millions of users.