How to think in SQL, a set-based mindset - Kevin Devine

One of the main sticking points for developers when they need to write SQL is thinking about the process the incorrect way. Java, C# and others require procedural thinking for optimization, but SQL optimization requires a different tactic, set-based thinking. In this talk, Kevin Devine, takes you through a number of SQL scenarios that were originally written procedurally and shows you how they were optimized using set-based thinking. We will talk about optimizer decisions like full scan, index fast full scan, index range scan, hash joins, merge joins, nested loops anti semi joins, lazy spool, hash aggregate and more. In addition, we will examine the fallacy of process-oriented thinking for SQL and focus on results-oriented thinking. At the end of this talk, you should be able to look at SQL differently and go home ready to optimize those hard to understand queries.
Copyright © 2015 Stir Trek Conference, Inc.
All rights reserved. No part of this publication may be reproduced, distributed, or transmitted in any form or by any means, including photocopying, recording, or other electronic or mechanical methods, without the prior written permission of the publisher, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law.

[@AnyFactor]

0:00 Meme
1:15 Introduction
1:40 Steps in thinking in SQL
4:35 "What do you want?" vs "How do I get"
5:14 Looping is NOT a SQL concept
6:35 Trigger in Cursor Looping example
8:55 Optimizing the Trigger without using cursor and looping
10:19 Cursors lessons learned
11:22 Static Forward_Only and Fast Forward (Static - Makes copy in memory and run through that)
13:08 - SARG (Search Argument) - Easiest way to fix poor performance; Demo with Cast, Convert
15:44 SQL Index Terminology - Clustered Index Search, Clustered Index Scan, Index Scan, Index Seek, Key Lookup, Table Scan
16:23 Clustered Index Seek/Scan
17:16 Index Seek/Scan (With includes)
18:34 Table Scan (Avoid using it)
20:08 SQL Joins Terminology - Inner Joins, Left and Right Outer Join, Merge Join, Hash Match, Nested Loops Join, Anti/Semi Join( (Left, Right, Inner)
20:32 - Inner Joins, Right/Left Outer Joins
21:30 Merge Join
22:32 Hash Match
22: 55 Nested Loops (Bad)
23:28 Anti/Semi Join( (Left, Right, Inner)
24: 05 Demo on Joins Vs Loop
25:38 SQL Plans Terminology - Rows (Estimated Vs Actual), I/O cost, CPU Cost, Executions, Operator Cost, Row Size, Data Size, Ordered, Index, Output List, Where
25:55 Rows (Estimated Vs Actual), I/O cost, CPU Cost
28:30 Estimated Subtree Cost, Executions, Rebinds and Rewinds, Output List, Where
30:30 Visual Explain - MySQL Workbench
31:35 SQL Thinking (with demo): Results Oriented Vs Process Oriented
36:50 Questions
39:10 Tools for Benchmarking loadtesting?
40:40 Different results because of caching?
41:40 View on using views?

[@Aaron-Gerzzog-Berger]

Amazing! My mind is blown!