Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

April 20

13:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Description

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

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.