Skip to Main Content

Speeding up scalar subqueries

Scalar subqueries are an easy way to add aggregations (count, sum, etc.) to queries. But adding lots of these to one statement like this: select … ( select count ( … ) … ), ( select avg ( … ) … ), ( select max ( … ) … ) from … Can make your SQL significantly slower. But there's an alternative that can make this much faster: CROSS APPLY! Watch this session to learn the ins-and-outs of scalar subqueries, how cross apply can be better, and why scalar subqueries can be the fastest solution in some cases. Highlights include: 03:50 - What is a scalar subquery 05:50 - Slow running scalar subquery demo 07:10 - Why scalar subqueries are slow 09:30 - Rewriting scalar subqueries as joins 11:35 - Challenges with turning scalar subqueries to joins 14:20 - Cross apply joins 16:20 - Convert scalar subqueries to cross apply 19:00 - Demo of cross apply examples 24:30 - Demo of converting scalar subqueries to cross apply 35:35 - Advantages of using scalar subqueries to scalar subquery caching 39:00 - Demo of optimizer rewrite of scalar subqueries to joins 41:30 - Demo of scalar subquery caching 50:10 - Demo of optimizing function calls in the where clause by using scalar subquery caching 52:40 - Scalar subquery performance scale 54:15 - Recap of SSQ vs joins vs cross apply

Resources

Workshop Info

Session Has Completed - 20 April 2021
1 Hour
Write Great SQL Office Hours
English
SQL

Other Upcoming Sessions