CROSS/OUTER APPLY and Performance

One quite useful technique to be aware of is the use of CROSS or OUTER APPLY with an in-line SQL e.g.

1
2
3
4
5
6
7
8
9

INSERT INTO {TABLE}
 
SELECT A.{FIELDS},APP.{FIELDS}
 
FROM {TableA} A CROSS/OUTER APPLY (
 
SELECT {more FIELDS} FROM {TableB} B WHERE .. A.{some_field} = B.{some_field}
 
) APP

However, with CROSS APPLY, the SQL Server optimizer tends to change the logic into regular joins. Mostly this is a good thing, but not if you want to “force” the driving table to be {TableA}. This can offer a considerable performance boost in some circumstances.

What you can do to force this behaviour is to add TOP 1 to the inner SELECT – assuming you expect only one row back from the CROSS APPLY SQL. Or you ordered the SQL result set and *do* just want the first row e.g. maximum/minimum effective date.

Note: CROSS/OUTER APPLY are SQL Server specific syntax for LATERAL in Oracle and others.