SQL for Application Developers

09:00 – 10:15

Replace application loops with single queries

thinking in sets, round trips counted

Takeaway Three application loops rewritten as one query each, with the round trips measured before and after.

10:30 – 11:45

Join five tables and get the right row count

join types, duplicate blow-ups, anti-joins

Takeaway A five-table report that returns the correct count, kept beside the plausible version that silently duplicated rows.

12:00 – 13:00

Answer four questions with one query

GROUP BY done properly, conditional aggregation, HAVING

Takeaway One query producing a breakdown that previously took four passes over the table.

14:00 – 15:15

Use the three window functions that come up constantly

ranking, running totals, month-on-month change

Takeaway Top three per category, a running balance and a month-on-month change: written, not copied.

15:30 – 17:00

Read an execution plan out loud

EXPLAIN ANALYZE, scans vs seeks, where the time goes

Takeaway A slow query profiled and explained aloud: you name the problem node before touching a line of SQL.

09:00 – 10:15

Take a nine-second query under fifty milliseconds

composite indexes, column order, covering indexes

Takeaway One correctly ordered composite index, and the before-and-after plans to prove it.

10:30 – 11:45

Fix the five queries that ignore a good index

functions on columns, implicit casts, leading wildcards

Takeaway Five index-defeating queries diagnosed and fixed by changing the query rather than adding indexes.

12:00 – 13:00

Prevent a lost update three different ways

transactions and isolation, row locks, optimistic locking

Takeaway A lost update reproduced across two live sessions, then prevented three ways with the trade-offs of each.

14:00 – 15:15

Cut an N+1 from two hundred queries to two

N+1 in ORMs, parameterised queries, SQL injection, live

Takeaway An N+1 collapsed to two queries, and a live injection against the unparameterised version to end the argument.

15:30 – 17:00

Paginate a ten-million-row table without slowing down

offset vs keyset pagination, ordering guarantees

Takeaway Keyset pagination replacing OFFSET on ten million rows, timed at page one and page fifty thousand.