1

Example:

UPDATE t
SET Amount = applied.MaxAmount
FROM @test t
CROSS APPLY
(
    SELECT MAX(t2.Amount) AS MaxAmount
    FROM @test t2
    WHERE t2.Id = t.ForeignId
) AS applied

vs

UPDATE t
SET Amount = applied.MaxAmount
FROM @test t
OUTER APPLY
(
    SELECT MAX(t2.Amount) AS MaxAmount
    FROM @test t2
    WHERE t2.Id = t.ForeignId
) AS applied

It's my understanding that these two scripts will accomplish the same thing since the MAX() function will always return a result even if that result is NULL. But the OUTER APPLY is much slower than the CROSS APPLY when I apply it to a large dataset.

I would like to know why CROSS APPLY is so much faster. Is it just using a more efficient algorithm?

Kyle
  • 63
  • 1
  • 7
  • Have you looked at the execution plans? I could imagine that `outer apply` uses a different plan; I'm surprise there is a large difference (can you quantify it?). – Gordon Linoff Jan 26 '18 at 15:11
  • 1
    https://stackoverflow.com/questions/6726696/cross-apply-vs-outer-apply-speed-difference – Slideroh Jan 26 '18 at 15:12
  • @GordonLinoff Here are the stats from my actual scenario: Using OUTER APPLY: `Table 'Worktable'. Scan count 9218, logical reads 5417660, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'MyTable'. Scan count 4, logical reads 69209, physical reads 2, read-ahead reads 47525, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.` – Kyle Jan 26 '18 at 15:20
  • Using CROSS APPLY: `Table 'MyTable'. Scan count 6, logical reads 116178, physical reads 32, read-ahead reads 40690, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.` There are more stats there but they are 0s and I don't have room in this comment. – Kyle Jan 26 '18 at 15:22
  • @Slideroh That question doesn't actually address the difference in performance at all. Not sure why you posted it. – Kyle Jan 26 '18 at 15:29

0 Answers0