Modern T SQL for Better Performance
summary
Shows specific replacements for common slow patterns: use LAG/LEAD window functions instead of self-joins or APPLY to compare adjacent rows (700k rows runs in ~9s vs minutes for naive joins), use the ROW_NUMBER trick to detect islands and gaps, replace manual month math with EOMONTH vs date math (example: 8M rows down from 12s to 5s; 31M rows runs ~19s), prefer windowed running totals with OVER and a frame for subscription counts (scaled to 1M rows), note Adaptive Join (batch mode) flips nested loop to hash on columnstore but requires Enterprise and a columnstore index.
AI-written summary · about
chapters (11)
- 0:00Introduction
- 1:03Agenda
- 2:55Cursors
- 4:31Stock Market Problem
- 5:29Stock Market Demo
- 10:47Lead
- 11:08Subscription Problem
- 12:13Example
- 29:20Learning T SQL
- 33:53The Solution
- 41:43Intelligent Query Processing
related talks
46mT-SQL Window Function Performance
34mCommon Table Expressions and Window Functions simple, maintainable, fast queries
01h 00mComplex Queries with Modern SQL
41mModern SQL: Evolution of a dinosaur
01h 00m7 Tips to Performance Tuning, Optimization, and Everything
52mWriting T-SQL code for the engine, not for you "more demos" by popular demand
32mWindowing Functions for Database Analytics
50mJOIN "My Session" ON Modern = 'SQL'