Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated 7 years ago

Adaptive Joins, SQL Server 2017

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react.

It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry.

So, here goes nothin’!

Cross

Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s nothing special or different about this plan from the regular Inner Join version.

SELECT u.Id, ca.Score 
  FROM dbo.Users_cx AS u
CROSS APPLY (
  SELECT p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca

SELECT u.Id, ca.Score

FROM dbo.Users_cx AS u

CROSS APPLY (

SELECT p.Score

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-12-11'

) ca

I do have to point out that Cross Apply used to only be implemented as a Nested Loops Join. I learned that many years ago from one of the best articles written about Cross Apply by Paul White. That changed recently — it’s possible to see it implemented with a Hash Join in at least 2016. I’ve seen it crop up in Cross Apply queries without a TOP operator.

It may have been in previous versions, but… Yeah. ColumnStore before 2016.

Anyway, the Cross Apply with a TOP operator does appear to skip the Adaptive Join and favor Parallel Nested Loops, as you can see in this particularly Machanic-y query.

SELECT u.Id, ca.Score 
  FROM dbo.Users_cx AS u
CROSS APPLY (
  SELECT TOP 2147483647 p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-01'
) ca

SELECT u.Id, ca.Score

FROM dbo.Users_cx AS u

CROSS APPLY (

SELECT TOP 2147483647 p.Score

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-12-01'

) ca

Outer

Outer Apply suffers a rather gruesome fate, where neither implementation gets an Adaptive Join.

SELECT u.Id, ca.Score 
  FROM dbo.Users_cx AS u
OUTER APPLY (
  SELECT p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca     

SELECT u.Id, ca.Score 
  FROM dbo.Users_cx AS u
OUTER APPLY (
  SELECT TOP 2147483647 p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca

SELECT u.Id, ca.Score

FROM dbo.Users_cx AS u

OUTER APPLY (

SELECT p.Score

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-12-11'

) ca

SELECT u.Id, ca.Score

FROM dbo.Users_cx AS u

OUTER APPLY (

SELECT TOP 2147483647 p.Score

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-12-11'

) ca

Sad face. I still haven’t gotten anything to show up in my Extended Events session for why Adaptive Joins were skipped for certain queries.

Exists

Exists not only doesn’t get an Adaptive Join, but… MY EXTENDED EVENTS SESSION FINALLY SORT OF TELLS ME WHY!

I mean, it doesn’t make any sense, but it’s there.

Like your alcoholic aunt.

SELECT u.Id
  FROM dbo.Users_cx AS u
WHERE EXISTS (
  SELECT 1
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-01-01'
)

SELECT u.Id

FROM dbo.Users_cx AS u

WHERE EXISTS (

SELECT 1

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-01-01'

)

I’m going to skip showing you a non-Adaptive plan, because you’ve seen enough of those.

Here’s what the Extended Events session shows. The query in the XE session and the query I ran are slightly different because I was testing different permutations (I paid like $7 for that word) to see if it made any difference.

Got that? eajsrUnMatchedOuter.

I’m going to say that the next time a bartender tries to cut me off.

Not Exists

Would you believe that Not Exists gets an Adaptive Join, but Exists doesn’t?

SELECT u.Id
  FROM dbo.Users_cx AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-01'
)

SELECT u.Id

FROM dbo.Users_cx AS u

WHERE NOT EXISTS (

SELECT 1

FROM dbo.Posts AS p

WHERE p.OwnerUserId = u.Id

AND u.LastAccessDate >= '2016-12-01'

)

And… would you believe that there’s a bug in the query plan that makes the lines all woogy?

Sir, I’m going to need you to step out of the vehicle.

Territory

I just love looking at and experimenting with this stuff! I hope you’ve been enjoying reading about it.

Thanks for still doing that!

Building a Faux PaaS, Part 2: Choosing and Testing a Cloud Vendor

What Do You Want to See in a Free Query Store Tool?

6 Comments. Leave new

Koen Verbeeck
May 22, 2017 1:21 am

Please never stop blogging. I really like your sense of humor.
(oh yeah, the blog post was interesting too)

Reply
- Erik Darling
  May 22, 2017 8:16 am
  
  Hahaha, thanks!
  
  Reply
arun
May 22, 2017 9:00 am

Hey Eric, with my brief dealings within this area I think the next phase of Adaptive Query Processing will hopefully use it

Reply
- Erik Darling
  May 22, 2017 9:02 am
  
  Sorry , not following — use what?
  
  Reply
  - Simon
    May 23, 2017 5:24 am
    
    it
    
    Reply
Venkat Sarath
May 22, 2017 11:52 pm

Thanks, Eric for putting all this info together.

Reply